Displaying Data from Multiple Tables (Joins)
Displaying Data from Multiple Tables (Joins)
This domain tests combining rows from related tables. You must choose the correct join type, place filter conditions correctly, and avoid accidental row multiplication. Oracle supports ANSI join syntax and older outer-join syntax with the (+) operator.
Core rules
- An inner join returns only matching rows. LEFT OUTER JOIN preserves all rows from the left table; RIGHT OUTER JOIN preserves the right table; FULL OUTER JOIN preserves both.
- CROSS JOIN returns the Cartesian product. A join without a condition produces the same multiplication.
- ON supplies the join condition; USING names a common column and removes duplicate output columns. NATURAL JOIN joins all same-named columns automatically, which can be risky.
- A self join aliases the same table twice, for example employee and manager rows.
- In Oracle's old syntax, (+) marks the deficient side. It belongs on the table that may lack a matching row. ANSI join syntax and (+) syntax cannot be mixed in the same FROM clause.
- Conditions in ON affect matching; conditions in WHERE filter after the join. For an outer join, a WHERE predicate on the preserved table's nullable side can turn it into an inner join.
Worked example
SELECT d.department_name, e.last_name FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id AND e.salary > 5000 ORDER BY d.department_name;
The salary predicate is part of the ON condition, so departments with no qualifying employee still appear with a null last_name. Moving e.salary > 5000 to WHERE would discard those preserved departments.
Traps
A missing join condition creates a Cartesian product. Qualifying columns with table aliases prevents ambiguous-column errors. NATURAL JOIN can join unintended same-named columns such as a shared date column. Putting a filter on the outer-joined table in WHERE defeats the outer join. Placing (+) on the wrong table changes which rows are preserved.