Matching Rows Across Tables
A join combines rows from two tables using a condition. The kind of join decides what happens to rows that have no match on the other side.
The Join Types
- INNER JOIN returns only rows that match on both sides. Unmatched rows are dropped.
- LEFT OUTER JOIN keeps every row from the left table, filling missing right columns with NULL.
- RIGHT OUTER JOIN does the same but preserves the right table.
- FULL OUTER JOIN keeps unmatched rows from both sides.
A Common Trap
If you put a condition on the right table in the WHERE clause of a left join, NULL filled rows fail that test and disappear, quietly turning your outer join back into an inner join. To keep them, move that condition into the ON clause instead.
Finding Missing Rows
A left join where you then keep only rows whose right key is NULL is the classic way to find records on the left that have no partner on the right, such as customers with no orders.
Key idea
Inner joins return only matched pairs, outer joins also keep unmatched rows as NULL, and right table filters belong in ON to preserve them.