SQL Interview Questions and Answers (2026 Edition)

A real SQL interview bank runs into the hundreds of questions once you count every dialect quirk and every variation on a join. Memorizing all of them is the wrong goal — what actually gets tested is whether you can reason correctly about a handful of core ideas: filtering versus aggregating, what a join actually does to rows, and why a query that returns the right answer can still be slow. This guide covers the questions that come up in almost every SQL interview, grouped by topic, with the reasoning spelled out rather than just the answer. Paste any example into GenKitLab's SQL Formatter if the one-line version is hard to read — interviewers write queries compactly, and reformatting one in your head is a skill worth practicing separately from the logic itself.

WHERE vs. HAVING. WHERE filters individual rows before any grouping happens; HAVING filters groups after GROUP BYhas aggregated them. You can't reference an aggregate like COUNT(*) in a WHEREclause because the aggregation hasn't happened yet at that point in query execution — that's the whole reason HAVING exists.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 5;

DELETE vs. TRUNCATE vs. DROP. DELETE removes rows one at a time, can take a WHERE clause, fires triggers, and is fully transactional. TRUNCATEremoves every row by deallocating the data pages rather than logging each deletion — much faster on a large table, but it can't be filtered and typically can't be rolled back the same way in every engine. DROP removes the table itself, definition included — the columns, constraints and indexes are gone, not just the data.

Primary key vs. foreign key vs. unique constraint. A primary key uniquely identifies a row and cannot be NULL; a table has at most one. A unique constraint also forbids duplicates but allows NULL (in most engines, multiple NULLs are permitted) and a table can have several. A foreign key doesn't enforce uniqueness at all — it enforces that a value in one table must exist as a key in another, which is what keeps a child row from referencing a parent that doesn't exist.

INNER JOIN returns only rows with a match on both sides. LEFT JOIN keeps every row from the left table, filling unmatched right-side columns with NULL. FULL OUTER JOIN keeps every row from both sides, matched or not.

-- every customer who has placed an order
SELECT * FROM customers c INNER JOIN orders o ON o.customer_id = c.id;

-- every customer, order columns NULL if they never ordered
SELECT * FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;

-- every customer and every order, matched where possible
SELECT * FROM customers c FULL OUTER JOIN orders o ON o.customer_id = c.id;

Self-join.A table joined to itself, using two aliases, to compare rows within the same table — the classic example is finding an employee's manager, where both the employee and the manager live in the same employees table.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

A join with no ON conditionproduces a cartesian product — every row on the left paired with every row on the right, with no logic connecting them at all. It's almost never intentional; the parameterized query builder guide walks through exactly how a missing join condition produces a query that's syntactically valid but returns rows nobody asked for.

COUNT(*) vs. COUNT(column). COUNT(*) counts every row in the group, regardless of NULLs. COUNT(column) counts only the rows where that column is not NULL — the two return different numbers the moment a column has any missing values.

GROUP BY and the SELECT list. Every column in SELECTthat isn't wrapped in an aggregate function must also appear in GROUP BY. Otherwise the database can't know which single value to return for that column when a group has multiple rows — some engines reject the query outright, others silently pick an arbitrary row's value, which is worse.

Finding duplicate rows is the classic version of this pattern: group by the column (or columns) that should be unique, then keep only the groups that appear more than once.

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

What an index is.An index is a separate, ordered data structure — a B-tree, in most general-purpose databases — that lets the engine look up rows by a column's value without scanning the whole table. It speeds up reads for the same reason a book's index beats reading every page. It slows down writes because every INSERT, UPDATE, or DELETEhas to keep that structure in sync, not just the table's own rows — more indexes means more structures to maintain on every write.

The N+1 query problem.Fetching a list of N parent rows, then running one additional query per parent to fetch its related rows, instead of fetching everything the query actually needs in a single join or a single batched query. It's an application-layer bug more than a SQL one, but recognizing it in a query log — one query, then N nearly identical ones — is a common interview check.

Clustered vs. non-clustered index, conceptually.A clustered index determines the physical order the table's rows are stored in — there can be at most one, because rows can only be sorted on disk one way at a time. A non-clustered index is a separate structure that points back to the row's location rather than reordering the table itself, so a table can have several. The exact implementation details differ by engine — this is a concept worth understanding at this level rather than memorizing engine-specific internals that don't transfer.

Reading the plan a database actually chooses — sequential scan versus index scan, which join algorithm it picked — is covered in depth in how to read EXPLAIN ANALYZE output, which is worth working through before a senior-level SQL interview.

Window functions vs. GROUP BY. GROUP BY collapses multiple rows into one row per group — you lose the individual rows. A window function like ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)computes a value across a group of rows (the “window”) but keeps every row intact, attaching the computed value as an extra column. That's the difference that trips people up: window functions aggregate without collapsing.

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

CTEs vs. subqueries. A CTE (WITHclause) names an intermediate result up front and can be referenced multiple times in the query that follows — a subquery has to be repeated or nested each time it's needed. CTEs also read top-to-bottom, which makes a multi-step query easier to follow than a query with subqueries nested three levels deep. Some engines can also optimize a CTE differently than an equivalent subquery, but readability is the reason to reach for one first.

WITH high_value_customers AS (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 10000
)
SELECT c.name
FROM customers c
JOIN high_value_customers h ON h.customer_id = c.id;

UNION vs. UNION ALL. UNION combines two result sets and removes duplicate rows, which requires an extra sort or hash step. UNION ALL keeps every row from both sets, duplicates included, and is faster because it skips that step entirely. Default to UNION ALL unless you specifically need duplicates removed — reaching for UNION out of habit is a common, quietly expensive mistake.

Reading answers is not the same as writing queries under pressure. Build a few realistic SELECT statements with the SQL Formatter to check your formatting instincts, work through the parameterized query builder guide to see how the same query changes shape across Postgres, MySQL and SQL Server, and if a minified one-liner ever shows up in a take-home assignment, the SQL minifier article explains what that transformation does and does not change about the query's meaning.