0

Anti-joins, self-joins & the accidental Cartesian product

SQLused in ~60% of apps

Concept 23 gave you INNER and LEFT. These three patterns are what interviewers build on top of them, because each one is a single missing detail away from a query that runs, returns a plausible number of rows, and is wrong: forget the guard on a self-join and every pair is counted twice; forget the ON condition anywhere and you get everything paired with everything.

Core idea. An anti-join finds left rows with no partner (a LEFT JOIN filtered on `right.pk IS NULL`, or NOT EXISTS). A self-join is an ordinary join where both sides are the same table wearing two different aliases, used to compare rows in a table to other rows in that same table — and because every row can now pair with every other row (including itself), it needs a guard condition or it silently becomes its own Cartesian product. A Cartesian product itself is what you get whenever the ON condition is missing, or present but not selective enough: rows multiply instead of adding, with no error to warn you.
Playground, this isn't a screenshot
users
idname
1alice
2bob
3carol
orders
iduser_iditem
1011keyboard
1021monitor
1032desk
10499orphan
select u.name, o.item
from users u
join orders o on o.user_id = u.id;
result3 rows
nameitem
alicekeyboard
alicemonitor
bobdesk

Only rows that matched. carol vanishes (no orders) and so does the orphan order (no user). Unmatched rows are not hidden — they were never produced.

↑ play a little first, then dig in