SQL: Joins and Operators
Logical Operator: OR
Only one of the conditions needs to be true. Evaluated after the AND operator when parenthesis are not present
Left Outer Join / Left Join
Preserves the unmatched rows from the first (left) table, joining them. requires join clause
Right Outer Join / Right Join
Preserves the unmatched rows from the second (right) table, joining them. requires join clause
Minus Operator
Relational set operator that returns only the results in the first query that are not in the second query
Logical Operator: AND
Both of the conditions need to be true.
Cross-Join
A join in which each row from one table is combined with each row from another table.
Outer Join
A join in which rows that do not have matching values in common columns are nevertheless included in the result table.
Equi-Join
A join where the condition contains an equality operator
Logical Operator: BETWEEN... AND
Compares a value to a range of values
Logical Operator: IN
Compares a value to a specific list of literal values Functionally equivalent to using compound OR conditions Cannot be used within an Outer Join condition
Set Operators
These combine sets of rows returned by queries, instead of individual items. Examples: UNION, INTERSECT, MINUS, UNION ALL, etc. "Compound Query"
Inner Join
They are also referred to as an EQUIJOIN. It creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. May or may not have the same column name. Will NOT return non-matching values
Hash Join
This is where two tables are joined based on a hashing algorithm. You may see this in your Explain Plan output.
Non Equi-Join
a Join there the condition does NOT contain an equality operator (e.g greater than or less than)
Natural Join
an implicit join clause based on the common columns in the two tables. Common columns MUST have the same name in the two tables
Full Outer Join
A keyword that returns all records when there is a match in either left (table1) or right (table2) table records.
Logical Operator: <>
Not Equal To. Functionally Equivalent to != and ^= NOT equivalent to NULL
Intersect Operator
Relational set operator that yields only the rows that appear in both tables
Union Operator
relational set operator that combines all rows from two tables, excluding duplicate rows