Interview questions
SQL interview questions
Common SQL interview questions with short, correct answers, from joins to window functions and indexes. Most come with a query you can run and change in the free SQL playground.
- 38 questions
- Updated November 2026
matching
Basics
-
1. What is the difference between WHERE and HAVING? Junior
Run this query in the SQL playgroundWHEREfilters rows before they are grouped, so it can't use aggregates.HAVINGfilters groups afterGROUP BY, so it can:HAVING COUNT(*) > 1. Put plain row conditions inWHERE; it is faster because fewer rows reach the grouping. -
2. In what order is a SELECT query evaluated? Mid-level
Logically:
FROMandJOIN, thenWHERE,GROUP BY,HAVING,SELECT,DISTINCT,ORDER BY, and lastLIMIT. That's why a column alias fromSELECTcan't be used inWHERE, but can usually be used inORDER BY. -
3. What is the difference between DELETE, TRUNCATE and DROP? Junior
DELETEremoves rows (all, or those matchingWHERE), one by one; it fires triggers and can be rolled back.TRUNCATEremoves every row at once and usually resets the auto-increment counter. It is much faster; in MySQL it can't be rolled back.DROP TABLEremoves the table itself, structure and all.
-
4. What is the difference between UNION and UNION ALL? Junior
Both stack the results of two queries with the same number of columns.
Run this query in the SQL playgroundUNIONremoves duplicate rows, which needs a sort or hash step.UNION ALLkeeps every row and is faster, so use it whenever duplicates are impossible or wanted. -
5. How does NULL behave in comparisons? Junior
Run this query in the SQL playgroundNULLmeans unknown, so any comparison with it is unknown, not true:NULL = NULLis not true andsalary <> 100skips rows where salary isNULL. UseIS NULL/IS NOT NULL, andCOALESCE(column, default)to replace it.COUNT(column)skips NULLs;COUNT(*)doesn't. -
6. What is the difference between CHAR and VARCHAR? Junior
CHAR(n)is fixed length and padded with spaces, which suits values that are always the same length, such as country codes.VARCHAR(n)stores only the characters used plus a small length prefix, so it suits text whose length varies, such as names and emails.
Joins
-
7. What types of JOIN are there? Junior
INNER JOIN: only rows that match on both sides.LEFT JOIN: every row from the left table; columns from the right areNULLwhere nothing matches.RIGHT JOIN: the same, the other way round.FULL OUTER JOIN: every row from both sides (not in MySQL; emulate withLEFT JOINUNIONRIGHT JOIN).CROSS JOIN: every combination of rows.
-
8. How do you find rows with no match in another table? Junior
Use a
Run this query in the SQL playgroundLEFT JOINand keep the rows where the right side isNULL, or useNOT EXISTS. Both find, for example, customers who never placed an order. AvoidNOT INwith a subquery that can returnNULL: it then returns no rows at all. -
9. What is a self join? Mid-level
A table joined to itself under two aliases. The classic case is an employees table with a
Run this query in the SQL playgroundmanager_idcolumn: joinemployees etoemployees monm.id = e.manager_idto list each employee with their manager. Use aLEFT JOINto keep people who have no manager. -
10. What is the difference between a condition in ON and in WHERE for a LEFT JOIN? Mid-level
In
Run this query in the SQL playgroundON, the condition decides which right-hand rows match, and every left row is still kept. InWHERE, it runs after the join and removes rows, so a condition on the right table likeWHERE o.status = 'shipped'silently turns theLEFT JOINinto an inner join. -
11. Why can a join return more rows than you expect? Mid-level
When a row matches several rows on the other side, it appears once per match. Joining orders to order items, then summing an order-level column, counts it once per item. Aggregate in a subquery first, or count with
COUNT(DISTINCT ...).
Grouping & aggregates
-
12. What does GROUP BY do? Junior
It collapses rows with the same values in the listed columns into one row per group, so aggregate functions (
Run this query in the SQL playgroundCOUNT,SUM,AVG,MIN,MAX) work per group. Every selected column must be grouped or aggregated; MySQL'sONLY_FULL_GROUP_BYmode enforces this. -
13. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)? Junior
Run this query in the SQL playgroundCOUNT(*)counts rows.COUNT(column)counts rows where that column is notNULL.COUNT(DISTINCT column)counts the different non-null values. -
14. How do you find duplicate values? Junior
Group by the column (or columns) that should be unique and keep groups with more than one row:
Run this query in the SQL playgroundGROUP BY email HAVING COUNT(*) > 1. To see the full duplicate rows, join that result back to the table or useCOUNT(*) OVER (PARTITION BY email). -
15. How do you count rows that meet a condition inside one query? Mid-level
Put a
Run this query in the SQL playgroundCASEinside the aggregate:SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END). This gives several counts in one pass, which is the usual way to build a pivot-style report.
Subqueries & CTEs
-
16. How do you find the second-highest salary? Junior
Take the highest salary that is below the maximum:
Run this query in the SQL playgroundSELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). For the Nth highest, useDENSE_RANK()orORDER BY salary DESC LIMIT 1 OFFSET n - 1on the distinct salaries. -
17. What is a correlated subquery? Mid-level
A subquery that refers to a column of the outer query, so it is evaluated once per outer row. Example: employees who earn more than the average of their own department. It is easy to write, but on big tables a join or window function is often faster.
Run this query in the SQL playground -
18. What is the difference between IN and EXISTS? Mid-level
Run this query in the SQL playgroundINcompares a value with a list or subquery result.EXISTSchecks whether a subquery returns any row and can stop at the first one. Modern optimisers often run both the same way. The real difference isNOT IN: if the subquery returns aNULL, it matches nothing, so preferNOT EXISTS. -
19. What is a CTE? Mid-level
A common table expression is a named subquery defined with
Run this query in the SQL playgroundWITH name AS (...)before the main query. It makes long queries readable and can be referenced more than once. CTEs work in MySQL 8+, MariaDB 10.2+, PostgreSQL and SQLite. -
20. What is a recursive CTE used for? Senior
Walking hierarchies and generating series: org charts, category trees, threaded comments, or every date in a range. It has an anchor query,
Run this query in the SQL playgroundUNION ALL, and a part that joins back to the CTE itself, which repeats until it returns no new rows.
Window functions
-
21. What is a window function? Mid-level
A function that computes a value over a set of rows related to the current row, without collapsing them like
Run this query in the SQL playgroundGROUP BYdoes. It usesOVER (PARTITION BY ... ORDER BY ...). Examples:ROW_NUMBER(),RANK(),SUM() OVER,LAG(). Available in MySQL 8+ and MariaDB 10.2+. -
22. What is the difference between ROW_NUMBER, RANK and DENSE_RANK? Mid-level
With ties,
Run this query in the SQL playgroundROW_NUMBER()still numbers 1, 2, 3.RANK()gives tied rows the same number and skips the next ones: 1, 2, 2, 4.DENSE_RANK()gives the same number without gaps: 1, 2, 2, 3. -
23. How do you get the top earner in each department? Mid-level
Rank the rows inside each department, then keep rank 1:
Run this query in the SQL playgroundRANK() OVER (PARTITION BY department_id ORDER BY salary DESC)in a subquery or CTE, filtered withWHERE pos = 1. UseROW_NUMBER()instead if you need exactly one row per department even with ties. -
24. How do you calculate a running total? Mid-level
Use
Run this query in the SQL playgroundSUM(amount) OVER (ORDER BY date). The default frame runs from the first row to the current one. AddPARTITION BYto restart the total for each customer or month. -
25. What do LAG and LEAD do? Senior
Run this query in the SQL playgroundLAG(column)reads the value from the previous row in the window's order, andLEAD(column)from the next one. They make row-to-row comparisons easy, such as the change from last month or the gap between two orders, without a self join.
Indexes & performance
-
26. What is an index and what does it cost? Junior
An index is a separate sorted structure (usually a B-tree) that lets the database find rows without scanning the whole table. It speeds up
WHERE,JOINandORDER BYon the indexed columns. It costs disk space and slows down everyINSERT,UPDATEandDELETE, because the index must be updated too. -
27. What is a composite index and why does column order matter? Mid-level
An index on several columns, such as
(customer_id, ordered_at). It works from left to right: it helps queries filtering oncustomer_id, or oncustomer_idandordered_at, but not onordered_atalone. Put equality columns first and range or sort columns after them. -
28. When is an index not used? Mid-level
- A function or calculation on the column:
WHERE YEAR(created_at) = 2026(use a date range instead). - A leading wildcard:
LIKE '%gmail.com'. - Comparing with a different type, which forces a conversion.
- Low selectivity: when most rows match anyway, a full scan is cheaper.
- Skipping the first column of a composite index.
- A function or calculation on the column:
-
29. How do you find out why a query is slow? Mid-level
Run
EXPLAIN(orEXPLAIN ANALYZEin MySQL 8 and PostgreSQL) and look for full table scans (type: ALL), large row estimates,Using filesortandUsing temporary. Enable the slow query log to find the worst queries, then add the right index or rewrite the query. -
30. What is a covering index? Senior
An index that contains every column a query needs, so the database answers from the index alone without reading the table rows. MySQL shows
Using indexinEXPLAIN. For example, an index on(status, created_at)coversSELECT created_at FROM orders WHERE status = 'paid'. -
31. What is the N+1 query problem? Mid-level
Loading a list with one query, then running one more query for each row, such as each post's author: 1 + N queries. Fix it with a join, or with an
IN (...)query that loads all related rows at once (eager loading,with()in Laravel). -
32. Why is OFFSET pagination slow on big tables? Senior
LIMIT 20 OFFSET 100000still reads and throws away the first 100,000 rows. Keyset (cursor) pagination is fast at any depth: remember the last id and ask forWHERE id > :last_id ORDER BY id LIMIT 20, which uses the index directly.
Design & transactions
-
33. What is the difference between a primary key, a unique key and a foreign key? Junior
A primary key identifies each row: unique, not null, and one per table. A unique key also forbids duplicates but allows
NULL(in most databases) and a table can have several. A foreign key makes a column refer to a row in another table, so the database refuses orphan rows. -
34. What is normalisation? Mid-level
Organising tables so each fact is stored once, which avoids update anomalies. 1NF: one value per column, no repeating groups. 2NF: every column depends on the whole key. 3NF: no column depends on another non-key column. You may denormalise later on purpose, for read speed.
-
35. What does ACID mean? Junior
- Atomicity: a transaction happens completely or not at all.
- Consistency: it moves the database from one valid state to another, respecting constraints.
- Isolation: concurrent transactions don't see each other's unfinished work.
- Durability: once committed, the change survives a crash.
-
36. What are transaction isolation levels? Senior
From weakest to strongest:
READ UNCOMMITTED(dirty reads possible),READ COMMITTED(only committed data),REPEATABLE READ(the same row reads the same within a transaction; MySQL InnoDB's default) andSERIALIZABLE(as if transactions ran one at a time). Stronger levels mean more locking or retries. -
37. How do you stop two users from buying the last item at the same time? Senior
Do the check and the change atomically. Either update with a condition,
Run this query in the SQL playgroundUPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0, and check that one row changed, or lock the row inside a transaction withSELECT ... FOR UPDATEbefore changing it. -
38. What is SQL injection and how do you prevent it? Junior
Building SQL by joining strings with user input, so input like
' OR 1=1 --changes the query. Prevent it with prepared statements and bound parameters (PDO, Laravel's query builder and Eloquent do this). Never put raw input into table names, column names orORDER BY; check those against an allow-list.
Practise offline
All 38 questions and answers on a printable A4 PDF. Free for newsletter subscribers.