SQL Cheat Sheet for Interviews (2026)

1. INNER JOIN vs LEFT JOIN vs FULL OUTER JOIN — when to use each? Beginner

INNER: only matched rows. LEFT: all rows from left + matched right (NULLs where no match). FULL OUTER: all rows from both sides. Use LEFT when you need 'users with zero orders' — INNER silently drops them.

-- Users with zero orders
SELECT u.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

2. Find duplicate rows in a table. Beginner

GROUP BY the columns you consider duplicates, HAVING COUNT(*) > 1. Or use ROW_NUMBER() window partitioned on those columns and keep rows where row_number > 1.

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

3. Why does COUNT(*) differ from COUNT(column)? Beginner

COUNT(*) counts every row including NULLs. COUNT(column) counts non-NULL values only. COUNT(1) is identical to COUNT(*) — no perf difference in modern databases.

4. Difference between UNION and UNION ALL? Beginner

UNION deduplicates the combined result (adds a sort/hash step). UNION ALL keeps duplicates and is significantly faster. Use UNION ALL unless you specifically need dedup.

5. How do you get the Nth highest salary? Intermediate

Use DENSE_RANK() over ORDER BY salary DESC, then filter by rank = N. Handles ties correctly (two people at #1 both get rank 1). LIMIT/OFFSET fails on ties.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
  FROM employees
) t
WHERE rk = 3;

6. Explain window functions vs GROUP BY. Intermediate

GROUP BY collapses rows into one per group. Window functions add computed columns while preserving every row. Use windows when you need 'each row + its running total' or 'each row + its rank within its department.'

-- Salary and dept rank on the same row
SELECT name, dept, salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;

7. What's a CTE and when do you use it over a subquery? Intermediate

CTE = named subquery via WITH. Prefer CTEs for readability, when the subquery is referenced multiple times, or for recursion. Modern planners often materialize CTEs; use INLINE hints if you need the subquery to fold in. Subqueries win for one-off, single-use cases.

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT dept, COUNT(*) FROM high_earners GROUP BY dept;

8. Running total per user, ordered by date? Intermediate

SUM() as a window function with PARTITION BY user_id ORDER BY date. The frame defaults to UNBOUNDED PRECEDING to CURRENT ROW for aggregates over ORDER BY.

SELECT user_id, date, amount,
  SUM(amount) OVER (
    PARTITION BY user_id ORDER BY date
  ) AS running_total
FROM transactions;

9. Delete duplicates keeping one row per group. Intermediate

Use ROW_NUMBER() to tag duplicates, then delete rn > 1. Postgres-specific: use ctid. MySQL 8+: use CTE with row_number.

WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (
    PARTITION BY email ORDER BY created_at
  ) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

10. Explain GROUP BY rules — why does SELECT a, b, COUNT(*) FROM t GROUP BY a fail? Intermediate

Every non-aggregated column in SELECT must appear in GROUP BY (SQL standard, enforced strictly in Postgres). MySQL 5.7- silently allowed this with unpredictable results. Add b to GROUP BY, or wrap it in MIN/MAX/ANY_VALUE.

11. How does an index speed up a query? Intermediate

B-tree index turns O(n) scan into O(log n) lookup. Best for equality (= 5), range (BETWEEN), and prefix LIKE (LIKE 'abc%'). Useless for suffix LIKE, functions on the column, or leading wildcards. Composite index on (a, b) supports queries on 'a' or 'a AND b', NOT queries on 'b' alone.

CREATE INDEX idx_users_email ON users(email);
-- Uses index
SELECT * FROM users WHERE email = '[email protected]';
-- Does NOT use index (function on column)
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

12. Top N per group — write the query. Intermediate

ROW_NUMBER() partitioned by the group column, ordered by the ranking column, filter row_num <= N.

-- Top 3 highest-paid per department
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY dept ORDER BY salary DESC
  ) AS rn
  FROM employees
) t
WHERE rn <= 3;

13. EXISTS vs IN — which is faster? Intermediate

EXISTS short-circuits on first match — faster for large subquery results. IN materializes the subquery result. Modern planners often produce identical plans for both. EXISTS handles NULLs safely; NOT IN with NULLs returns unexpected empty results.

14. NULL semantics — why does WHERE col != 'x' skip NULLs? Advanced

NULL != anything = NULL (unknown), not TRUE. WHERE clause filters on TRUE only. Use WHERE col != 'x' OR col IS NULL to include NULLs. This is the #1 SQL interview gotcha.

-- Wrong: skips NULLs
SELECT * FROM users WHERE status != 'active';
-- Right
SELECT * FROM users WHERE status IS DISTINCT FROM 'active';

15. Composite index on (a, b, c) — which queries use it? Advanced

Leftmost-prefix rule: queries on (a), (a, b), (a, b, c) use the index. Queries on (b), (c), (b, c) do NOT. Postgres partial exception: bitmap index scans can combine two single-column indexes.

16. Pivot rows to columns without a PIVOT operator? Advanced

Use conditional aggregation: SUM(CASE WHEN category = 'x' THEN value ELSE 0 END) AS x_value. Works in every SQL dialect. Postgres also has crosstab() in the tablefunc extension.

SELECT user_id,
  SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
  SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded
FROM transactions
GROUP BY user_id;

17. How do you find the median in SQL? Advanced

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) in Postgres/Oracle. MySQL: no built-in — use ROW_NUMBER() and count rows to find middle. Discuss the even-count case: median is average of two middle values.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees;

18. Correlated subquery — what is it and when is it slow? Advanced

Subquery that references the outer query — runs once per outer row. O(n*m) worst case. Rewrite as a JOIN or window function when possible. Modern planners sometimes decorrelate automatically but don't count on it.

19. How do you paginate large result sets efficiently? Advanced

OFFSET N LIMIT M gets slow at large N (must scan+discard). Use keyset pagination: WHERE id > last_seen_id ORDER BY id LIMIT M — O(log n) with an index, regardless of page depth.

-- Slow at page 1000
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 20000;
-- Fast at any depth
SELECT * FROM posts WHERE id > 20000 ORDER BY id LIMIT 20;

20. What does EXPLAIN ANALYZE show and how do you read it? Advanced

Actual execution plan with real timings. Read bottom-up: leaf nodes are scans (Seq Scan = full table, Index Scan = index used). Look for high 'rows removed by filter' (missing index), large seq scans on big tables (missing index), and nested loops on big tables (join method mismatch).