SQL Interview Questions 2026: Beyond SELECT and JOIN

SQL interview questions for backend roles rarely stop at "write a query that returns X". Writing the query is the entry ticket. The follow-ups are about what the database does with it: why this LEFT JOIN lost rows, why that index is ignored, what the other transaction sees while yours is still open, why the email uniqueness check in the application let a duplicate through.

That shift is what separates a backend SQL round from an analyst SQL screen. Both need joins and aggregation, but a backend loop cares about correctness under concurrency and performance at scale. Five areas cover almost all of it:

1. Joins, NULLs and set operations - three-valued logic, outer joins and the traps that return wrong answers silently
2. Indexes and query plans - how an index is used, why a predicate cannot use it, how to read EXPLAIN
3. Transactions and isolation - ACID, anomalies, isolation levels, locking and retries
4. Schema design - normalization, keys and relationships, and when to denormalize
5. Constraints - why the database, not the application, is the last line of defence

Try the quiz first. The questions come from real backend scenarios, and the ones you miss tell you which sections to read closely.

Start here: a 5-question self-check

Five questions from the SQL set on squizzu, one per area, ranging from a quick warm-up to two that trip up senior engineers. You see an explanation after each answer and a deeper one beneath it. No account needed.

The serialization-failure question splits candidates most clearly, because many developers have only ever run at the default isolation level and assume an error from the database means their SQL is wrong. The constraint question is the other common miss: an application-level uniqueness check feels sufficient until two requests arrive at the same moment. If either caught you out, read areas 3 and 5 first.

What a SQL interview looks like in 2026

In a backend or data engineering loop, SQL usually shows up three ways.

Write and fix queries. A schema and a question, live or take-home. The interviewer watches whether you handle NULLs, pick the right join and avoid multiplying rows.

Diagnose a slow query. A query, sometimes a plan, and the question "why is this slow?". These test whether you know how indexes work rather than which ones exist.

Reason about concurrency and design. Two users edit the same record, a background job and a request touch the same row, a new feature needs a table. These are the senior questions, and they draw on areas 3 to 5.

The engines in these conversations are mostly PostgreSQL and MySQL, with SQL Server in enterprise shops. Interviewers do not expect you to know every dialect, but they do expect you to say when behaviour depends on the engine, such as the default isolation level or support for FULL OUTER JOIN. If your loop is for a product analytics role instead, the query shapes differ; 20 Meta data scientist interview questions covers window functions, ranking and percentiles in that style.

Area 1: Joins, NULLs and set operations

NULL means unknown, and it changes the logic. SQL uses three-valued logic: a comparison involving NULL is neither true nor false but unknown, and a WHERE clause keeps only rows where the condition is true. So column = NULL never matches anything, which is why the test is IS NULL, and NULL = NULL is unknown too. Two consequences come up in almost every loop.

The first is NOT IN with a NULL in the list. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and the last comparison is unknown, so no row passes. A query that worked for months returns nothing the day a NULL enters the subquery. Write anti-joins with NOT EXISTS instead, which does not have this problem:

-- customers who never ordered: empty as soon as one customer_id is NULL
SELECT c.id FROM customers c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders o);

-- the same question, unaffected by NULLs
SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

The second is a WHERE filter on the outer side of a LEFT JOIN. A left join keeps unmatched left rows and fills the right side with NULLs. A WHERE condition on a right-side column then evaluates to unknown for those rows and removes them, which quietly turns the query into an inner join:

SELECT c.id, o.id AS order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'shipped';   -- in ON: customers without shipped orders survive

Moving the condition into the ON clause, as above, keeps every customer.

Joins multiply rows. An inner join returns one row per matching pair, so if a key repeats on both sides the rows fan out. That is why summing an amount after joining orders to order items and to payments gives an inflated total: each payment is counted once per item. Aggregate each side to the join key first, then join.

Set operations combine rows vertically. UNION removes duplicates and UNION ALL keeps them, which is both faster and usually what you want when stacking two tables of events. INTERSECT and EXCEPT return common rows and the difference; Oracle has long spelled the latter MINUS. For de-duplication, set operations treat two NULLs as the same value, the opposite of the = operator.

Portability questions appear here too. MySQL has no FULL OUTER JOIN. The usual workaround is a LEFT JOIN for the matches plus the left-only rows, combined with the right-only rows from the reverse join, filtered to where the left key is NULL.

What the interviewer is testing: whether your queries stay correct on messy real data. Common follow-up: "COUNT(*) or COUNT(column)?" COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column, so after a left join COUNT(o.id) correctly gives zero orders for a customer who has none.

Area 2: Indexes and query plans

An index is a separate, sorted structure, usually a B-tree, that lets the database find rows by seeking instead of scanning the whole table. It helps only the queries that filter, join or sort on the columns it contains: a table with an index on email gets nothing from it for WHERE status = 'active'. Beyond that, almost every indexing question reduces to one test: can the predicate be turned into a range of the index's sort order?

Composite indexes are sorted by their first column, then the next. An index on (last_name, first_name) serves filters on last_name alone or on both columns, but not on first_name alone, which is only sorted within each last name. When one query has an equality and a range, put the equality column first: (status, created_at) lets the engine jump to status = 'shipped' and read one contiguous range of dates. The same index also serves ORDER BY created_at for that status, so a query ending in LIMIT 10 can stop after ten rows without sorting.

Some predicates cannot seek at all. They are called non-sargable, and interviewers love them:

• a function on the column, as in WHERE YEAR(order_date) = 2026; rewrite it as a date range
• an implicit type conversion, such as comparing a VARCHAR column to an unquoted number
• a leading wildcard, as in LIKE '%smith%', which has no known prefix to seek to
• an OR across two different columns, which one single-column index cannot answer

The fix is usually to leave the indexed column bare and move the computation to the other side of the comparison:

-- scan: the index stores order_date, not YEAR(order_date)
SELECT id FROM orders WHERE YEAR(order_date) = 2026;

-- seek: one contiguous range of the index
SELECT id FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

A covering index answers the query on its own. If a query filters on status and returns only email, an index on (status, email) contains everything needed, and the engine can skip the lookup back into the table for each row.

Indexes are not free. Every INSERT, UPDATE and DELETE must maintain every index on the table, so a write-heavy table with ten indexes pays for them on each write. And the optimizer may rightly ignore an index when a predicate matches most of the table, because reading everything sequentially beats a million individual lookups.

Reading the plan is the practical skill. EXPLAIN shows the plan the optimizer chose; EXPLAIN ANALYZE in PostgreSQL, and the equivalent in other engines, runs the query and adds actual row counts and timings. The first thing to compare is estimated rows against actual rows at each step. A step estimated at ten rows that actually produced two million means the optimizer planned for the wrong data, typically because table statistics are stale. It probably chose a join strategy that only works on small inputs. Refreshing statistics with ANALYZE is often the whole fix.

What the interviewer is testing: whether you can explain a slow query from how the data is stored. Common follow-up: "Why does OFFSET 100000 get slower the deeper you page?" Because the database still reads and discards every skipped row. Keyset pagination, WHERE id > :last_seen ORDER BY id LIMIT 20, seeks directly to the next page instead.

Area 3: Transactions and isolation

A transaction groups statements into one unit that is atomic (all or nothing), consistent (it moves the data from one valid state to another), isolated (concurrent transactions do not see each other's intermediate states) and durable (committed changes survive a crash). Atomicity and durability are rarely controversial. Isolation is where the questions are, because full isolation is expensive and every engine lets you trade some of it away.

The standard defines four isolation levels, each preventing more anomalies:

Level	Prevents	Still allows
READ UNCOMMITTED	nothing reliably	dirty reads of uncommitted data
READ COMMITTED	dirty reads	non-repeatable reads, phantoms
REPEATABLE READ	non-repeatable reads	phantoms in the standard; write skew under snapshot isolation
SERIALIZABLE	every anomaly	nothing; conflicting transactions are blocked or aborted

Defaults differ, which is itself an interview question: PostgreSQL, Oracle and SQL Server default to READ COMMITTED, while MySQL's InnoDB defaults to REPEATABLE READ. Most PostgreSQL and MySQL implementations use MVCC: each update writes a new row version, and a reader sees the versions valid for its snapshot, so readers and writers do not block each other.

The lost update is the most practical anomaly. Two requests read stock = 10, each subtracts one in application code, and both write back 9. There are three standard fixes, and knowing when to use each is the real answer:

• Do it in one statement: UPDATE items SET stock = stock - 1 WHERE id = ? is atomic in the engine.
• Lock pessimistically: SELECT ... FOR UPDATE locks the row until commit, for short transactions where conflicts are common.
• Check optimistically: keep a version column and run UPDATE ... WHERE id = ? AND version = ?. Zero rows updated means someone else saved first. This fits web forms, where a user edits for minutes and a lock cannot be held.

Side by side, for item 42, which the application read with stock = 10 and version = 7:

-- 1. atomic: the engine reads and writes in one step (and never goes below zero)
UPDATE items SET stock = stock - 1 WHERE id = 42 AND stock > 0;

-- 2. pessimistic: a second transaction waits at FOR UPDATE until this one commits
BEGIN;
SELECT stock FROM items WHERE id = 42 FOR UPDATE;  -- the current value, read under the lock
UPDATE items SET stock = stock - 1 WHERE id = 42;
COMMIT;

-- 3. optimistic: 0 rows updated means another request saved first; reload and retry
UPDATE items SET stock = 9, version = version + 1
WHERE id = 42 AND version = 7;

Write skew is the subtler cousin. Two transactions each read the same set of rows, each concludes that a rule still holds, and each updates a different row, so neither overwrites the other, yet together they break the rule. The textbook case is two on-call doctors each checking that someone else is on call before going off call. Snapshot isolation does not catch it, because there is no write-write conflict. SERIALIZABLE does, and in PostgreSQL it does so by aborting one transaction with a serialization failure. That error is expected behaviour, not a bug in your SQL: the application must catch it and retry the whole transaction, which is why code that runs at SERIALIZABLE needs a retry loop.

Deadlocks get the same treatment. Transaction 1 holds row A and waits for B while transaction 2 holds B and waits for A. The database detects the cycle and rolls one of them back, and the application retries. Acquiring locks in a consistent order, such as always by ascending id, makes deadlocks rare.

One operational question rounds this out: a connection sitting "idle in transaction" for hours. Under MVCC, old row versions can only be cleaned up once no open transaction might still need them, so a single forgotten transaction blocks cleanup across the database and tables bloat until it ends.

What the interviewer is testing: whether you know what your code can see and overwrite when it is not alone. Common follow-up: "Why not run everything at SERIALIZABLE?" Because it costs throughput and forces every write path to handle retries; most systems use READ COMMITTED and protect the few critical invariants with locks, atomic updates or constraints.

Area 4: Schema design

Normalization means storing each fact in one place. If every order row repeats the customer's email, changing the email means updating dozens of rows, and missing one leaves the data contradicting itself. Keeping customer details once in a customers table, with orders pointing at it by key, removes that update anomaly. The normal forms are the rules for getting there. First normal form wants one atomic value per column, so no comma-separated lists. Second removes columns that depend on only part of a composite key. Third removes columns that depend on another non-key column, such as a dept_name that really belongs to dept_id.

Relationships follow from the same idea:

• One-to-many: the foreign key goes on the "many" side, so orders.customer_id references customers.id.
• Many-to-many: neither side can hold a list, so a junction table such as enrollments(student_id, course_id) holds one row per pair, usually with that pair as its primary key.
• One-to-one: the child table's primary key is also its foreign key to the parent, which guarantees at most one row per parent.

Surrogate or natural keys? A natural key such as an email carries meaning but can change, and every foreign key pointing at it would have to change too. A surrogate key, an auto-increment integer or a UUID, never changes. Random UUIDs scatter inserts across a B-tree index; time-ordered UUIDv7 values avoid that, and PostgreSQL 18 added a built-in uuidv7() function to generate them. Whichever you choose, the natural key still deserves a UNIQUE constraint.

Denormalization is a deliberate trade. A read-heavy dashboard may justify a summary table or a materialized view that duplicates data to avoid expensive joins, in exchange for writes or refreshes that must keep the copy in sync. The trade-off is the answer, not the technique. The anti-pattern to recognise is entity-attribute-value, a generic (entity_id, attribute, value) table that promises flexibility and costs type checking, constraints and simple queries. A JSONB column for genuinely variable attributes, next to typed columns for the rest, is usually the better compromise in PostgreSQL.

What the interviewer is testing: whether your schema makes invalid data hard to store and common queries easy to write. Common follow-up: "Where would you put an index in this schema?" On every foreign key column you join on or delete through, since most engines do not index them automatically, then on the columns your most frequent queries filter by.

Area 5: Constraints as the last line of defence

Application code usually validates input, so candidates are often asked why the database should check the same rules again. The answer is concurrency and other writers. An application that checks "is this email taken?" and then inserts has a race: two requests can each find the email unused, and each goes ahead with the insert. Only a UNIQUE constraint, checked atomically by the database, closes that window. It also protects the data from the batch job, the other service and the manual fix at 2 a.m., none of which run your validation code.

Each constraint type has a job:

• NOT NULL forbids a missing value.
• UNIQUE forbids duplicates. In standard SQL it allows several NULLs, because NULLs are not equal to each other; PostgreSQL 15 added NULLS NOT DISTINCT for when that is wrong. SQL Server is the exception and allows only one NULL, so several need a filtered unique index.
• CHECK validates one row, such as price >= 0 or end_time >= start_time. It cannot look at other rows.
• FOREIGN KEY requires that a referenced row exists, with an ON DELETE action that decides what happens to children: block the delete, cascade it, or set the column to NULL.
• PRIMARY KEY is effectively UNIQUE plus NOT NULL.

Two techniques mark a senior answer. A partial unique index enforces uniqueness only on some rows, such as one active subscription per user alongside any number of cancelled ones. And adding a constraint to a large, busy table can lock it while every existing row is checked. PostgreSQL lets you add it NOT VALID, which enforces it for new writes immediately, and run VALIDATE CONSTRAINT later with a lighter lock:

-- at most one active subscription per user; cancelled rows are not indexed
CREATE UNIQUE INDEX one_active_subscription
  ON subscriptions (user_id) WHERE status = 'active';

-- new writes are checked at once; existing rows are checked later
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive;

What the interviewer is testing: whether you design data rules that hold even when the application is wrong or not alone. Common follow-up: "How do you enforce that a room cannot be double-booked?" UNIQUE catches only identical rows, not overlapping ranges; PostgreSQL's exclusion constraint on the room and a time range is the database-level answer.

A one-week SQL interview plan

For developers who write SQL at work and want to answer from how the database behaves rather than from habit.

• Day 1 - NULL logic. Build two small tables with NULLs in the join keys. Predict, then run, NOT IN, NOT EXISTS, a left join with a WHERE filter and a UNION over NULL rows.
• Day 2 - Joins at scale. Reproduce a fan-out by joining orders to items and payments, get the wrong total, then fix it by aggregating first.
• Day 3 - Indexes. Load a million rows into a table. Time a filtered query without an index, with a single-column index and with a composite one, and read EXPLAIN ANALYZE each time.
• Day 4 - Break an index. Wrap the column in a function, compare it to the wrong type and add a leading wildcard. Watch each plan fall back to a scan, then rewrite each query so it seeks again.
• Day 5 - Concurrency. Open two sessions. Reproduce a lost update, then fix it with an atomic UPDATE, with FOR UPDATE and with a version column. Then create a deadlock on purpose.
• Day 6 - Design. Model a small domain with one-to-many, many-to-many and one-to-one relationships. Add UNIQUE, CHECK and foreign keys, and one partial unique index.
• Day 7 - Rehearse. Explain out loud, in two minutes each: why NOT IN breaks on NULL, how you would investigate a slow query, and the three ways to prevent a lost update.

Keep practising

Five questions only sample the database. The full SQL set has more than 200 reviewed questions across joins, set operations, indexes, transactions, schema design, constraints, window functions and dialect differences.

Work through the SQL questions on squizzu. Each explanation covers why the other options fail, which is exactly what the "why is this slow?" and "what does this return?" questions test.

SQL rarely comes alone in a backend loop. Java interview questions for 2026 and Python interview questions for 2026 cover the language round, and the system design round takes transactions and indexing up to the level of whole services. If you are heading for a data role, data engineer interview questions for 2026 covers window functions, deduplication and pipelines that stay correct when they run twice.