SQL Subquery Guide: Types, Examples, and Refactoring

What is a subquery in SQL?

SELECT product_id, price
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
);

The inner query returns one average and the outer query compares every product with it. This shape is concise because the inner result has a guaranteed scalar grain. Before using any subquery, write its contract: which outer values it can see, how many rows and columns it may return, and how zero rows or NULL should affect the answer.

Choose a subquery type from the question you need to answer

Subqueries are not one technique. A scalar subquery supplies one value. A row subquery supplies a tuple where the database supports row comparison. A set subquery feeds IN, ANY, or ALL. An existence subquery answers whether at least one qualifying record exists. A derived table creates an intermediate rowset that can be joined, filtered, or aggregated. A correlated subquery reads values from the current outer row.

Choose the shape that expresses intent directly. Returning full child rows and later deduplicating them is a poor substitute for EXISTS when the question is purely existential. Conversely, EXISTS cannot provide child attributes that belong in the output. Clarity about the contract prevents accidental grain changes.

Make scalar subqueries truly single-valued

A scalar subquery can appear in a comparison, projection, calculation, or conditional expression. Zero returned rows usually becomes NULL; more than one row normally raises an error. The dangerous pattern is relying on accidental uniqueness or adding an unordered LIMIT 1. That suppresses the error but does not define which related row is correct.

SELECT
  c.customer_id,
  (
    SELECT s.status
    FROM customer_status s
    WHERE s.customer_id = c.customer_id
    ORDER BY s.effective_at DESC, s.status_id DESC
    FETCH FIRST 1 ROW ONLY
  ) AS latest_status
FROM customers c;

The secondary key makes ties deterministic, but verify that “latest” is the right business rule and that late-arriving corrections are modeled correctly. If several scalar subqueries independently probe the same child table, calculate the desired child row once with a lateral join, windowed derived table, or named stage instead of repeating the access path.

Use IN and EXISTS with deliberate NULL semantics

IN asks whether a value equals any value returned by a subquery. EXISTS asks whether the subquery returns at least one row, and the selected columns inside it are irrelevant. Optimizers can often transform both into semi-join strategies, so choose by meaning first. The largest correctness difference appears with negation and NULL.

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'open'
);

NOT IN can become unknown for every outer value when the returned set contains NULL, producing no rows. You can filter nulls deliberately, but NOT EXISTS usually communicates anti-existence more safely. Test a null outer key, a null inner key, an empty inner set, and a mixture of matching and nonmatching rows. Do not assume constraints exist unless the schema actually enforces them.

Recognize correlation and repeated-work risk

A correlated subquery references a column from the outer query. Logically, it is evaluated for the current outer row, although an optimizer may decorrelate it into a join, semi-join, aggregate, or other set operation. Correlation is useful when the inner predicate naturally depends on each entity, such as comparing an employee with the average of that employee's department.

SELECT e.employee_id, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(x.salary)
  FROM employees x
  WHERE x.department_id = e.department_id
);

Inspect the actual plan rather than assuming one execution per row or perfect decorrelation. Warning signs include a large outer input, millions of inner loops, repeated scans, volatile functions, range predicates, and weak indexes. A pre-aggregated department stage joined once may be clearer and faster, but it can change NULL or empty-group behavior, so prove equivalence with edge cases.

Use derived tables to establish a controlled intermediate grain

A subquery in FROM creates a derived table. This is valuable when a stage must filter, aggregate, rank, or reshape data before another relationship. Name every output column clearly and document what one row represents. If the derived table aggregates orders to one row per customer, the outer join should rely on that grain rather than assuming it.

SELECT c.customer_id, x.order_count, x.revenue
FROM customers c
JOIN (
  SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(order_total) AS revenue
  FROM orders
  GROUP BY customer_id
) x ON x.customer_id = c.customer_id;

This prevents order rows from multiplying another customer-level relationship, but it also removes customers without orders because the outer join is inner. Choose join preservation independently from aggregation. Some engines materialize a stage, while others inline it; treat a derived table as a semantic boundary first, not a guaranteed performance barrier.

Limit nesting that hides grain and decision points

Deep nesting is not automatically slow, but it increases review cost. Readers must track aliases, correlated references, aggregation levels, preservation rules, and filters across several scopes. Repeated subqueries can also calculate the same intermediate result more than once. A useful refactoring extracts stages when they have a nameable business meaning, a reusable result, or a validation invariant.

1. Annotate grain. Write what one row means after every subquery.
2. Mark outer references. Identify correlation and expected probe counts.
3. Find repeated logic. Compare predicates, joins, and aggregates structurally.
4. Extract named stages. Use a CTE or view when the stage deserves an identity.
5. Validate boundaries. Measure rows, distinct keys, nulls, and totals at each stage.
6. Inspect the final plan. Confirm whether the engine inlines, materializes, or repeats work.

Diagnose subquery performance with execution evidence

Start by locating work that grows with the outer row count. In an actual plan, inspect loops, rows per loop, total rows, buffer reads, elapsed time, sorts, spills, and remote calls. A cheap inner probe repeated ten times may be fine; the same probe repeated ten million times is not. Parameter-sensitive selectivity and skew can make one execution fast and another pathological.

Do not optimize by syntax folklore. Replacing every subquery with a join can introduce row multiplication; forcing materialization can add I/O; flattening a scalar check can change zero-row behavior. Benchmark the original and rewrite with identical parameters and validate result equivalence before comparing runtime.

Validate subquery contracts before production

• State whether zero, one, or many rows are allowed.
• Test empty results, one result, several results, and NULL values.
• For scalar selection, define a deterministic business rule instead of arbitrary limiting.
• For NOT IN, prove the returned expression cannot be NULL or use a safer form.
• Measure correlated loop counts with realistic outer populations.
• Compare row counts, distinct keys, and metrics before and after any rewrite.
• Review access permissions and remove sensitive literals before using external tools.
• Keep a regression fixture for ties, late data, duplicate keys, and empty groups.

Trace a subquery incident from symptom to mechanism

Suppose a customer dashboard suddenly reports no eligible accounts after a release. The visible predicate uses account_id NOT IN (SELECT blocked_account_id FROM blocks). A new ingestion path has introduced one row whose blocked key is NULL. Because comparison with that set becomes unknown, the outer predicate rejects every account. The immediate repair may filter NULL or use NOT EXISTS, but a complete review also asks why a supposedly identifying field accepted NULL and whether other consumers share the same assumption.

Build the proof in stages. Preserve the original row counts, list null and duplicate keys in the inner result, evaluate the predicate for a tiny set of representative accounts, and compare the repaired query with an independent anti-join control. Then inspect the new actual plan: a semantically safer form can still need an index or updated statistics. Save the failing NULL fixture as a regression test and monitor both blocked-key null rate and eligible-account count. This workflow fixes the mechanism instead of merely restoring today's dashboard.

SQL subquery frequently asked questions

Continue with a related guide

Use these closely related guides to verify the next layer of the workflow without mixing separate troubleshooting or decision intents.

• Nested SQL queries
• Correlated subqueries
• Common table expressions