Outer joins preserve rows even when no matching record exists on the other side. They are essential for completeness reports: all customers, including those who have never purchased; all units, including empty units; or records whose relationships are missing.
Key Takeaways at a Glance:
- •LEFT OUTER JOIN preserves every row from the left table; unmatched right-side columns are filled with NULL.
- •To find rows in Table A that have no match in Table B (Anti-Join), use 'LEFT JOIN ... WHERE B.key IS NULL'.
- •Crucial distinction: A filter placed in the ON clause filters right-side rows before joining; in the WHERE clause, it filters after joining (potentially converting LEFT JOIN into INNER JOIN!).
- •RIGHT JOIN is the mirror image of LEFT JOIN; in production, LEFT JOIN is universally preferred for reading left-to-right clarity.
In Simple Words (The Mental Model)
An INNER JOIN throws away people who don't have matches. A LEFT JOIN says: 'Keep everyone from Table A, no matter what. If they have matching records in Table B, show them. If they have no records in Table B, just put NULL in those columns.'
Real-World Analogy
Think of a school attendance roll call. Every enrolled student in the class is on the master list (Left Table). When you call out names to check who paid the museum fee (Right Table), you don't erase the unpaid students from the attendance book—you just write 'Unpaid / NULL' next to their names.