SQL interview questions and answers

Joins and filtering

Difference between WHERE and HAVING?

WHERE filters rows before grouping happens. HAVING filters groups after GROUP BY has aggregated them, which is why you cannot reference COUNT(*) in a WHERE clause — it does not exist at that stage yet.

INNER JOIN vs LEFT JOIN?

INNER JOIN returns only rows with a match in both tables. LEFT JOIN returns every row from the left table and fills unmatched right-side columns with NULL, for "all of A, with B if it exists."

Why can a LEFT JOIN silently act like an INNER JOIN?

Putting a filter on the right table in the WHERE clause instead of the ON clause, e.g. WHERE b.status = 'active', drops rows where b is NULL, because NULL never equals 'active' — collapsing the outer join back to an inner one.

What is the N+1 query problem?

Fetching a list of parent rows, then running one additional query per row for related data, instead of a single join or a batched IN(...) query. It scales linearly with row count and is a common ORM pitfall.

Window functions and CTEs

What does a window function do that GROUP BY cannot?

A window function such as RANK() OVER (PARTITION BY dept ORDER BY salary DESC) computes a value across related rows without collapsing them into one row per group, keeping every original row plus the computed column.

RANK vs DENSE_RANK vs ROW_NUMBER?

ROW_NUMBER gives a unique sequential number regardless of ties. RANK gives tied rows the same rank but skips subsequent numbers (1,1,3). DENSE_RANK ties them equally without skipping (1,1,2).

What is a CTE, and why use one over a subquery?

A WITH clause names a temporary result set for readability, and most engines let you reference it more than once without repeating the logic. A RECURSIVE CTE can also walk hierarchical data, which a plain subquery cannot.

Indexing and performance

What does an index do, and what does it cost?

A B-tree index lets the engine locate rows by a column’s value without scanning the table, turning an O(n) scan into roughly O(log n) lookup. It costs storage and slower writes, since every insert, update or delete has to update the index too.

When does an index on a column not get used?

Common cases: wrapping the column in a function (WHERE YEAR(date) = 2024), a leading wildcard in LIKE, implicit type conversion between column and literal, or a low-selectivity column where a full scan is actually cheaper.

Does column order matter in a composite index?

Yes. An index on (a, b) is usable left-to-right — it speeds up filtering on a alone or on a and b together, but not on b alone. Put the more selective or more commonly filtered column first.

Clustered vs non-clustered index?

A clustered index determines the physical storage order of the table’s rows and there can be only one per table, usually the primary key. A non-clustered index is a separate structure with pointers back to the row, and a table can have many.

Transactions and concurrency

What does READ COMMITTED prevent?

It prevents dirty reads — you never see another transaction’s uncommitted changes. It does not stop non-repeatable reads or phantom rows, which need a stricter isolation level.

Non-repeatable read vs phantom read?

A non-repeatable read is the same row returning different data on a second read within one transaction because another transaction updated it. A phantom read is a range query returning a different row set on a second run because rows were inserted or deleted in that range.

What does SELECT FOR UPDATE do?

It locks the selected rows within the current transaction so no other transaction can modify or lock them until this one commits or rolls back, commonly used to safely read-then-update a value like a balance.

Schema design

What is 3NF trying to prevent?

It removes columns that do not depend on the primary key and columns that depend on other non-key columns, so a fact is stored in exactly one place and cannot go stale in some rows when updated in others.

When would you deliberately denormalize?

Read-heavy systems sometimes duplicate data, such as a customer’s name on every order row, to avoid an expensive join at query time, trading write complexity and storage for faster reads.

What does a foreign key actually enforce?

It requires that a value in the child column already exists in the referenced parent column, or is NULL, preventing orphaned rows, and can cascade deletes or updates. It does not by itself guarantee an index, though many engines add one automatically.

DELETE vs TRUNCATE vs DROP?

DELETE removes rows one at a time, is logged and can take a WHERE clause. TRUNCATE deallocates all rows at once, is minimally logged and faster, and resets auto-increment. DROP removes the table structure itself.

UNION vs UNION ALL?

UNION deduplicates the combined result, which needs a sort or hash pass. UNION ALL just concatenates the results without checking for duplicates, so it is cheaper when you already know there is no overlap.

Why is SELECT * discouraged in production code?

It pulls columns you may not need, wasting bandwidth, and it silently breaks if the table’s columns change, since the application can no longer predict the result shape.

How this is worked out

• Twenty questions grouped by joins, window functions, indexing, transactions and schema design.
• Every answer explains the mechanism, not just the rule, because that is what a follow-up question probes.
• Syntax is written engine-agnostic; a specific engine’s quirks (e.g., MySQL vs Postgres locking) are noted only where they actually differ.

Questions people ask

Which of these comes up most in a data or backend screen?

The LEFT JOIN turning into an INNER JOIN by accident is one of the most common "why is this wrong" trick questions, because it looks correct at a glance.

Do I need to memorise exact SQL syntax for a whiteboard round?

Most interviewers care more that the logic is right than that every keyword is exact. Being able to explain what a query should do, even with minor syntax slips, usually passes.

Is NoSQL covered here?

No. This page is relational SQL only — joins, indexes, transactions and normalization are concepts specific to that model.