Joins
What the domain covers
Joins combine rows from two or more tables using related columns. This domain tests inner joins, outer joins, self joins, cross joins, and join syntax.
Core rules
- An inner join returns only matching rows. ON supplies the join condition; USING(col) works when both tables share a column name and removes the duplicate column from output.
- A left outer join keeps all left-table rows; right outer keeps all right-table rows; full outer keeps all rows from both sides. Unmatched columns are NULL.
- A self join joins a table to itself with two aliases, often for employee-manager hierarchies.
- A cross join produces a Cartesian product: every row of one table paired with every row of the other. It occurs accidentally when the join condition is missing.
- NATURAL JOIN joins all columns with the same name, which is risky when tables share unrelated columns.
- Legacy Oracle outer join syntax uses (+), but ANSI JOIN syntax is clearer.
Worked example
SELECT e.last_name, d.department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id;
Reasoning: the join condition matches employee department IDs to department IDs. LEFT OUTER JOIN keeps every employee, including those with no department or an invalid department ID. For unmatched employees, department_name is NULL. If the condition were in WHERE as d.department_id = e.department_id, unmatched rows would be filtered out, turning the outer join into an inner join.
Traps
Do not omit the join condition unless you truly want a Cartesian product. NULL join keys never match, so employees with NULL department_id are unmatched. With USING, do not qualify the shared column with a table alias. A WHERE filter on the outer table can silently remove null-extended rows. NATURAL JOIN can add unintended join columns.