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