RIGHT, FULL, CROSS, and Self Joins
RIGHT JOIN preserves the right input and is often rewritten as a LEFT JOIN by reversing table order for readability. FULL OUTER JOIN preserves unmatched rows from both sides. CROSS JOIN forms a Cartesian product, combining every row from one input with every row from the other, so row counts can grow rapidly. A self join joins a table to itself, often to represent hierarchies such as employees and managers.
Choose joins based on the result semantics, not just syntax familiarity. Inspect cardinality and keys: joining two one-to-many relationships at once can multiply rows and inflate aggregate results unless the data is aggregated at the right grain first.
Self join for an employee hierarchy
SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.id;💡 The same table appears twice with different aliases. The LEFT JOIN includes employees who do not have a manager assigned.
Key Takeaways
- FULL OUTER JOIN preserves unmatched rows from both sides.
- CROSS JOIN creates every possible row pair.
- Understand join cardinality before aggregating joined data.
Knowledge Check
What does a CROSS JOIN produce?