SQL Interview Questions & Answers (2026 Guide)

1. What's the difference between INNER JOIN and LEFT JOIN? Beginner

INNER JOIN returns only rows with matches in both tables. LEFT JOIN returns every row from the left table, filling NULLs where the right side has no match. Use LEFT JOIN when you need to count 'users with zero orders' or 'products never sold' — INNER JOIN silently drops those rows.

-- Users who never placed an order
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

2. How do you find the second-highest salary? Beginner

The idiomatic answer uses DENSE_RANK() over ORDER BY salary DESC and filters rank = 2. Beware of ties: MAX(salary) WHERE salary < MAX(salary) gives you the second-highest DISTINCT value, which is usually what interviewers want, but say so out loud.

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

3. Explain the difference between WHERE and HAVING. Beginner

WHERE filters rows before aggregation. HAVING filters groups after aggregation. You cannot reference aggregate functions in WHERE — that's what HAVING is for. Query planners often let you push filters from HAVING to WHERE when the filter doesn't reference an aggregate — write the WHERE version yourself, it's a signal.

-- Departments with avg salary > 100k
SELECT dept_id, AVG(salary) AS avg_pay
FROM employees
WHERE hired_at >= '2020-01-01'   -- pre-aggregation filter
GROUP BY dept_id
HAVING AVG(salary) > 100000;      -- post-aggregation filter

4. Write a query for running total (cumulative sum). Intermediate

Window function: SUM(amount) OVER (ORDER BY date). If you need it per-user, add PARTITION BY user_id. Interviewer follow-up: 'Do it without window functions.' The self-join answer is O(n²) and you should explicitly call that out — modern SQL uses window functions for a reason.

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

5. Explain database indexes and when they hurt performance. Intermediate

An index is a sorted data structure (usually B-tree) that lets the planner skip rows. It speeds SELECT/WHERE/JOIN on the indexed column and slows INSERT/UPDATE/DELETE because every write updates the index. Composite indexes on (a, b, c) accelerate queries filtering (a), (a, b), or (a, b, c) — but not (b), (c), or (b, c). Low-cardinality columns (boolean, status enum) often don't benefit — the planner may prefer a full scan.

6. What's a covering index? Intermediate

An index that includes every column the query needs, so the planner returns rows without touching the base table. Huge win for hot read paths — no heap fetch. Postgres uses INCLUDE to add non-key columns; MySQL just puts them in the index. Trade-off: bigger index, slower writes.

-- Postgres covering index
CREATE INDEX idx_orders_user_date ON orders(user_id) INCLUDE (total, status);
-- SELECT total, status FROM orders WHERE user_id = 42
-- served entirely from index, no heap fetch

7. Find duplicate rows. Intermediate

GROUP BY the duplicate-defining columns, HAVING COUNT(*) > 1. If they want the actual duplicate rows and not just the keys, wrap the group query as a subquery or use ROW_NUMBER() OVER (PARTITION BY ...).

-- Emails registered more than once
SELECT email, COUNT(*) AS dupes
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Keep the first, mark the rest
SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
FROM users;

8. Explain SQL isolation levels. Advanced

READ UNCOMMITTED — dirty reads. READ COMMITTED — no dirty reads, but non-repeatable (row can change mid-transaction). REPEATABLE READ — same row reads same value in the transaction, but phantoms possible (new rows appear). SERIALIZABLE — full isolation, implementation costs vary (Postgres uses SSI, MySQL uses locking). Default in Postgres = READ COMMITTED. Default in MySQL = REPEATABLE READ. Know your database's default before answering.

9. Design a schema for a URL shortener. Advanced

One table: urls(short_code CHAR(7) PRIMARY KEY, long_url TEXT, created_at TIMESTAMPTZ, expires_at TIMESTAMPTZ, click_count BIGINT). Discuss the click_count write-hotspot — every visit is an UPDATE on the same row. Solution: separate clicks table with INSERT-only, aggregate async. Discuss the short_code generation — base62 encoding of a monotonic ID vs random-hash + collision check.

10. Why is SELECT * bad in production? Advanced

Three reasons. (1) Prevents covering-index optimization — planner has to fetch every column. (2) Ships every column over the wire — kills throughput on wide tables. (3) Breaks when the schema changes — adding a column silently changes the result shape and can break ORMs. Always list the columns you actually use.

11. Explain EXPLAIN and what to look for. Advanced

EXPLAIN shows the query plan without running it. EXPLAIN ANALYZE runs it and shows actual timings. Look for: (1) Seq Scan on large tables (missing index or planner chose wrong), (2) rows-estimate vs actual (stale ANALYZE), (3) nested loop with a big outer relation (should be hash join), (4) sort spills to disk. On Postgres, add BUFFERS to see hot vs cold reads.

12. Design a leaderboard query at 10M users. Advanced

Straight ORDER BY score DESC LIMIT 100 with an index on score works for top-N. For per-user rank ('what rank am I?'), you need percentile_disc or Redis ZSET. Discuss the tradeoff: SQL is source-of-truth, Redis is fast lookup — write both, read from Redis, reconcile async.

-- Top 100 (indexed)
CREATE INDEX idx_scores_desc ON scores(score DESC);
SELECT user_id, score FROM scores ORDER BY score DESC LIMIT 100;

-- Your rank (this is where it gets expensive)
SELECT COUNT(*) + 1 AS rank
FROM scores
WHERE score > (SELECT score FROM scores WHERE user_id = 42);