Displaying Data from Multiple Tables
Join types
An inner join returns only matching rows. Outer joins preserve unmatched rows from one side (LEFT, RIGHT) or both (FULL), filling the other side with NULLs. A cross join produces the Cartesian product. A self join is a table joined to itself using two aliases. A non-equijoin uses BETWEEN or inequality instead of equality.
Syntax: Oracle's legacy form puts (+) on the side that may be missing; the ANSI forms are NATURAL JOIN, JOIN ... USING (col), JOIN ... ON condition, plus LEFT/RIGHT/FULL OUTER JOIN. NATURAL JOIN and USING join automatically on columns with the same name - which is exactly when they go wrong. With USING, the shared column cannot be qualified with a table alias in the select list.
Traps
Forgetting a join condition silently yields a Cartesian product. Ambiguous unqualified column names raise ORA-00918. A predicate on an outer-joined table placed in WHERE (instead of ON) silently converts the outer join back to an inner join - a classic exam trap. The (+) operator goes on the side lacking rows and cannot be used with OR or on both sides.
Worked example
SELECT e.last_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
Every employee appears, including those without a department, whose department name is NULL.
Rule of thumb
Count the tables, count the join conditions: n tables in an inner join need at least n-1 conditions.