SQL Interview Questions

A curated set of SQL interview questions, ordered roughly from fundamentals to more advanced — the kind you'll actually be asked in real screens and on-sites.

Fundamentals#

Q: What's the difference between WHERE and HAVING? WHERE filters individual rows before any grouping happens and cannot reference aggregate functions, because aggregates don't exist yet at that stage. HAVING filters groups after GROUP BY has produced them and is the only one of the two that can reference aggregates like SUM() or COUNT(). A query can use both together: WHERE to cheaply narrow raw rows first, HAVING to filter the resulting aggregated groups.

Q: What's the difference between DELETE, TRUNCATE, and DROP? DELETE removes rows matching a WHERE clause (or all rows without one), is logged row-by-row, and can be rolled back inside a transaction. TRUNCATE removes all rows at once by deallocating the table's storage, is much faster, but typically can't be filtered and is harder or impossible to roll back depending on the database. DROP removes the table's definition entirely — structure and data both gone.

Q: What does a primary key guarantee that a unique index doesn't? Both enforce uniqueness, but a primary key additionally disallows NULL values and a table can only have one (though it can span multiple columns as a composite key). A table can have several unique indexes, and unique-indexed columns are still allowed to contain a NULL.

Joins#

Q: Explain the difference between INNER JOIN and LEFT JOIN with an example. INNER JOIN returns only rows where the join condition matches on both tables — a user with no orders wouldn't appear in a users INNER JOIN orders query at all. LEFT JOIN returns every row from the left table regardless, filling in NULL for the right table's columns when there's no match — so that same user would still appear, with NULL in place of any order data. LEFT JOIN is the tool for "find rows in A with no matching row in B," using WHERE right_table.column IS NULL after the join.

Q: When would you choose a subquery over a join, or vice versa? Choose a join when the query needs to return columns from both tables. Choose a subquery — specifically EXISTS or IN — when you only need to filter one table based on whether a related row exists in another, without pulling any of its columns into the result; EXISTS in particular can short-circuit as soon as one match is found, which often outperforms a join that would otherwise produce duplicate outer rows for multiple matches.

NULL handling#

Q: Why does WHERE column = NULL return zero rows even for rows where the column really is NULL? SQL's three-valued logic means comparing anything to NULL, including NULL itself, evaluates to NULL rather than TRUE or FALSE — and WHERE only keeps rows where the condition is TRUE. The correct check is IS NULL (or IS NOT NULL), which are special operators built specifically to test for the absence of a value rather than compare values.

Q: Why is NOT IN risky when the subquery can return NULL, and what's the safer alternative? If even one row in the subquery's result is NULL, NOT IN returns zero rows for the entire outer query — again a consequence of three-valued logic, since comparing the outer value against that NULL yields NULL rather than a definite FALSE, and NOT IN requires every comparison to definitively fail. NOT EXISTS avoids the trap entirely, since it only checks for the presence of a matching row rather than comparing values directly.

Performance and design#

Q: A colleague says "subqueries are always slower than joins" — is that true? Not reliably. Modern query planners (MySQL, PostgreSQL, SQL Server) frequently rewrite an equivalent subquery and join into the same execution plan, especially for EXISTS/IN patterns against indexed columns. The exception is a correlated subquery that the planner can't flatten — those can genuinely execute once per outer row. When performance is in question, the right move is checking EXPLAIN on the actual query rather than assuming based on syntax alone.

Window functions#

Q: What's the difference between RANK(), DENSE_RANK(), and ROW_NUMBER(), and how does that affect a "top N per group" query? All three assign a position to each row within a partition, differing only in how ties are handled: ROW_NUMBER() never ties, always producing a unique sequential number; RANK() gives tied rows the same position but then skips the ranks that would have followed; DENSE_RANK() also ties rows together but never skips. For "top N per group," ROW_NUMBER() is usually the right choice specifically because it guarantees exactly N rows per group even when there's a tie at the cutoff — RANK() can return more than N rows whenever two values tie right at the boundary.

Q: Why can't you filter directly on a window function's result in a WHERE clause? Window functions are computed after WHERE and GROUP BY have already run, as part of producing the final result set — so a condition like WHERE row_num = 1 fails, because row_num doesn't exist yet at the point WHERE is evaluated. The standard fix is wrapping the query in a subquery or CTE that computes the window function, then filtering in the outer query against that already-computed column.