SQL Interview Questions and Answers (2026)

INNER JOIN returns only rows that match on both sides. LEFT JOIN returns every row from the left table, with NULLs where the right side has no match; RIGHT JOIN is the mirror image. FULL OUTER JOIN returns all rows from both sides, matched where possible. In practice most teams write everything as LEFT JOIN and avoid RIGHT JOIN for readability.

The ON clause decides which right-side rows match; unmatched left rows are still kept with NULLs. WHERE runs after the join, so a condition like WHERE orders.status = 'paid' discards those NULL rows and silently turns the query into an inner join. If you want "all customers, plus their paid orders if any", the filter belongs in ON.

UNION removes duplicate rows across the combined result, which costs a sort or hash step. UNION ALL just concatenates. Use UNION ALL unless you specifically need de-duplication — it's faster and it's what you mean most of the time.

Three common ways: LEFT JOIN ... WHERE b.id IS NULL, WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id), or WHERE a.id NOT IN (SELECT a_id FROM b). Prefer NOT EXISTS. NOT IN returns no rows at all if the subquery produces even one NULL, which is a classic production bug.

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. WHERE amount > 100 removes small orders before they're counted; HAVING COUNT(*) > 5 keeps only customers with more than five orders. Filter in WHERE whenever you can — it reduces the rows the database has to aggregate.

COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values. After a LEFT JOIN, COUNT(*) reports 1 for a customer with no orders while COUNT(orders.id) correctly reports 0.

NULL means "unknown", so any comparison with it — including NULL = NULL — evaluates to unknown rather than true, and WHERE only keeps rows that are true. Use IS NULL / IS NOT NULL, and COALESCE(col, default) to substitute a value. Aggregates like SUM and AVG ignore NULLs, which changes the average if you expected them to count as zero.

FROM and joins, then WHERE, GROUP BY, HAVING, SELECT (including window functions), DISTINCT, ORDER BY, and finally LIMIT/OFFSET. That order explains two common surprises: you can't use a SELECT alias in WHERE, and you can't filter on a window function without wrapping the query in a subquery or CTE.

A window function computes a value across a set of related rows without collapsing them. GROUP BY returns one row per group; SUM(amount) OVER (PARTITION BY customer_id) returns every original row with the customer's total alongside it. That makes running totals, rankings and "compare to previous row" queries straightforward.

All three number rows within a partition by the ORDER BY. ROW_NUMBER always gives unique sequential numbers, breaking ties arbitrarily. RANK gives ties the same number and then skips (1, 1, 3). DENSE_RANK gives ties the same number without skipping (1, 1, 2).

Rank inside a CTE, then filter outside it: compute ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn in the CTE, then SELECT ... WHERE rn <= 3. The same pattern with rn = 1 is the standard way to pick the latest row per group, and with rn > 1 to find duplicates to delete.

Use DENSE_RANK() OVER (ORDER BY salary DESC) and filter for rank 2 — it handles ties correctly and generalises to the Nth highest. The older answer, SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees), works too, but say why you'd pick the window version.

They read a value from a previous or following row in the window without a self-join. LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) gives each order the previous order's amount — the basis for month-over-month change, gaps between events, and sessionisation.

A CTE (WITH name AS (...)) is a named subquery that makes a long query readable and can be recursive, which is how you walk hierarchies such as org charts. A subquery is fine for something small and used once. A temporary table physically stores the intermediate result, so it's worth it when that result is expensive, reused several times, or benefits from its own index. Note that optimizers differ on whether a CTE is inlined or materialised.

An index is a separate sorted structure (usually a B-tree) that lets the database find rows without scanning the whole table. The cost is disk space and slower writes — every INSERT, UPDATE and DELETE must maintain each index. Index the columns you filter, join and sort on; don't index everything.

A clustered index defines the physical order of the table's rows, so there can be only one — in SQL Server and MySQL's InnoDB it's normally the primary key. A non-clustered (secondary) index is a separate structure that points back to the row. PostgreSQL has no clustered index in this sense: tables are heaps and every index is secondary.

Queries that filter on a leading prefix: a, a and b, or all three. A query filtering only on b or c generally can't seek into it. Put columns used with equality first and the range or sort column last.

Common reasons: a function or expression wraps the column (WHERE YEAR(created_at) = 2026 instead of a date range), an implicit type conversion, a leading wildcard (LIKE '%abc'), the filter matches so much of the table that a scan is cheaper, or the statistics are stale. Rewriting the predicate so the bare column is on one side usually fixes the first three.

Read the execution plan (EXPLAIN, or EXPLAIN ANALYZE to see actual timings) before changing anything. Look for full scans on large tables, estimated row counts far from actual, and expensive sorts or nested loops. Then fix the cause: add or adjust an index, make predicates index-friendly, select only the columns you need, and filter earlier. Guessing without a plan wastes time in an interview and on the job.

Atomicity: a transaction fully commits or fully rolls back. Consistency: it moves the database from one valid state to another, with constraints enforced. Isolation: concurrent transactions don't see each other's half-finished work, to the degree the isolation level promises. Durability: once committed, the change survives a crash.

Read Uncommitted allows dirty reads. Read Committed prevents dirty reads but allows non-repeatable reads. Repeatable Read prevents those too, though the standard still allows phantom rows. Serializable behaves as if transactions ran one at a time. Higher levels cost more blocking or more retries. Defaults differ: PostgreSQL and SQL Server use Read Committed, MySQL InnoDB uses Repeatable Read.

Two transactions each hold a lock the other needs, so neither can proceed; the database detects it and kills one. Reduce them by touching tables and rows in a consistent order, keeping transactions short, indexing so statements lock fewer rows, and retrying the failed transaction in application code.

DELETE removes rows one at a time, can take a WHERE, fires triggers and is fully logged. TRUNCATE removes all rows by deallocating storage — much faster, no WHERE, usually resets identity counters. DROP removes the table itself, structure included. Whether TRUNCATE can be rolled back depends on the engine: yes in PostgreSQL and SQL Server inside a transaction, no in MySQL.

Normalization organises data so each fact is stored once — in practice, aiming for third normal form, where every non-key column depends on the whole key and nothing but the key. It prevents update anomalies. You denormalize deliberately for read performance — reporting tables, precomputed totals — and accept the job of keeping the copies in sync.

Use parameterised queries or prepared statements so user input is always sent as data, never concatenated into the SQL string. ORMs do this by default, but raw-query escape hatches don't. Add least-privilege database accounts as a second layer.