SQL remains one of the most tested skills in technical interviews, whether you are applying for a backend, data engineering, analytics, or full-stack role. Interviewers use SQL to probe how well you understand data modeling, set-based thinking, and performance. This guide walks through the questions you are most likely to face in 2026, grouped by topic, with concise, correct answers and real query examples you can practice and adapt.

Joins and Set Operations

An INNER JOIN returns only rows that have matching values in both tables. A LEFT JOIN returns all rows from the left table plus matching rows from the right table, filling unmatched right-side columns with NULL. A RIGHT JOIN is the mirror image, keeping all rows from the right table. A FULL OUTER JOIN returns all rows from both tables, with NULLs wherever a match is missing on either side. A useful mental model is that the JOIN type controls which unmatched rows survive; the ON clause controls what counts as a match.

-- Customers and their orders; keep customers even if they never ordered
SELECT c.id,
       c.name,
       o.id AS order_id,
       o.total
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.id
ORDER BY c.name;

-- Find customers who have NEVER placed an order
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.id
WHERE o.id IS NULL;

UNION combines the result sets of two queries and removes duplicate rows, which requires a sort or hash to deduplicate and therefore costs more. UNION ALL concatenates the results and keeps every row, including duplicates. If you know the sets are disjoint, or duplicates are acceptable, prefer UNION ALL because it avoids the extra deduplication work. Both require the two queries to have the same number of columns with compatible data types.

A self join is a join of a table to itself using table aliases, used when rows in a table relate to other rows in the same table. The classic example is an employees table where each row has a manager_id that references the id of another employee. To list each employee alongside their manager's name, you join the table to a second alias of itself on manager_id = id.

Indexes and Performance

An index is a separate data structure, usually a B-tree, that stores a sorted copy of one or more columns along with pointers to the underlying rows. Instead of scanning every row (a full table scan), the database can traverse the index to find matching rows in logarithmic time. Indexes accelerate reads on the indexed columns for equality and range lookups, joins, ORDER BY, and GROUP BY. The trade-off is that every INSERT, UPDATE, or DELETE must also maintain the index, so over-indexing slows writes and consumes storage.

A composite index covers multiple columns in a defined order, for example (last_name, first_name). It follows the leftmost-prefix rule: the index can be used for queries that filter on a leading prefix of the columns. An index on (last_name, first_name) helps queries filtering on last_name alone or on both columns, but not queries filtering only on first_name. Order columns by how you query them, typically putting the most selective or most frequently filtered column that is used for equality first.

A covering index is an index that contains every column a query needs, so the database can answer the query entirely from the index without visiting the table (called an index-only scan). If a query selects and filters on columns all present in the index, no heap or clustered-table lookup is required, which greatly reduces I/O. Many databases let you add non-key columns purely to make an index covering.

-- Composite, potentially covering index for a common query
CREATE INDEX idx_orders_customer_status
    ON orders (customer_id, status)
    INCLUDE (total, created_at);

-- This query can be served as an index-only scan
SELECT status, total, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'shipped';

-- Inspect the plan before and after adding the index
EXPLAIN ANALYZE
SELECT status, total, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'shipped';

Normalization and Data Modeling

Normalization is the process of organizing columns and tables to reduce redundancy and avoid update anomalies. First normal form (1NF) requires atomic column values and no repeating groups. Second normal form (2NF) requires 1NF plus every non-key column to depend on the whole primary key, which matters only for composite keys. Third normal form (3NF) requires 2NF plus no transitive dependencies, meaning non-key columns must not depend on other non-key columns. In practice, aim for 3NF and then denormalize selectively for read performance where measurements justify it.

Denormalization introduces controlled redundancy to reduce expensive joins and speed up read-heavy workloads such as reporting dashboards and analytics. Common techniques include storing a precomputed aggregate (like an order_count on a customer row), duplicating a frequently joined attribute, or maintaining a materialized view. The cost is that you must keep the redundant data consistent, usually with triggers, application logic, or scheduled refreshes, so only denormalize when profiling shows the joins are a real bottleneck.

Transactions and ACID

ACID describes the guarantees a transaction provides. Atomicity means a transaction is all-or-nothing; if any part fails, the whole thing rolls back. Consistency means a transaction moves the database from one valid state to another, respecting constraints. Isolation means concurrent transactions do not interfere in ways that violate correctness, governed by the isolation level. Durability means once a transaction commits, its changes survive crashes, typically via a write-ahead log.

The SQL standard defines four isolation levels that trade consistency against concurrency. READ UNCOMMITTED allows dirty reads. READ COMMITTED prevents dirty reads but allows non-repeatable reads. REPEATABLE READ prevents non-repeatable reads but may allow phantom reads. SERIALIZABLE prevents all three and makes transactions behave as if run one at a time. Higher levels reduce anomalies at the cost of more locking or more retries. Most systems default to READ COMMITTED; choose a stronger level only where the business logic requires it.

-- Money transfer as an atomic transaction
BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

-- If any statement fails, roll back everything
COMMIT;
-- ROLLBACK; would undo both updates

Window Functions and Advanced Queries

A window function performs a calculation across a set of rows related to the current row, defined by an OVER clause, without collapsing them into a single output row. GROUP BY reduces many rows to one row per group, so you lose row-level detail. Window functions keep every row while adding aggregate or ranking columns computed over a partition. This makes them ideal for running totals, moving averages, ranking within groups, and comparing a row to its neighbors.

-- Rank orders per customer by amount, and compute a running total
SELECT
    customer_id,
    id AS order_id,
    total,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY total DESC
    ) AS rank_in_customer,
    SUM(total) OVER (
        PARTITION BY customer_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

All three assign a position within an ordered partition, but they handle ties differently. ROW_NUMBER always produces distinct, consecutive numbers, breaking ties arbitrarily. RANK gives tied rows the same number but then skips the following values, so two rows ranked 1 are followed by rank 3. DENSE_RANK also gives tied rows the same number but does not skip, so two rows ranked 1 are followed by rank 2. Choose based on whether you need gaps after ties.

Query Optimization and the N+1 Problem

Start with EXPLAIN or EXPLAIN ANALYZE to read the execution plan and find full table scans, expensive sorts, or bad join orders. Look for missing indexes on filtered and joined columns, and confirm the optimizer's row estimates match reality; large discrepancies suggest stale statistics, so run ANALYZE. Reduce the data touched by filtering early, selecting only needed columns instead of SELECT *, and avoiding functions on indexed columns in the WHERE clause, which prevents index use. Finally, consider whether the query shape itself can be improved with a join instead of a correlated subquery, or a covering index.

The N+1 problem happens when code runs one query to fetch a list of N parent rows, then runs one additional query per row to fetch related data, resulting in N+1 round trips to the database. It is common with ORMs that lazy-load relationships. The fix is to fetch related data in a single query, using a JOIN or a batched IN query, or the ORM's eager-loading mechanism (such as a join fetch or a prefetch). This collapses N+1 queries into one or two, dramatically reducing latency.

-- Instead of N+1: one query per post to get its author
-- Do a single join fetch of posts with their authors
SELECT
    p.id AS post_id,
    p.title,
    u.id AS author_id,
    u.name AS author_name
FROM posts AS p
JOIN users AS u
     ON u.id = p.author_id
WHERE p.published = TRUE
ORDER BY p.created_at DESC
LIMIT 20;

Frequently Asked Questions

For most backend and full-stack roles you should be fully comfortable with joins, aggregation with GROUP BY and HAVING, subqueries and CTEs, indexing basics, transactions and ACID, and at least the idea of window functions. You should also be able to reason about query performance and the N+1 problem. Data engineering roles go deeper into window functions, partitioning, and optimization, while pure frontend roles usually require less. Practicing a mix of writing queries and explaining trade-offs prepares you for both live-coding and discussion rounds.

Default to standard ANSI SQL and mention the dialect you know best, such as PostgreSQL or MySQL. Call out where behavior differs, for example that PostgreSQL uses ILIKE for case-insensitive matching or that MySQL historically defaulted to REPEATABLE READ. Interviewers appreciate when you note that a feature is engine-specific rather than assuming it works everywhere, because it signals real-world experience.

The most effective practice combines writing queries with verbally explaining your reasoning, because interviews test both. Use a real database with sample data to write and run queries, then rehearse narrating your approach, index choices, and trade-offs as if an interviewer were listening. AI Mock Interview offers a free voice-based SQL track and a question generator at /tools/question-generator that gives instant feedback on both your technical accuracy and how clearly you communicate.