Module 2 — Subqueries

Why subqueries exist

Sometimes a question can't be answered by looking at raw table data directly — you first need to compute something (a max, an average, a list of ids, a yes/no check), and only then compare each row against that computed thing. A subquery is a SELECT statement nested inside another SQL statement, used to produce that intermediate value, list, or table on the fly.

What it is

A subquery is just a SELECT wrapped in parentheses, placed somewhere another SQL statement expects a value, a list, or a table. It can appear in WHERE, SELECT, FROM, or even HAVING.

When to use it

• You need to compare a row against an aggregate (max, avg, count) computed from the same or a different table.
• You need to check existence of a related row without pulling back any of its columns (data validation, filtering).
• You need to build a small computed lookup table on the fly, used only within this one query.

When NOT to use it

• When a plain JOIN says the same thing more simply and you actually need columns from the other table — joins are usually easier for a reader to follow than deeply nested subqueries.
• When a correlated subquery would force a slow row-by-row loop (see Execution Flow below) and an equivalent JOIN or window function would run in a single pass.

Schema & sample data for this module

We reuse employees/departments from Module 1 (same data), and add a customers/orders pair for the data-quality/production examples.

DDL (recap from Module 1)

-- employees / departments: identical to Module 1 (16 employees, 6 departments, Legal has 0 employees,
-- Tanvi Desai & Sameer Gupta have NULL dept_id, Priya/Rohit(103) tie at 145000, Ananya/Vikram tie at 92000)

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(50),
    country       VARCHAR(50)
);

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,             -- can be a "dangling" id that doesn't exist in customers (data quality issue)
    order_date   DATE,
    total_amount DECIMAL(10,2)
);

INSERT INTO customers VALUES
(201, 'Rahul Bansal',   'India'),
(202, 'Grace Lin',      'Singapore'),
(203, 'Omar Haddad',    'UAE'),
(204, 'Laura Fischer',  'Germany');

INSERT INTO orders VALUES
(5001, 201, '2024-01-10', 2500.00),
(5002, 202, '2024-01-11', 1200.00),
(5003, 201, '2024-01-15',  800.00),
(5004, 203, '2024-02-01', 4300.00),
(5005, 999, '2024-02-05',  650.00);   -- customer_id 999 DOES NOT EXIST in customers -- orphaned order (data bug)

Scalar subquery

What it is: a subquery that returns exactly one row and one column — a single value. It can be used anywhere a plain value could go: in WHERE, in SELECT, even inside arithmetic.

Why it exists: you often need to compare a row against a single computed number — the overall max, the overall average, a specific lookup value — without hardcoding that number.

Easy example

Find employees who earn more than the company-wide average salary.

SELECT emp_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

Line by line: the inner query SELECT AVG(salary) FROM employees runs first, completely on its own, and produces one number (roughly 100,687.5). The outer query then just does a plain comparison, as if you'd typed WHERE salary > 100687.5 directly.

Scalar subquery in SELECT

SELECT
    emp_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

IN subquery

What it is: a subquery that returns a list of values (one column, many rows), used with IN to check membership.

Easy example

Find all employees who work in a department located in Bangalore.

SELECT emp_name
FROM employees
WHERE dept_id IN (
    SELECT dept_id FROM departments WHERE location = 'Bangalore'
);

The inner query returns (1, 4) — Engineering and HR. The outer query keeps any employee whose dept_id is in that list.

Medium example — customers who have placed at least one order

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
);
-- Rahul, Grace, Omar -- Laura is excluded, she has never ordered

Subqueries in HAVING

What it is: a subquery placed inside a HAVING clause so you can compare one group's aggregate against another aggregate — something a plain WHERE can never do, because WHERE runs before grouping even happens.

WHERE vs HAVING — why this needs HAVING at all

Example — departments whose average salary beats the company average

SELECT dept_id, AVG(salary) AS dept_avg
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (
    SELECT AVG(salary) FROM employees
);
-- The inner AVG runs once, company-wide; HAVING compares every group's own AVG against it

Example — customers whose total spend beats the average customer's total spend

SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id
HAVING SUM(total_amount) > (
    SELECT AVG(total_spent)
    FROM (
        SELECT SUM(total_amount) AS total_spent
        FROM orders
        WHERE customer_id IS NOT NULL
        GROUP BY customer_id
    ) per_customer
);

Multi-column / tuple subqueries

What it is: instead of comparing one column against a single-column subquery, you compare a row of columns — written as a parenthesized tuple — against a subquery that returns matching multiple columns per row.

Syntax

WHERE (dept_id, salary) IN (
    SELECT dept_id, MAX(salary)
    FROM employees
    GROUP BY dept_id
)

Read this as: "keep a row only if its (dept_id, salary) pair exactly matches one of the (dept_id, MAX(salary)) pairs the subquery produces" — i.e. this employee holds the top salary within their own department.

Full example — highest salary per department

SELECT e.emp_name, e.dept_id, e.salary
FROM employees e
WHERE (e.dept_id, e.salary) IN (
    SELECT dept_id, MAX(salary)
    FROM employees
    GROUP BY dept_id
);

Vendor support differences

• PostgreSQL, MySQL 8+: full row/tuple comparison support, as shown above.
• SQL Server: does not support row-value tuple comparisons in IN — you must rewrite using a join or a correlated subquery instead.
• Snowflake, BigQuery: support tuple IN comparisons similarly to Postgres.

Alternative using a join / window function (portable everywhere)

SELECT emp_name, dept_id, salary
FROM (
    SELECT emp_name, dept_id, salary,
           RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 1;

Correlated subquery

What it is: a subquery that references a column from the outer query. Because the inner query's result depends on which outer row is currently being processed, it conceptually has to be re-evaluated once per outer row.

Easy example

Find employees who earn more than the average salary of their own department (not the company-wide average).

SELECT e.emp_name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e.dept_id   -- <- this line correlates the subquery to the outer row
);

Line by line: for every outer row e, the inner query recomputes the average salary using only rows from e's own department (e2.dept_id = e.dept_id). Sneha Kapoor (Engineering, 98000) is compared against Engineering's average (~143,250), not the whole company's.

NOT IN & the NULL trap

This is one of the most important gotchas in all of SQL, and it comes up constantly in interviews.

The problem, demonstrated

Goal: find customers who have never placed an order.

-- Looks reasonable...
SELECT customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
);
-- With OUR data (no NULLs in orders.customer_id), this correctly returns Laura Fischer.

Now watch what happens if just one row in the subquery has a NULL customer_id — which is extremely plausible in real data (an order placed as a guest checkout, with no customer linked yet):

INSERT INTO orders VALUES (5006, NULL, '2024-02-10', 300.00);  -- a guest checkout order

SELECT customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
);
-- Now returns ZERO ROWS. Not an error -- just silently, completely empty.

Why this happens

NOT IN (list) is logically equivalent to column != v1 AND column != v2 AND column != v3 ... for every value in the list. If any one value in that list is NULL, that one comparison becomes column != NULL, which evaluates to UNKNOWN — not TRUE, not FALSE. And TRUE AND TRUE AND UNKNOWN evaluates to UNKNOWN overall, which is treated as "not true", so the row gets filtered out. This happens for every row being tested, since every row's chain of ANDs includes that same poisoned NULL comparison — so the whole result set silently collapses to empty.

The safe fix

SELECT c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Correctly returns Laura Fischer, regardless of any NULLs in orders.customer_id

NOT EXISTS never compares values directly against NULL the way NOT IN does — it just asks "does at least one matching row exist," and a NULL row in the subquery simply never matches anything, which is exactly the harmless behavior you want.

ANY & ALL

What they are: operators that compare a value against every value returned by a subquery, using a comparison operator (=, >, <, etc.) combined with ANY or ALL.

• > ANY (subquery) — true if the value is greater than at least one value returned (equivalent to > MIN(...)).
• > ALL (subquery) — true if the value is greater than every value returned (equivalent to > MAX(...)).

Example — employees earning more than ALL Marketing employees

SELECT emp_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE dept_id = 3   -- Marketing
)
AND dept_id != 3;   -- exclude Marketing itself from the comparison

This is functionally identical to comparing against MAX(salary) FROM employees WHERE dept_id = 3, but written with ALL. Most engineers actually prefer the MAX()/MIN() scalar subquery form shown in the Level 1 section — it's more widely understood at a glance than ANY/ALL, which some engineers rarely encounter.

Empty-set & NULL behavior

Every subquery operator has its own specific, memorizable behavior when the subquery returns zero rows, or when NULLs are mixed into the values it returns. This table is one of the highest-density interview-trap references in all of SQL — memorize it.

ANY / ALL with NULL values mixed in

NULLs inside the subquery's result list behave differently for ANY vs ALL, and it mirrors the IN/NOT IN NULL trap covered earlier:

• x > ANY (1, 2, NULL) — if x is greater than 1 or 2, the whole expression is TRUE (one real match is enough; the NULL is simply ignored once a TRUE is already found).
• x > ANY (NULL) alone (only a NULL, no real values) — UNKNOWN, which behaves like FALSE in a WHERE clause.
• x > ALL (1, 2, NULL) — even if x beats 1 and 2, the comparison against NULL is UNKNOWN, and ALL requires every comparison to be TRUE — so the overall result becomes UNKNOWN (filtered out), never TRUE. This is silent and easy to miss.

Safer rewrites

-- Instead of:  salary != ALL (SELECT salary FROM other_table)
-- Prefer:
SELECT * FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM other_table o WHERE o.salary = e.salary
);

-- Instead of:  salary > ALL (SELECT salary FROM other_table)
-- Prefer:
SELECT * FROM employees e
WHERE e.salary > (SELECT MAX(salary) FROM other_table);
-- MAX() over an empty set is NULL, and "salary > NULL" is UNKNOWN -- decide explicitly
-- whether that should mean TRUE (nothing to beat) or FALSE for your business rule,
-- rather than relying on ALL's vacuous-truth default.

ORDER BY & LIMIT inside subqueries

An ORDER BY placed inside a subquery is not automatically meaningless — it depends entirely on whether the subquery also has a row-limiting clause (LIMIT, TOP, FETCH FIRST).

When ORDER BY inside a subquery is ignored

SELECT * FROM employees
WHERE dept_id IN (
    SELECT dept_id FROM departments ORDER BY dept_name   -- <- pointless here
);

Without a LIMIT, the subquery still returns the exact same set of rows regardless of what order they're produced in — IN/EXISTS/set operators don't care about row order at all. Most optimizers will simply discard a useless ORDER BY here (some will even warn or refuse it, since a subquery feeding into IN conceptually has no ordering).

When it matters — paired with LIMIT/TOP/FETCH FIRST

The moment you add a row-limiting clause, ORDER BY becomes essential — it decides which row(s) survive the limit. This is the classic "latest row per group" / scalar-subquery pattern:

-- Postgres / MySQL: latest order date per customer, via a correlated scalar subquery
SELECT c.customer_name,
       (SELECT o.order_date
        FROM orders o
        WHERE o.customer_id = c.customer_id
        ORDER BY o.order_date DESC
        LIMIT 1) AS latest_order_date
FROM customers c;

Here ORDER BY ... LIMIT 1 is doing real work: it picks exactly one row (the most recent) out of potentially many matching rows, and the scalar subquery is only legal at all because it's guaranteed to return at most one row.

EXISTS & NOT EXISTS

What it is: a boolean test — TRUE if the subquery returns at least one row, FALSE if it returns none. EXISTS never cares what columns the subquery selects (writing SELECT 1 or SELECT * inside makes no difference) — only whether a row exists.

This is the "SEMI JOIN" pattern from Module 1, formalized.

Easy example

SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Rahul, Grace, Omar (Laura excluded -- she has no matching order row)

Execution flow internals: how correlated subqueries really run

Logically, a correlated subquery behaves like a nested loop: for every row the outer query considers, the database substitutes that row's values into the inner query and re-evaluates it from scratch.

SELECT e.emp_name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employees m WHERE m.manager_id = e.emp_id
);
-- "find employees who manage at least one other employee"

Conceptual execution, row by row:

1. Take outer row: Arjun Mehta (emp_id=101). Run inner query with manager_id = 101 → finds Priya & Rohit → EXISTS is TRUE → keep Arjun.
2. Take outer row: Priya Nair (emp_id=102). Run inner query with manager_id = 102 → finds Sneha → EXISTS is TRUE → keep Priya.
3. Take outer row: Sneha Kapoor (emp_id=104). Run inner query with manager_id = 104 → finds nobody → EXISTS is FALSE → drop Sneha.
4. ...continue for all 16 rows.

Rewriting subqueries as joins

Many subqueries have an equivalent JOIN form. Knowing both, and knowing when to prefer which, is a strong interview signal.

IN subquery → INNER JOIN + DISTINCT

-- Subquery form
SELECT customer_name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- Equivalent JOIN form
SELECT DISTINCT c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Both give the same result here. The JOIN form needs DISTINCT because a customer with multiple orders would otherwise be duplicated (Module 1's cardinality issue) — the subquery form never had that problem in the first place, since IN only checks membership.

EXISTS → semi-join (same idea)

-- EXISTS form (preferred for pure existence checks)
SELECT c.customer_name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- JOIN + DISTINCT form (works, but needs the DISTINCT safety net)
SELECT DISTINCT c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Subqueries in SELECT, FROM, and WHERE

Subquery in FROM (a "derived table" / inline view)

SELECT dept_id, avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) dept_avgs
WHERE avg_salary > 100000;

The inner query is treated as if it were a real table — you must give it an alias (dept_avgs here), and every database requires this alias even though you never reference it by name in a simple case like this.

Subquery in SELECT (scalar, per-row)

SELECT
    d.dept_name,
    (SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.dept_id) AS headcount
FROM departments d;

This is a correlated scalar subquery run once per department row — functionally similar to a LEFT JOIN + GROUP BY, but sometimes easier to read for a single extra computed column. At scale, prefer the JOIN + GROUP BY form (Module 1) since it's usually a single pass instead of one subquery execution per outer row.

Subqueries with UPDATE, DELETE, and INSERT

Everything so far has been read-only SELECT. Subqueries are just as common — and much higher-stakes — inside the statements that actually change data.

UPDATE with a subquery — refresh a denormalized value

-- Keep a denormalized "current_headcount" column on departments in sync
UPDATE departments d
SET current_headcount = (
    SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.dept_id
);

The scalar subquery is re-evaluated once per row of departments being updated — exactly the correlated-subquery execution model from earlier, just inside a SET clause instead of a SELECT.

DELETE with a subquery — remove orphan rows

DELETE FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM customers c
    WHERE c.customer_id = o.customer_id
);
-- Deletes order 5005 (customer_id 999, which doesn't exist in customers)

INSERT INTO ... SELECT — load filtered/aggregated rows into a reporting table

INSERT INTO dept_salary_report (dept_id, avg_salary, headcount)
SELECT dept_id, AVG(salary), COUNT(*)
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 0;

No parentheses needed around the SELECT here — INSERT INTO ... SELECT ... is its own first-class statement shape, not a subquery nested inside another clause, but it's built from the exact same querying skills.

LATERAL / APPLY: correlated subqueries in FROM

A plain subquery in FROM (a derived table) is not allowed to reference columns from other tables earlier in the same FROM clause — it has to be fully self-contained. LATERAL (PostgreSQL) and CROSS APPLY/OUTER APPLY (SQL Server) remove that restriction: the subquery can reference columns from preceding tables, row by row.

Use case — latest order per customer (Postgres LATERAL)

SELECT c.customer_name, latest.order_id, latest.order_date
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.order_id, o.order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id   -- referencing the outer table's column: only legal because of LATERAL
    ORDER BY o.order_date DESC
    LIMIT 1
) latest ON true;
-- LEFT JOIN LATERAL keeps Laura Fischer with NULLs, since she has no orders at all

Use case — top N rows per parent (SQL Server CROSS APPLY)

SELECT c.customer_name, top_orders.order_id, top_orders.total_amount
FROM customers c
CROSS APPLY (
    SELECT TOP 2 o.order_id, o.total_amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.total_amount DESC
) top_orders;
-- Each customer contributes their own top 2 orders by amount -- impossible to express with a plain subquery in FROM

CROSS APPLY vs OUTER APPLY

• CROSS APPLY — behaves like an INNER JOIN: if the lateral subquery returns zero rows for a given outer row, that outer row is dropped entirely.
• OUTER APPLY — behaves like a LEFT JOIN: if the lateral subquery returns zero rows, the outer row is kept with NULLs for the lateral columns.

Optimizer decorrelation & subquery materialization

Everything said so far describes subqueries logically — as if the database really does re-run a correlated subquery once per outer row, in a loop. In practice, real optimizers frequently rewrite the query into something that runs very differently, while guaranteeing the same result.

What decorrelation means

Decorrelation is the optimizer proving that a correlated subquery can be rewritten as an equivalent join or semi-join, so it can be executed as a single set-based pass instead of a per-row loop.

-- Written as a correlated subquery...
SELECT e.emp_name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employees m WHERE m.manager_id = e.emp_id
);

-- ...but the optimizer is free to internally execute it more like a semi-join:
SELECT DISTINCT e.emp_name
FROM employees e
SEMI JOIN employees m ON m.manager_id = e.emp_id;   -- conceptual, not literal syntax in most engines

When the optimizer can decorrelate

• The correlation is a simple equality (inner.col = outer.col), not a complex expression.
• The subquery doesn't depend on non-deterministic functions or side effects.
• The overall semantics (including how NULLs and duplicates behave) can be proven identical after rewriting.

When it cannot decorrelate

• The correlated subquery uses LIMIT/TOP/ORDER BY to pick a specific row per outer row (the "latest row per group" pattern) — this genuinely needs a per-row computation, which is exactly why LATERAL/APPLY exists as an explicit, guaranteed-correlated construct.
• The subquery references outer columns inside aggregate functions or nested subqueries in ways the optimizer can't safely flatten.
• Some older optimizer versions simply don't implement decorrelation for a given shape, even when it would theoretically be safe.

Materialization vs inlining a derived table

For a non-correlated subquery in FROM (a derived table), the optimizer has a similar choice: materialize it (run it once, fully, into a temporary result, then join against that) or inline/flatten it (merge its logic directly into the outer query plan, sometimes pushing outer filters down into it).

• Predicate pushdown: a filter from the outer query (WHERE avg_salary > 100000) gets pushed down inside the derived table's own query plan, so the database filters early instead of computing the full derived table and filtering afterward.
• Optimization barrier: some constructs (an aggregate, DISTINCT, LIMIT, a window function inside the derived table) prevent pushdown — the database must materialize that subquery fully before applying outer filters, since pushing the filter in earlier would change the result.

Subquery vs join vs CTE vs window function vs temp table

By this point in the module you know four or five different tools that can often solve the exact same problem. Knowing which one to reach for — and being able to say why out loud — is a stronger interview signal than knowing any single syntax.

Production maintainability & readability tradeoffs

• A deeply nested subquery (subquery inside a subquery inside a subquery) is usually the hardest of all five forms for a future reader — including future you — to follow. A CTE that names each intermediate step almost always reads better for anything beyond one level of nesting.
• Prefer the tool that most directly states your intent: EXISTS says "I only care whether a match exists"; a JOIN says "I need columns from both sides"; a window function says "I'm comparing each row to its peers without collapsing rows."
• None of these are mutually exclusive in real production SQL — a well-written query commonly combines a CTE, a window function, and a small correlated subquery in the same statement, each doing the piece it's best suited for.

How interviewers trick people on subqueries

1. The NOT IN + NULL trap — covered in depth above. This is probably the single most common SQL interview gotcha across all companies.
2. The forgotten correlation trap — they ask for "above department average" and quietly check whether you correlate the inner query by dept_id, or accidentally compute the company-wide average instead.
3. The "subquery returns multiple rows" trap — they ask you to use a scalar subquery (= (SELECT ...)) against data where the subquery could return more than one row, to see if you catch the runtime error before running it.
4. The EXISTS vs IN vs JOIN performance question — "which is fastest?" The honest answer is "it depends on the optimizer, data size, and indexes — but EXISTS is generally safest for a pure existence check, since it can short-circuit and never risks duplicate rows." Interviewers want to see you avoid a dogmatic one-line answer.
5. The double-negative trap — "find employees NOT in a department that has NO employees earning over 100000" nests two negations; many candidates fumble translating this into nested NOT EXISTS clauses correctly.

3 Hard Interview Questions

SELECT dept_name, avg_salary
FROM (
    SELECT d.dept_name, AVG(e.salary) AS avg_salary
    FROM departments d
    JOIN employees e ON d.dept_id = e.dept_id
    GROUP BY d.dept_name
) dept_avgs
WHERE avg_salary = (
    SELECT MAX(avg_salary)
    FROM (
        SELECT AVG(salary) AS avg_salary
        FROM employees
        GROUP BY dept_id
    ) all_avgs
);
-- Result: Engineering (~143,250)

SELECT customer_id, total_spent
FROM (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
) customer_totals
WHERE total_spent > (
    SELECT AVG(total_spent)
    FROM (
        SELECT customer_id, SUM(total_amount) AS total_spent
        FROM orders
        WHERE customer_id IS NOT NULL
        GROUP BY customer_id
    ) t
);

SELECT m.emp_name
FROM employees m
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.manager_id = m.emp_id
)
AND NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.manager_id = m.emp_id AND e.salary >= 90000
);

SELECT m.emp_name
FROM employees m
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.manager_id = m.emp_id)
AND 90000 > ALL (
    SELECT e.salary FROM employees e WHERE e.manager_id = m.emp_id
);

Subquery debugging & error cases

Recognizing these error shapes on sight — before running anything — is what separates someone who's memorized syntax from someone who understands what a subquery actually does under the hood.

Scalar subquery returns more than one row

SELECT emp_name FROM employees
WHERE salary = (SELECT salary FROM employees WHERE dept_id = 1);
-- ERROR: more than one row returned by a subquery used as an expression
-- Engineering (dept_id 1) has multiple employees -- the subquery isn't actually scalar here

Fix: aggregate it down to one value (MAX/MIN/AVG), or switch to IN if you genuinely want to compare against a list rather than a single value.

Subquery returns multiple columns where one is expected

SELECT emp_name FROM employees
WHERE dept_id IN (SELECT dept_id, dept_name FROM departments);
-- ERROR: subquery has too many columns
-- IN expects the subquery to return exactly one column to compare against

Fix: select only the one column you're comparing, or use the tuple-comparison form (WHERE (col1, col2) IN (SELECT col1, col2 FROM ...)) if you genuinely need a multi-column match.

Correlated subquery references the wrong alias

SELECT e.emp_name
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e2.dept_id   -- BUG: e2.dept_id = e2.dept_id, always true
);
-- Doesn't error -- silently computes the company-wide average instead of the department average,
-- because the "correlation" condition accidentally compares e2 to itself instead of to the outer e

Derived table missing an alias

SELECT * FROM (SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id);
-- ERROR: subquery in FROM must have an alias
-- Postgres and MySQL both require this even though the alias is never referenced again

Fix: always name it — ... GROUP BY dept_id) dept_avgs.

ORDER BY not allowed or silently ignored in some subquery contexts

• An ORDER BY inside a subquery feeding IN/EXISTS/set operators is either ignored or, in some engines (SQL Server historically), rejected outright with an error unless paired with TOP/OFFSET-FETCH.
• An ORDER BY inside a scalar subquery paired with LIMIT 1 is not just allowed but required for the result to be deterministic — the difference is entirely about whether a row-limiting clause is present (see the ORDER BY / LIMIT section above).

2 FAANG-Level Questions

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Overall max is Arjun at 185000 -> second highest is 160000 (Karan Verma)

SELECT DISTINCT e1.salary
FROM employees e1
WHERE 1 = (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employees e2
    WHERE e2.salary > e1.salary
);
-- "exactly 1 distinct salary is greater than mine" = I am the second highest

SELECT d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
)
AND NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id AND e.salary <= 90000
);
-- Result: none of our 5 non-empty departments qualify (each has at least one employee at or below 90000)

SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id AND e.salary <= 90000
);
-- Result: Legal included, since it has no violating employees (vacuously true)

1 Production Scenario

-- Data quality gate: fail the load (or quarantine these rows) if any exist
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id
);
-- Flags order 5005 (customer_id 999, doesn't exist in customers)

Production data-quality patterns

This section pulls together everything a data/backend engineer actually reaches for daily once subqueries move from interview whiteboard to a real pipeline.

Performance indexing for correlated subqueries

A correlated subquery re-runs once per outer row — if the correlation column isn't indexed, that's a full table scan per outer row, which is catastrophic at scale.

-- Index the column the correlated subquery filters on
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- For EXISTS checks with an extra filter, a composite index on both columns helps the most
CREATE INDEX idx_employees_dept_salary ON employees(dept_id, salary);

• Index the exact column used in the correlation condition (WHERE o.customer_id = c.customer_id → index orders.customer_id).
• Index columns used inside an EXISTS's inner filter, not just the join column, if the inner query also filters (e.g. AND e.salary > 90000 → composite index on (manager_id, salary)).
• Without the index, an unindexed correlated subquery degrades toward O(outer_rows × inner_table_size) — the single most common cause of "this query used to be instant and now it times out" as a table grows past a few hundred thousand rows.

Semi-join & anti-join patterns

EXISTS is a semi-join: it returns each outer row at most once, purely as a yes/no gate, never duplicating it even if many inner rows match. NOT EXISTS is an anti-join: the mirror image, keeping outer rows with zero matches.

Common production data-quality checks (the same shape, over and over)

-- 1. Orphaned facts (fact row with no matching dimension row)
SELECT * FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);

-- 2. Dimensions with no facts (dead inventory / never-used entity)
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 3. Duplicate keys (should be unique but aren't)
SELECT customer_id, COUNT(*) FROM orders
GROUP BY customer_id, order_date HAVING COUNT(*) > 1;

-- 4. Quarantine bad rows instead of failing the whole batch load
INSERT INTO orders_quarantine
SELECT * FROM staging_orders s
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = s.customer_id);

Handling NULL business rules (the guest-checkout pattern)

Before writing an orphan check, you first have to decide: is a NULL foreign key an actual data bug, or an expected, allowed state (a guest checkout with no linked customer yet)? Conflating the two is a common production mistake.

-- Split into two separate, deliberate checks instead of one ambiguous query:

-- (a) TRUE orphans: has a customer_id, but it doesn't exist in customers -- a real bug
SELECT * FROM orders o
WHERE o.customer_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);

-- (b) Expected/allowed NULLs: guest checkouts -- report separately, don't fail the pipeline on these
SELECT * FROM orders o
WHERE o.customer_id IS NULL;

Deduplication with subqueries

-- Keep only the lowest-id row per duplicate key, delete the rest
DELETE FROM orders o
WHERE o.order_id NOT IN (
    SELECT MIN(o2.order_id)
    FROM orders o2
    GROUP BY o2.customer_id, o2.order_date
);
-- Compare with the window-function version: ROW_NUMBER() OVER (PARTITION BY customer_id, order_date
-- ORDER BY order_id) filtered to rn > 1, then delete -- usually preferred in production for large tables,
-- since it avoids the correlated MIN() re-scan per group.

Subqueries in CHECK constraints — why they usually don't work

It's tempting to try to enforce cross-row business rules directly at the schema level:

-- This looks reasonable, but most databases REJECT a subquery inside CHECK:
ALTER TABLE orders ADD CONSTRAINT chk_customer_exists
CHECK (customer_id IN (SELECT customer_id FROM customers));  -- typically not allowed

Incremental loads with NOT EXISTS

Instead of truncating and reloading an entire target table every run, an incremental load only inserts rows from staging that aren't already present in the target — the anti-join pattern applied to pipeline design.

INSERT INTO target (order_id, customer_id, order_date, total_amount)
SELECT s.order_id, s.customer_id, s.order_date, s.total_amount
FROM staging s
WHERE NOT EXISTS (
    SELECT 1 FROM target t WHERE t.order_id = s.order_id
);

This is the same anti-join shape as the orphan/data-quality checks above, just aimed at "which staging rows are genuinely new" instead of "which fact rows are broken."

Why this needs to be idempotent

A pipeline is idempotent if running it twice on the same staging batch produces the same end state as running it once — no duplicate rows from an accidental re-run. The NOT EXISTS anti-join gives you this for free: if the same staging batch is (accidentally or deliberately) reprocessed, every row it already inserted last time now already exists in target, so NOT EXISTS correctly skips them the second time.

10 Practice Questions

Write the SQL yourself before expanding each answer.

SELECT emp_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

SELECT dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id AND e.salary > 150000
);

SELECT emp_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM employees m WHERE m.manager_id = e.emp_id
);

SELECT customer_id
FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
) t
WHERE order_count > 1;
-- Rahul Bansal (201) placed 2 orders

SELECT e.emp_name
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id
);

SELECT dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
-- Legal

SELECT e.emp_name, e.dept_id, e.salary
FROM employees e
WHERE e.salary = (
    SELECT MIN(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id
);

SELECT e.emp_name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.manager_id = e.manager_id
);
-- Note: employees with manager_id = NULL (Arjun, Karan, Meera, Divya, Ishita) compare against
-- the average of OTHER NULL-manager employees -- since NULL = NULL is never true in the correlation,
-- double check this edge case: most engines treat "e2.manager_id = e.manager_id" as UNKNOWN when both are NULL,
-- so top-level employees would each compare against an EMPTY set (AVG of nothing = NULL), and salary > NULL is UNKNOWN --
-- meaning all NULL-manager employees are silently excluded. Flag this out loud as a known limitation.

SELECT order_id, total_amount
FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE country != 'India'
);
-- Grace Lin's and Omar Haddad's orders (5002, 5004)
-- Note: order 5005 (customer_id 999) is correctly excluded, since 999 isn't in the customers list at all