SQL Interview Questions and Answers: The 20 That Still Get Asked

Part 1: Joins and Filtering (Q1–Q6)

Q1. Difference between INNER and LEFT JOIN?

INNER keeps only matched rows; LEFT keeps all left-table rows, filling NULLs on the right when unmatched. The follow-up is always the trap: adding WHERE right.col = x turns a LEFT JOIN into an INNER JOIN because NULL fails the comparison — unless the condition lives in the ON clause. Walkthrough: the join decision guide.

Q2. Find customers with no orders.

Answer with NOT EXISTS: SELECT c.* FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); — then mention why NOT IN is risky (a single NULL in the subquery returns zero rows). That second sentence is what separates senior answers.

Q3. What does a CROSS JOIN return?

The cartesian product: every row of A paired with every row of B. Legitimate uses: date spines, generating combinations for matrix reports. Interviewers want to hear that an accidental CROSS JOIN (missing ON condition) is a row-explosion bug.

Q4. Self-join example?

Employee → manager: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id; — LEFT so the CEO (no manager) does not vanish.

Q5. Difference between WHERE and HAVING?

WHERE filters rows before grouping, HAVING filters groups after aggregation. Performance-flavored bonus: push conditions into WHERE whenever possible — fewer rows reach the aggregation step.

Q6. UNION vs UNION ALL?

UNION deduplicates (requires a sort or hash pass); UNION ALL concatenates. Use UNION ALL when duplicates are impossible — it is strictly cheaper.

Part 2: Aggregation and NULLs (Q7–Q11)

Q7. COUNT(*) vs COUNT(column)?

COUNT(*) counts rows; COUNT(col) counts non-NULL values. The classic follow-up: after a LEFT JOIN, COUNT(o.id) vs COUNT(*) tells you whether "no orders" shows as 0 or 1. Full trap list: NULL handling cheat sheet.

Q8. Why does NOT IN return nothing sometimes?

If the subquery returns any NULL, every NOT IN comparison evaluates to UNKNOWN, never TRUE — the result set is empty. Fix with NOT EXISTS or filter NULLs explicitly.

Q9. How do you find duplicate rows?

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1; — then to see the full duplicate rows: SELECT * FROM users WHERE email IN (SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1);. Deleting them safely: the dedup guide.

Q10. What does GROUP BY do with NULLs?

All NULLs form a single group — NULLs are treated as equal for grouping even though NULL = NULL is UNKNOWN in comparisons. The subtlety interviewers fish for: this is why GROUP BY and WHERE behave differently around NULLs.

Q11. SUM over zero rows returns what?

NULL, not 0. Wrap in COALESCE for reports. Also note AVG over zero rows is NULL, and AVG skips NULLs in its denominator.

Part 3: Window Functions (Q12–Q16)

Q12. ROW_NUMBER vs RANK vs DENSE_RANK?

On ties: ROW_NUMBER assigns unique arbitrary numbers, RANK skips (1,1,3), DENSE_RANK does not skip (1,1,2). Pick by whether your report wants exactly N rows or all rows at the top N positions. Details with output tables: the ranking guide.

Q13. Get the latest record per group.

The canonical answer: SELECT * FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY created_at DESC) AS rn FROM t) x WHERE rn = 1; — mention the deterministic-tiebreaker habit (add a second ORDER BY column).

Q14. Running total?

SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — noting the ROWS frame, because the default RANGE frame behaves differently with duplicate day values.

Q15. Compare each row to the previous period.

LAG: revenue - LAG(revenue) OVER (ORDER BY month). Follow-up: the first row's LAG is NULL — handle with COALESCE or skip the row deliberately. Worked example: month-over-month growth.

Q16. Top N per group?

ROW_NUMBER within PARTITION BY, filter rn <= N. The senior-level caveat: ROW_NUMBER cuts ties arbitrarily; if ties must be kept, use RANK or DENSE_RANK — say this before they ask.

Part 4: Indexes and Performance (Q17–Q20)

Q17. When would you not use an index?

Tiny tables, low-cardinality columns, write-heavy tables where the query is rare, and columns always wrapped in functions. Full reasoning: the index decision guide.

Q18. Explain composite index column order.

An index on (a, b) serves filters on a and on a+b, but not b alone (leftmost prefix). Order: equality columns first, then ranges/sorts. Bonus point: a covering index that includes all selected columns can avoid table lookups entirely.

Q19. How do you debug a slow query?

EXPLAIN ANALYZE, find the dominant node, check estimated vs actual rows (mismatch → stale stats), then work the predicate/index/rewrite checklist: optimization checklist.

Q20. What is the N+1 problem?

An ORM fetches a list, then runs one query per item for related data — 1 + N queries instead of one join. Fix with a join, a batched IN, or an explicit eager-load. Interviewers like hearing that the fix sometimes lives in application code, not SQL.

How to Practice

Write each answer as a runnable query against a scratch database — recognizing the pattern is worth more than recalling the syntax. Our generator can draft the query from your description; verify the result rows, then explain the query out loud, which is exactly what the interview asks for.