SQL interview questions split into two kinds. The definition kind ('what is a JOIN') you can answer from memory, and Google will happily answer it too. The other kind gives you a query that looks obviously correct and asks what it returns — and those are the ones that decide interviews, because they test whether you can execute SQL in your head rather than recognise it.
This page is entirely the second kind. Four areas cover most of what gets asked: how JOINs multiply rows and where a filter belongs, why NULL breaks comparisons and aggregates, why an aggregate condition cannot live in WHERE, and how window functions rank rows without collapsing them.
Everything here is standard SQL and behaves the same on MySQL, PostgreSQL and SQL Server; where engines differ, the explanation says so. Try each query before opening the answer — the explanations also name the follow-up question that usually comes next.
Four areas, nine problems
JOINs: the questions that separate the two answers
2 problemsEveryone can recite that a LEFT JOIN keeps unmatched left rows. The mark is won on what happens next: those rows arrive with NULLs in every right-hand column, and that single fact explains the two classic bugs — a WHERE filter on a right-hand column silently turns your LEFT JOIN back into an INNER JOIN, and COUNT(right_column) quietly skips them. Answer with the NULL consequence and you have pre-empted both follow-ups.
How it gets asked: "What is the difference between LEFT JOIN and INNER JOIN?" · "What happens to unmatched rows?"
Q1
Table users has 100 rows. Table orders has rows for only 30 of those users. How many rows does each query return?
-- A
SELECT * FROM users u INNER JOIN orders o ON o.user_id = u.id;
-- B
SELECT * FROM users u LEFT JOIN orders o ON o.user_id = u.id;
- AA: 30, B: 100 — always
- BA: one row per matching order; B: that same set plus one NULL-filled row for each of the 70 unmatched users
- CBoth return exactly 100
- DA: 100, B: 30
▶Show answer & explanation
Answer: B. A: one row per matching order; B: that same set plus one NULL-filled row for each of the 70 unmatched users
🐱 The trap in (A) is assuming 30: a user with 5 orders produces 5 rows, so an INNER JOIN returns one row per matching order, not per user. B returns that same set plus 70 rows where every orders column is NULL. This row-multiplication effect is the reason COUNT(*) after a join so often looks wrong — the interviewer is usually checking whether you noticed it before they have to point it out.
Q2
Why does adding this WHERE clause change a LEFT JOIN into something else?
SELECT u.id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.total > 100;
- AIt does not; LEFT JOIN always keeps all left rows
- BUnmatched rows have o.total = NULL, and
NULL > 100 is not true, so they are filtered out — the result is effectively an INNER JOIN - CWHERE runs before the join, so it filters the orders table first
- DIt causes a syntax error in standard SQL
▶Show answer & explanation
Answer: B. Unmatched rows have o.total = NULL, and NULL > 100 is not true, so they are filtered out — the result is effectively an INNER JOIN
🐱 The join does keep the unmatched rows — then WHERE throws them away, because their o.total is NULL and any comparison with NULL yields UNKNOWN, which WHERE treats as not-true. If you want the filter without losing rows, put the condition in the ON clause (LEFT JOIN orders o ON o.user_id = u.id AND o.total > 100) or test OR o.total IS NULL. Knowing where the condition goes is the actual skill being tested.
NULL: the value that is not a value
2 problemsNULL means unknown, not empty and not zero — and every surprising SQL result you have ever debugged follows from that. Comparisons with NULL return UNKNOWN (so you need IS NULL), aggregates skip NULLs (so COUNT(col) and COUNT(*) differ), and NOT IN against a list containing NULL returns no rows at all. Interviewers use NULL questions because the behaviour is completely learnable and completely un-guessable.
How it gets asked: "What does COUNT(column) count?" · "Why doesn't WHERE x = NULL work?"
Q3
The orders table has 10 rows; 3 of them have a NULL coupon_code. What does each expression return?
SELECT COUNT(*), COUNT(coupon_code), COUNT(DISTINCT coupon_code)
FROM orders;
- A10, 10, 10
- B10, 7, and the number of distinct non-NULL codes
- C7, 7, 7
- D10, 3, 3
▶Show answer & explanation
Answer: B. 10, 7, and the number of distinct non-NULL codes
🐱 COUNT(*) counts rows and never skips anything: 10. COUNT(col) counts non-NULL values only: 7. COUNT(DISTINCT col) also ignores NULLs, then de-duplicates. This is the single most common source of 'my totals don't add up' bugs in reporting queries — and it is why COUNT(*) is the safe default when you mean 'how many rows'.
Q4
Why does this return zero rows even though plenty of users are not 1 or 2?
SELECT * FROM users
WHERE id NOT IN (SELECT manager_id FROM staff);
-- staff.manager_id contains 1, 2, and NULL
- ANOT IN is invalid with subqueries
- B
id NOT IN (1, 2, NULL) evaluates to UNKNOWN for every row, so nothing passes the WHERE - CThe subquery returns no rows
- DNOT IN requires an index to work
▶Show answer & explanation
Answer: B. id NOT IN (1, 2, NULL) evaluates to UNKNOWN for every row, so nothing passes the WHERE
🐱 x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and that last comparison is UNKNOWN — so the whole expression can never be TRUE. The result is an empty set, silently. Use NOT EXISTS (which handles NULLs correctly) or filter the subquery with WHERE manager_id IS NOT NULL. This question is a favourite precisely because the query looks obviously correct.
GROUP BY, HAVING and where filters belong
1 problemBoth questions have the same answer: logical execution order. FROM and JOIN build the rows, WHERE filters individual rows, GROUP BY collapses them into groups, HAVING filters the groups, then SELECT and finally ORDER BY. Filter on a raw column, use WHERE; filter on an aggregate, use HAVING — and put the condition as early as possible, because filtering before grouping means fewer rows to group.
How it gets asked: "WHERE vs HAVING?" · "Why can't I use a column that isn't in the GROUP BY?"
Q5
Find customers who placed more than 3 orders in 2026. Which query is correct?
-- A
SELECT user_id, COUNT(*) c FROM orders
WHERE COUNT(*) > 3 AND year = 2026
GROUP BY user_id;
-- B
SELECT user_id, COUNT(*) c FROM orders
WHERE year = 2026
GROUP BY user_id
HAVING COUNT(*) > 3;
- AA — WHERE is always the right place to filter
- BB — WHERE filters rows before grouping; the aggregate condition must go in HAVING
- CBoth work identically
- DNeither; you need a subquery
▶Show answer & explanation
Answer: B. B — WHERE filters rows before grouping; the aggregate condition must go in HAVING
🐱 A fails outright: WHERE runs before the rows are grouped, so COUNT(*) does not exist yet — most engines raise an error. B is right, and it also happens to be the efficient form: filtering to 2026 first means fewer rows to group. The rule to say out loud: raw-column conditions go in WHERE, aggregate conditions go in HAVING.
Window functions: rank without collapsing rows
2 problemsThe 'top N per group' question is the most-asked hard SQL question, and window functions are the answer interviewers hope for: unlike GROUP BY, they compute across a set of rows without collapsing them, so you keep every column. The three ranking functions differ only in how they treat ties — ROW_NUMBER never ties (1,2,3,4), RANK ties then skips (1,1,3), DENSE_RANK ties without skipping (1,1,2). Knowing which one to pick for 'the top 3 including ties' is the whole point.
How it gets asked: "Find the second-highest salary" · "Top N per group" · "ROW_NUMBER vs RANK vs DENSE_RANK?"
Q6
Three employees earn 100, 100 and 90. What does each ranking function return for them, in that order?
- AAll three functions return 1, 1, 2
- BROW_NUMBER: 1,2,3 · RANK: 1,1,3 · DENSE_RANK: 1,1,2
- CROW_NUMBER: 1,1,2 · RANK: 1,2,3 · DENSE_RANK: 1,1,3
- DThey are aliases for the same function
▶Show answer & explanation
Answer: B. ROW_NUMBER: 1,2,3 · RANK: 1,1,3 · DENSE_RANK: 1,1,2
🐱 ROW_NUMBER assigns a unique number regardless of ties, so the two 100s get 1 and 2 arbitrarily. RANK gives both 1 then skips to 3. DENSE_RANK gives both 1 then continues at 2. The practical consequence: for 'top 3 salaries including ties' use DENSE_RANK; for 'exactly 3 rows' use ROW_NUMBER — picking the wrong one is how people silently drop or duplicate rows.
Q7
Get the most recent order per customer, keeping all order columns. Which approach fits?
SELECT * FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
FROM orders o
) t WHERE rn = 1;
- AThis is wrong — GROUP BY user_id with MAX(created_at) is the correct approach
- BCorrect: PARTITION BY restarts the numbering per customer, so rn = 1 is each customer's latest row, with every column intact
- CIt returns one row in total, not one per customer
- DWindow functions cannot be filtered on
▶Show answer & explanation
Answer: B. Correct: PARTITION BY restarts the numbering per customer, so rn = 1 is each customer's latest row, with every column intact
🐱 PARTITION BY is the window equivalent of GROUP BY, except rows survive: numbering restarts for each user_id, so rn = 1 picks each customer's newest order with all its columns. The GROUP BY alternative in (A) gives you the max timestamp but not the rest of that row — retrieving it requires a self-join, which is exactly the clumsiness window functions exist to remove. Note the filter must be in an outer query: window functions are computed after WHERE.