What they're testing
Whether you write SQL or only call an ORM. Everyone who writes real SQL has been bitten by the WHERE-after-LEFT-JOIN trap at least once.
The short answer~30 seconds
INNER keeps only rows matching on both sides. LEFT keeps every row of the left table, filling the right-hand columns with NULL when there's no match. FULL OUTER keeps both sides. The part worth saying out loud: if you filter a right-hand column in WHERE, the NULL rows are discarded and the LEFT JOIN quietly degrades into an INNER JOIN — that condition belongs in ON.
The long answer
A more useful mental model than a Venn diagram: the JOIN produces a set of rows, and WHERE then filters that set. LEFT JOIN says "keep the left row even when there is no right row", and when there isn't, every right-hand column is NULL. WHERE then runs, and a predicate like o.status = 'paid' evaluates to unknown against NULL, so exactly the rows you were trying to keep get thrown away.
So the working rule is: predicates about the outer (possibly-missing) table go in ON; predicates that filter the final result go in WHERE. With an INNER JOIN both positions give the same answer, which is how the habit forms — and then someone switches the join to LEFT and the bug shows up six months later as a report that undercounts customers.
MySQL has no FULL OUTER JOIN; the workaround is a LEFT JOIN unioned with a RIGHT JOIN. It's a small detail, but mentioning it shows you've worked against a real engine rather than only the SQL standard.
Same intent, two different results
-- Đếm ĐÚNG: khách hàng nào cũng xuất hiện, kể cả khách chưa mua gì
SELECT c.id, count(o.id) AS paid_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id;
-- Đếm SAI: WHERE loại hết khách chưa có đơn 'paid'
SELECT c.id, count(o.id) AS paid_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid' -- <- LEFT JOIN vừa thành INNER JOIN
GROUP BY c.id;The second query raises no error and no warning. It just returns fewer customers.
What they'll ask next
?So what is WHERE o.id IS NULL after a LEFT JOIN for?
That's an anti-join: it keeps precisely the left rows with no match. It answers "customers who have never ordered" without a NOT EXISTS subquery. It's the one case where filtering a right-hand column in WHERE is deliberate.
?When would you use a CROSS JOIN?
When you genuinely want the Cartesian product: generating a date × product grid so a report has a cell for days with no sales. Outside that, a CROSS JOIN in a query is usually a join whose condition someone forgot.
These lose points
- Drawing a Venn diagram and stopping. Venn diagrams describe sets, joins produce rows — it can't explain why one left row can appear three times.
- Saying "LEFT JOIN is slower than INNER JOIN". Cost depends on indexes and the planner's estimates, not on the keyword.
These score well
- Pointing out that a join can MULTIPLY rows, so
count(*)after a join is almost always wrong — you wantcount(DISTINCT …), or to aggregate before joining.