Common SQL Interview Questions

SQL interviews rarely test exotic syntax. Instead, they probe whether you understand core relational concepts well enough to explain them clearly and apply them correctly: how filtering differs before and after grouping, what makes a key a key, how deletion commands differ in cost and reversibility, and how three-valued NULL logic changes the way comparisons behave. This topic collects the questions that come up again and again across junior-to-mid-level SQL interviews, paired with answers that go beyond a one-liner so you understand the reasoning, not just the result.

WHERE filters individual rows before any grouping or aggregation happens, and it cannot reference aggregate functions like SUM() or COUNT(). HAVING filters groups after GROUP BY has produced them, and it is specifically designed to filter on aggregate results, such as HAVING COUNT(*) > 5. A query can use both: WHERE narrows the rows that get grouped, then HAVING narrows which resulting groups are kept. If you write a condition on a raw column with no aggregation, put it in WHERE for better performance, since WHERE can eliminate rows before the (often expensive) grouping work happens.

INNER JOIN returns only rows that have matching values in both tables. LEFT (OUTER) JOIN returns all rows from the left table plus matched rows from the right table, filling unmatched right-side columns with NULL. RIGHT (OUTER) JOIN is the mirror image, keeping all rows from the right table. FULL OUTER JOIN keeps all rows from both tables, matching where possible and filling NULLs elsewhere. CROSS JOIN produces the Cartesian product of both tables with no matching condition at all. A SELF JOIN is not a distinct join type mechanically — it's any join (usually INNER or LEFT) where a table is joined to itself, typically used for hierarchical or comparative data like employee-manager relationships.

Both enforce uniqueness of values in a column or set of columns, and both are typically backed by an index. The differences: a table can have only one primary key but multiple unique keys/constraints. A primary key's columns cannot contain NULL, while a unique key generally allows one or more NULLs (since NULL is not considered equal to another NULL, so multiple NULLs don't violate uniqueness in most databases). The primary key is conventionally the column(s) other tables reference via foreign keys, signaling 'this is the canonical identifier for a row', while a unique key just guarantees no duplicate values exist for that column, such as an email address.

DELETE is a DML statement that removes rows one at a time (optionally filtered by WHERE), logs each row deletion, can be rolled back within a transaction, and fires any ON DELETE triggers. TRUNCATE is a DDL-like statement that deallocates the data pages holding all rows in one fast operation, cannot use WHERE (it always removes everything), typically cannot be rolled back in many databases (or requires the truncate to occur inside an explicit transaction, depending on the engine), and resets identity/auto-increment counters. DROP removes the entire table object itself — structure, data, indexes, constraints, and permissions all disappear. In short: DELETE removes some or all rows and keeps the table; TRUNCATE removes all rows fast and keeps the table; DROP removes the table entirely.

A CTE, defined with WITH name AS (SELECT ...), is a named, temporary result set that exists only for the duration of the query that follows it. It improves readability by letting you break a complex query into logical, named steps instead of nesting subqueries. CTEs can also be referenced multiple times in the same query without repeating the subquery logic, and a recursive CTE (WITH RECURSIVE in many databases) is the standard way to walk hierarchical data such as an org chart or category tree. Unlike a view, a CTE is not persisted in the database schema — it's scoped entirely to one statement.

NULL represents 'unknown', not zero or empty string, so any direct comparison with NULL using =, !=, <, or > evaluates to UNKNOWN rather than TRUE or FALSE — including NULL = NULL. This means WHERE column = NULL never matches any row, even rows that are NULL, which surprises many developers. The correct way to test for NULL is IS NULL or IS NOT NULL. NULL also affects aggregate functions (most ignore NULLs, e.g., AVG and COUNT(column) skip them, while COUNT(*) counts all rows) and logical operators (UNKNOWN propagates through AND/OR using three-valued logic, so a row is only returned by WHERE if the final result is TRUE, not UNKNOWN).

UNION combines the result sets of two or more SELECT statements and removes duplicate rows, which requires an implicit sort/deduplication step and is therefore slower. UNION ALL combines the result sets and keeps every row, including duplicates, making it faster since no deduplication work is done. Both require the SELECT statements to have the same number of columns with compatible data types in the same order. Use UNION ALL whenever you know duplicates either can't occur or don't matter, since it avoids unnecessary overhead.

A regular (non-correlated) subquery runs once, independently of the outer query, and its result is used by the outer query. A correlated subquery references a column from the outer query, so conceptually it re-executes once per row processed by the outer query, using that row's values as input. For example, finding employees who earn more than the average salary in their own department requires a correlated subquery that recalculates the department average per row. Correlated subqueries are powerful but can be slow on large datasets since naive execution is row-by-row; modern query optimizers often rewrite them as joins internally when possible.

A clustered index determines the physical order in which table rows are stored on disk — there can be only one per table because data can only be sorted one way physically. A non-clustered index is a separate structure that stores the indexed column values plus a pointer (or the clustered key) back to the actual row, and a table can have many non-clustered indexes. Reading via a clustered index is generally faster for range scans since the data is already in order, while non-clustered indexes add an extra lookup step to fetch the full row unless the query is 'covered' by the index alone.

The classic approach groups by the columns that define a 'duplicate' and filters groups with a count greater than one: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. To then delete duplicates while keeping one copy, a common pattern uses a window function like ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) in a CTE, then deletes rows where the row number is greater than 1. This is asked frequently because it tests whether a candidate can combine GROUP BY/HAVING or window functions correctly rather than writing a slow self-join.

A scalar function operates on a single value and returns a single value for each row it's applied to, such as UPPER(), ROUND(), or DATEDIFF(). An aggregate function operates across a set of rows (typically a group, or the whole table) and collapses them into one summary value, such as SUM(), AVG(), COUNT(), MIN(), and MAX(). This distinction matters for where each can legally appear: scalar functions can be used almost anywhere including WHERE, while aggregate functions require GROUP BY context and can't be used directly in a WHERE clause, only in HAVING or SELECT.