SQL Subqueries — 40-Minute Timeout from Correlated Query

Subqueries are one of the most flexible tools in SQL — they let you compose queries in ways that would otherwise require multiple steps or temporary tables. But they also hide performance traps, especially correlated subqueries that run once per row.

This guide covers the main subquery types, the EXISTS vs IN performance difference, the dangerous NULL behaviour of NOT IN, and when to reach for CTEs instead. Understanding the performance profile of each subquery type is the difference between a query that works and one that works at scale.

What a Subquery Actually Is

A subquery is a SELECT statement nested inside another SQL statement — inside a WHERE, FROM, or HAVING clause. Its result feeds the outer query as a value, a row set, or a correlation reference. The core mechanic: the outer query pauses, the inner query executes (once or per row), and the outer query uses that result to filter, compute, or join.

Subqueries come in two flavors: non-correlated (executed once, independent of the outer query) and correlated (re-executed for each row of the outer query — O(n*m) cost). A non-correlated subquery runs first, materializes its result, then the outer query uses it. A correlated subquery references columns from the outer query, forcing per-row execution. That distinction is the single most important performance property: correlated subqueries can turn a fast query into a 40-minute timeout.

Use subqueries when you need to compare a value against an aggregated result (e.g., "employees earning above department average") or when a JOIN would produce duplicate rows due to one-to-many relationships. In production systems, subqueries often replace complex JOINs for readability, but the trade-off is execution plan complexity. A well-placed subquery can reduce data scanned by orders of magnitude; a poorly placed one can scan the same table millions of times.

Scalar and Non-Correlated Subqueries

A scalar subquery returns exactly one value — one row, one column. You can use it anywhere a single value is expected: in SELECT to compute a derived column, in WHERE for a comparison, or in SET for an UPDATE. The key property of a non-correlated scalar subquery is that it executes once and its result is reused for every row of the outer query.

Subqueries in WHERE with IN accept a multi-row, single-column result — the outer query keeps rows where the column value appears in that list. Subqueries in FROM create a derived table (also called an inline view) — the result acts as a temporary table for the outer query to filter or join against.

Performance note: non-correlated subqueries execute once. The engine computes the inner result, then uses it for all outer row comparisons. This is fundamentally different from correlated subqueries, which re-execute per outer row.

-- Scalar subquery: compute one value, reuse for every row
SELECT
    employee_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;
-- The AVG subquery executes ONCE, result reused for all rows

-- Subquery in WHERE with IN
SELECT product_name
FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE type = 'Electronics'
);
-- Inner query executes once, outer query checks membership

-- Subquery in FROM (derived table / inline view)
SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) dept_averages
WHERE avg_salary > 60000;
-- Derived table computes once, outer query filters the result

Correlated Subqueries — Power and the O(n²) Trap

A correlated subquery references a column from the outer query. This means it cannot run independently — it must re-execute for each row the outer query processes. On a table with 100 rows, that's 100 executions. On a table with 100,000 rows, it's 100,000 executions. This is the O(n²) trap.

Correlated subqueries are useful for row-by-row comparisons that are hard to express any other way. The canonical example: 'find employees earning more than their own department's average.' The subquery needs to know which department the current outer row belongs to — and that changes row by row.

But for large tables, this pattern is almost always replaceable with a JOIN to a pre-aggregated derived table or CTE. The JOIN version computes the average once per department (not once per row) and is orders of magnitude faster.

-- CORRELATED SUBQUERY: executes AVG() once per outer row
-- On 100,000 employees: 100,000 AVG() computations
SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department  -- references outer row!
);

-- BETTER: pre-aggregate in a derived table (computes AVG once per dept)
SELECT e.name, e.salary, e.department
FROM employees e
JOIN (
    SELECT department, AVG(salary) AS dept_avg
    FROM employees
    GROUP BY department
) dept ON e.department = dept.department
WHERE e.salary > dept.dept_avg;

-- Or with a CTE (same performance, more readable):
WITH dept_averages AS (
    SELECT department, AVG(salary) AS dept_avg
    FROM employees
    GROUP BY department
)
SELECT e.name, e.salary, e.department
FROM employees e
JOIN dept_averages d ON e.department = d.department
WHERE e.salary > d.dept_avg;

EXISTS vs IN — Performance and the NULL Danger

EXISTS and IN are both used to check whether matching rows exist in another table, but they behave differently in important ways.

IN collects the full result set from the subquery, then checks membership for each outer row. If the subquery returns 10,000 rows, IN builds a 10,000-entry lookup structure first. EXISTS, on the other hand, is correlated — it runs the inner query for each outer row and stops as soon as it finds one match. This short-circuit makes EXISTS faster when you expect many matches.

The most important practical difference is NULL handling. NOT IN returns an empty result set if the subquery contains any NULL values — because SQL uses three-valued logic: a value compared to NULL is neither true nor false, it's unknown. One NULL in the subquery poisons the entire NOT IN check. NOT EXISTS does not have this problem because it never compares values directly to NULL.

-- IN: collects all values from subquery, then checks membership
SELECT name FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
);

-- EXISTS: short-circuits on first match — stops looking immediately
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders WHERE customer_id = c.id
);

-- The SELECT 1 is a convention — any column works here,
-- EXISTS only cares whether a row was found, not what it contains

-- DANGEROUS: NOT IN with potential NULLs
SELECT name FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders  -- if ANY row has NULL customer_id,
);                                  -- this returns ZERO rows silently!

-- SAFE: NOT EXISTS handles NULLs correctly
SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- ALSO SAFE: explicit NULL guard on NOT IN
SELECT name FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders
    WHERE customer_id IS NOT NULL  -- explicitly exclude NULLs
);

Subqueries in the FROM Clause — Deriving Battle Tables

You understand subqueries in WHERE. Now weaponize them in FROM. A subquery in the FROM clause creates a derived table — a temporary result set you can treat like a real table. Why bother? Because it lets you pre-aggregate, filter, or reshape data before your outer query touches it, avoiding repeated scans and saving your production DB from unnecessary thrashing. The derived table must have an alias. Every database needs a name to reference it. Execution order: the inner query runs first, materializing the derived table (in memory or tempdb), then the outer query queries that result. Be warned: if your derived table returns millions of rows, you just created a bottleneck. Always check execution plans. Derive only what you need, when you need it.

-- io.thecodeforge
-- Find customers whose total orders exceed 10k
SELECT dt.customer_id, dt.total_spent
FROM (
    SELECT o.customer_id, SUM(o.amount) AS total_spent
    FROM orders o
    WHERE o.status = 'completed'
    GROUP BY o.customer_id
    HAVING SUM(o.amount) > 10000
) AS dt
ORDER BY dt.total_spent DESC;

Subqueries with UPDATE and DELETE — Precision Surgery

Subqueries aren't just for SELECT. You can embed them in UPDATE and DELETE to target rows dynamically without manual joins or multiple statements. This is how you fix bad data without a full table scan in a transaction. For UPDATE, the subquery provides the new value or determines which rows to change. For DELETE, it identifies the rows to remove based on complex logic. Both patterns let you avoid race conditions where your filter changes between a SELECT and a subsequent write. The inner query executes first, giving you a snapshot of what to change. Critical: If your subquery returns NULL inadvertently, your UPDATE might set a column to NULL or your DELETE might remove nothing silently. Always test with a SELECT first, wrap in a transaction, and commit only when you see the expected row count.

-- io.thecodeforge
-- Update product prices to match supplier's latest price
UPDATE products p
SET p.price = (
    SELECT s.unit_price
    FROM supplier_prices s
    WHERE s.sku = p.sku
    AND s.effective_date = CURRENT_DATE
)
WHERE EXISTS (
    SELECT 1
    FROM supplier_prices s
    WHERE s.sku = p.sku
    AND s.effective_date = CURRENT_DATE
);