Combining tables correctly.
WHAT THE TYPES DO
- Inner: rows matching in both
- Left: every row from the left, with matches where they exist
- Right: the same, reversed
- Full: every row from both
- Cross: every combination
WHAT THE COMMONEST ERROR IS
A left join with a filter on the right table in the where clause.
WHY THAT IS WRONG
It converts the left join into an inner join, silently.
WHAT TO DO INSTEAD
Put that condition in the join itself.
WHAT FAN-OUT IS
A join producing more rows than expected, because the join key is not unique on one side.
WHAT IT CAUSES
Inflated totals, which look plausible and are wrong.
HOW TO DETECT IT
Compare row counts before and after the join.
WHAT TO DO ABOUT IT
Aggregate the one-to-many side first, then join.
WHAT A SELF JOIN SUITS
Comparing rows within one table: hierarchies, sequences, previous values.
WHAT AN ANTI JOIN DOES
Finds rows with no match.
HOW TO WRITE ONE
A left join filtering for nulls on the right, or a not-exists condition.
WHAT TO PREFER
Not-exists, which handles nulls correctly.
WHY
A not-in condition returns nothing at all when the list contains a null.
