0
Challenge · 23SQL III: Relationships & Joins
INNER JOIN & LEFT JOIN
SQLused in ~100% of apps
This is the single most-tested topic in every SQL interview, and the one juniors most reliably get half-right. They can recite "INNER returns matches, LEFT returns everything from the left" — and then write a LEFT JOIN, add one innocent WHERE clause, and silently turn it back into an INNER JOIN without noticing that a third of their rows disappeared.
Core idea. A join takes two tables and produces ROWS: for each row on the left, it looks for partners on the right using the ON condition. INNER keeps only the rows that found a partner. LEFT keeps every left row regardless, filling the right-hand columns with NULL where no partner was found. Everything else about joins — anti-joins, the accidental Cartesian product, the count(*) bug — falls out of those two sentences.
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