A subquery is a SELECT statement nested inside another SQL statement, whose result the outer statement uses as a single value, a list of values, a yes-or-no test or a temporary table. In SQL, subqueries are also called inner queries or nested queries, and they let a query ask a second question before it answers the first.
Subqueries can appear in the WHERE, HAVING, SELECT and FROM clauses, as well as in INSERT, UPDATE and DELETE statements. Their meaning is defined by the shape of the result they return and by whether they refer to columns of the outer query, which together decide both correctness and performance.
Kinds of subqueries by result shape
The SQL standard groups subqueries by what they return, and each shape is valid only in certain positions. Using the wrong shape, such as a multi-row result where one value is expected, produces a runtime error rather than a syntax error, often only when the data changes.
| Type | Returns | Typical position |
|---|---|---|
| Scalar subquery | One row, one column | SELECT list or comparison with = |
| Row subquery | One row, several columns | Row comparison such as (a, b) = (...) |
| Table subquery | Many rows and columns | FROM clause as a derived table |
| List subquery | Many rows, one column | IN, ANY, ALL |
| Existence subquery | Only whether any row exists | EXISTS, NOT EXISTS |
A scalar subquery that finds no rows yields NULL, and one that finds more than one row raises an error. Defensive queries therefore add an aggregate such as MAX() or a LIMIT 1 with a defined ORDER BY when the business rule allows it.
Correlated and non-correlated subqueries
A non-correlated subquery is self-contained: it can run once, and its result is reused for every row of the outer query. A correlated subquery refers to a column of the outer query, so logically it is evaluated once per outer row, as in finding employees who earn more than the average of their own department.
The example WHERE e.salary > (SELECT AVG(salary) FROM employees d WHERE d.dept_id = e.dept_id) is correlated through e.dept_id. Modern optimizers often rewrite such subqueries into joins, a process called decorrelation or unnesting, so the per-row model describes meaning rather than the actual execution plan.
| Property | Non-correlated | Correlated |
|---|---|---|
| References outer query | No | Yes |
| Logical evaluation | Once | Once per outer row |
| Can run alone | Yes | No |
| Typical use | Compare with a global value or list | Compare each row with its own group |
| Performance risk | Low | High if the optimizer cannot unnest it |
Subquery, JOIN or CTE
Many subqueries can be expressed as a JOIN or a CTE, and the choice affects readability and sometimes the plan. The decisive question is whether the outer query needs columns from the other table or only a filter based on it.
| Need | Best fit | Reason |
|---|---|---|
| Filter by existence only | EXISTS subquery | Stops at the first match, no duplicate rows |
| Return columns from both tables | JOIN | Subqueries in WHERE cannot expose inner columns |
| Reuse a derived result several times | CTE | Defined once, referenced by name |
| Compare with an aggregate | Scalar subquery or window function | Keeps the logic close to the comparison |
| Multi-step transformation | Chained CTEs | Each step readable and testable |
A semi-join, written with EXISTS or IN, returns each outer row at most once, while an equivalent inner join can duplicate outer rows when the inner table has several matches. Rewriting EXISTS as a join therefore often needs a DISTINCT, which adds sorting or hashing work.
A window function frequently replaces a correlated subquery that compares a row with its group, for example AVG(salary) OVER (PARTITION BY dept_id), because it computes the group value in a single pass rather than once per row.
How databases execute subqueries
The query optimizer decides how a subquery actually runs. Common strategies include unnesting into a semi-join or anti-join, materializing the inner result into a temporary hash table, or executing the subquery repeatedly with an index lookup for each outer row.
Capabilities differ between engines and versions. PostgreSQL usually turns EXISTS and IN into semi-joins and may inline CTEs since version 12, while older MySQL versions executed many IN subqueries row by row before semi-join optimizations arrived in 5.6. Reading the execution plan with EXPLAIN is the reliable way to see which strategy was chosen.
Derived tables in the FROM clause may be merged into the outer query or materialized first. Materialization helps when the derived result is small and reused, but it can block the use of indexes on the underlying table.
Common subquery pitfalls
The most dangerous subquery mistake involves NOT IN with NULL values. If the subquery returns even one NULL, x NOT IN (...) evaluates to unknown for every row and the query returns no rows at all, silently. NOT EXISTS does not have this problem and is the safer anti-join form.
| Symptom | Cause | Fix |
|---|---|---|
| Empty result with NOT IN | Subquery returns a NULL | Use NOT EXISTS or filter NULLs |
| More than one row error | Scalar subquery not unique | Aggregate, add a key filter or rewrite as a join |
| Very slow query | Correlated subquery run per row without an index | Index the correlation column or rewrite |
| Unexpected column match | Unqualified name resolves to outer table | Always qualify columns with aliases |
The last case is subtle: if the inner table lacks a referenced column, SQL resolves the name against the outer query, turning a typo into an accidental correlated subquery that runs without error. Qualifying every column with a table alias prevents it.
A non-technical view
A subquery is a question asked inside another question. Suppose you ask a librarian for every book longer than the average book in the library. Before answering, the librarian must first work out the average length, and only then pick the books.
In a database, the inner question works out that average, and the outer question uses it. Subqueries let one request combine several steps of reasoning without the person asking having to do the steps by hand.
A concrete example
A retail analyst needs a list of customers who registered more than a year ago but have never placed an order. The first attempt uses WHERE customer_id NOT IN (SELECT customer_id FROM orders) and unexpectedly returns zero rows, even though the marketing team knows such customers exist.
The cause is a handful of guest orders stored with a NULL customer_id. The analyst rewrites the filter as WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id). The query now returns about four thousand inactive customers, and the execution plan shows an efficient anti-join using the index on orders.customer_id, so the report runs in well under a second.