0

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.
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