DSAMotivator

Joins

SQL · 6 interview questions

A join matches rows from one table against rows from another. An INNER JOIN keeps only pairs that matched. A LEFT JOIN keeps every row from the left table regardless, padding the right-hand columns with NULL when nothing matched.

Almost every join question in an interview is really asking whether you know which of those you need. "Customers who never ordered" is a LEFT JOIN with a check for the NULL, or a NOT EXISTS — it is specifically *not* an inner join, because the rows you want are the ones that didn't match.

The mistake worth internalising: filtering a LEFT JOIN's right-hand table in the WHERE clause turns it back into an inner join. LEFT JOIN orders o ON ... WHERE o.status = 'shipped' discards the padded NULL rows, because NULL fails that test. If the condition belongs to the join, put it in the ON clause; if it belongs to the result, put it in WHERE. For an inner join the two are equivalent, which is why the distinction only bites once you switch to an outer join.

Joins also multiply. If a key appears three times on the right, the left row comes back three times, and any COUNT or SUM downstream is now inflated. When a join changes your row count unexpectedly, the join key isn't as unique as you assumed.

Joins interview questions

What's the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows that matched on both sides. LEFT JOIN returns every row from the left table, filling the right-hand columns with NULL where there was no match. RIGHT JOIN is the mirror image and is rarely used, since swapping the table order reads better.
Why they ask: The opening question. The follow-up is always a problem that needs the LEFT JOIN.
Why does adding a WHERE condition on the right-hand table turn a LEFT JOIN into an INNER JOIN?
Unmatched left rows get NULLs in the right-hand columns, and a WHERE test against NULL is unknown, so those rows are filtered out — leaving only matched rows. Put the condition in the ON clause instead if it should apply during matching rather than after.
Why they ask: The single most useful join fact there is, and it explains a bug most people have shipped.
How do you find rows in table A with no matching row in table B?
LEFT JOIN B and keep the rows where B's key came back NULL, or use NOT EXISTS (SELECT 1 FROM B WHERE ...). NOT EXISTS is usually clearer and is safe against NULLs, unlike NOT IN.
Why they ask: 'Customers who never ordered' in every possible costume.
You joined two tables and got more rows than you started with. Why?
The join key isn't unique on the right-hand side. Each left row is paired with every matching right row, so a key appearing three times triples that row. Any aggregate computed afterwards is inflated — this is the classic cause of a SUM that's mysteriously too big.
Why they ask: Separates people who write joins from people who've debugged a wrong total.
For an INNER JOIN, does it matter whether a condition goes in ON or WHERE?
For an inner join, no — the result is identical, so pick whichever reads better (convention: join keys in ON, filters in WHERE). For an outer join it matters enormously: ON filters during matching and preserves unmatched rows, WHERE filters afterwards and drops them.
Why they ask: The precise answer is 'no for inner, yes for outer', and saying only half of it sounds like a guess.
How do you do a FULL OUTER JOIN in MySQL?
MySQL doesn't support it. You emulate it with a LEFT JOIN unioned to a RIGHT JOIN — UNION rather than UNION ALL, so the rows matched by both sides aren't duplicated. Postgres and SQL Server support FULL OUTER JOIN directly.
Why they ask: A dialect question that catches people who learned SQL from Postgres docs and are being interviewed on MySQL.

Practise it

Free, database-tagged LeetCode problems that drill this specific skill. In the app these tick themselves off from your solve history.

You'll forget most of this by next week

That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.

Start your streak

Full refund within 7 days. No questions.