0
Challenge · 24SQL III: Relationships & Joins
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.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
users
| id | name |
|---|---|
| 1 | alice |
| 2 | bob |
| 3 | carol |
orders
| id | user_id | item |
|---|---|---|
| 101 | 1 | keyboard |
| 102 | 1 | monitor |
| 103 | 2 | desk |
| 104 | 99 | orphan |
select u.name, o.item from users u join orders o on o.user_id = u.id;
result3 rows
| name | item |
|---|---|
| alice | keyboard |
| alice | monitor |
| bob | desk |
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