# Correlated Subqueries: Definition and SQL Examples

Correlated subqueries are subqueries that reference a column from the outer query, which means they cannot run on their own. The database re-evaluates the inner query once for each row the outer query produces, so the inner result changes from row to row. This article defines the term, shows how the mechanism works, and walks through a complete example you can run yourself.

## Quick Answer

- A correlated subquery contains a reference to a table or alias from the surrounding query, called an outer reference.
- Because of that reference, the subquery cannot be executed once and reused. It runs once per candidate outer row.
- The correlation usually appears in the subquery's `WHERE` clause, as in `WHERE e2.dept = e.dept`.
- PostgreSQL describes this behavior directly: the subquery can refer to variables from the surrounding query, which act as constants during any one evaluation of the subquery [1].
- Correlated subqueries are common with `EXISTS`, `IN`, and comparisons against aggregates such as `AVG` or `MAX`, but MySQL notes they can be inefficient compared with a join or a window function [2].

## What Correlated Subqueries Mean

A correlated subquery is a nested `SELECT` statement that depends on the row currently being processed by the outer query. The dependency is the defining feature. Remove it and you have an ordinary, uncorrelated subquery that the database can evaluate once.

The plain definition: the inner query reads a value from the outer query, so the two queries are linked row by row.

The precise definition: a subquery is correlated when it contains an outer reference, meaning a column reference that resolves to a table in the enclosing query rather than to a table in the subquery's own `FROM` clause. MySQL's optimizer documentation draws the line at exactly this point, describing a correlated condition as an equality predicate where one side refers exclusively to tables outside the subquery and the other side refers exclusively to tables inside it [3].

That single reference changes everything about execution. An uncorrelated subquery is a self-contained value, like a constant. A correlated subquery is a function of the outer row.

If you want the broader picture first, see [Subqueries in SQL: Types, Syntax and Examples](/blog/data-analysis/subqueries-in-sql-types-examples).

## How It Works

The mechanism is a nested loop. For each row the outer query considers, the database substitutes the outer row's values into the subquery, runs the subquery, and uses the returned value in the outer `WHERE` or `SELECT` clause.

You can write the general shape as:

$$
\text{keep outer row } r \iff f(r) \text{ satisfies the outer condition}
$$

where:

- $r$ is the current candidate row from the outer query.
- $f(r)$ is the subquery, evaluated with $r$'s column values bound to the outer reference.
- The outer condition is the comparison, such as `e.salary > f(r)`.

The number of subquery executions is at most the number of rows the outer query scans before filtering. That is why cost grows quickly. A table with 10,000 rows can trigger 10,000 inner evaluations unless the optimizer rewrites the query.

Two details matter in practice. First, the outer reference acts as a constant inside a single evaluation, so the subquery sees one fixed value per run [1]. Second, `EXISTS` stops as soon as it finds one matching row, because it only needs to know whether any row exists [1]. That makes `EXISTS` correlated subqueries cheaper than they first appear.

## Worked Example

The dataset is a small company schema with a `departments` table and an `employees` table. Here is the `departments` table.

| dept | budget |
|---|---|
| Engineering | 1200000 |
| Sales | 800000 |
| Marketing | 500000 |
| Support | 300000 |

The task is to list every employee who earns more than the average salary of their own department. The query uses a correlated subquery for that comparison.

```sql
SELECT e.name, e.dept, e.salary
FROM employees AS e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees AS e2
  WHERE e2.dept = e.dept
)
ORDER BY e.dept, e.salary DESC;
```

The correlation is the condition `e2.dept = e.dept`. The inner query reads `e.dept` from the outer query, so it cannot be evaluated once. For Alice, the subquery computes the Engineering average. For Dave, it computes the Sales average. The value changes with every outer row.

The result, checked with an equivalent SQLite query:

| name | dept | salary |
|---|---|---|
| Alice | Engineering | 145000 |
| Grace | Marketing | 88000 |
| Eve | Sales | 95000 |
| Judy | Support | 52000 |

Four employees out of ten beat their department average. Alice earns 145000 against an Engineering average of 121333. Grace earns 88000 against a Marketing average of 71000. Eve earns 95000 against a Sales average of 76000. Judy earns 52000 against a Support average of 49500.

## How to Interpret It

Read the output as "employees who stand out within their own group." The comparison is relative, not absolute. Judy's 52000 is the lowest salary in the result set, yet she still qualifies because Support pays less on average than any other department.

This is the main interpretive point about correlated subqueries. The threshold is not fixed. It is recomputed per group, per row. If you mentally substitute a single global average, you will misread the result. The global average across all ten employees is 83300, and five employees clear that bar: Alice, Bob, Carol, Eve and Grace. Bob and Carol beat the global average but not the Engineering average, while Judy beats only her own department's average. The correlated version returns four rows because each department supplies its own benchmark.

When you see a correlated subquery in someone else's code, look for the outer reference first. That tells you the grouping key. Everything else follows from it.

## When to Use It (and when not to)

Use a correlated subquery when the comparison is naturally per-group and you want the logic to read that way. Typical cases:

- Filtering rows against a group aggregate, as in the example above.
- Testing existence with `EXISTS`, such as finding departments that have at least one employee above a threshold.
- Producing a per-row scalar value in the `SELECT` list, such as each employee's department headcount.

Avoid it when a set-based alternative is clearer or faster. MySQL's tutorial on group-wise maximums shows the same problem solved three ways: a correlated subquery, an uncorrelated `LEFT JOIN`, and a common table expression with a window function. The documentation flags the correlated version as potentially inefficient [2]. For a deeper look at the join alternative, see [CROSS JOIN vs LEFT JOIN in SQL: Differences and Examples](/blog/data-analysis/cross-join-vs-left-join-sql).

The practical rule: if the inner query does not actually need the outer row, remove the correlation. If it does, consider whether a window function expresses the same idea with one pass over the data. The [Common Table Expression SQL: Syntax and Examples](/blog/data-analysis/common-table-expression-sql) guide covers that rewrite pattern.

## Correlated Subqueries vs Uncorrelated Subqueries

The closest related idea is the uncorrelated subquery, which runs independently of the outer query.

| Property | Correlated subquery | Uncorrelated subquery |
|---|---|---|
| Outer reference | Yes, references an outer table or alias | No |
| Executions | Once per candidate outer row | Once for the whole statement |
| Can run standalone | No | Yes |
| Typical operators | `EXISTS`, `IN`, comparisons to aggregates | `IN`, `NOT IN`, scalar comparisons |
| Result stability | Changes per outer row | Same value for every outer row |
| Cost profile | Grows with outer row count | Fixed, evaluated once |

A useful test: copy the subquery into a fresh editor and run it. If it fails because a column is unknown, it is correlated. If it returns a value, it is not.

## Common Mistakes

- **Forgetting the alias in the outer reference.** Writing `WHERE dept = dept` inside the subquery makes both sides resolve to the inner table, so the condition is always true. Fix it by qualifying both sides, as in `e2.dept = e.dept`.
- **Assuming the subquery runs once.** If you expect a single global average but the subquery references the outer row, you get a per-row value. Fix it by removing the outer reference if a global value is what you want.
- **Using a correlated subquery where a join is faster.** MySQL explicitly notes the inefficiency [2]. Fix it by rewriting as a join against a grouped subquery or a window function.
- **Returning more than one column or row in a scalar position.** A subquery used with `>` or `=` must return one value. Fix it by adding `LIMIT 1` only when the choice is genuinely arbitrary, or by switching to `EXISTS` when you only need a yes or no.
- **Ignoring NULL behavior.** `NOT IN` against a subquery that returns NULL can produce no rows at all. Fix it by using `NOT EXISTS` instead.
- **Nesting correlations three levels deep.** Deep nesting is hard to read and hard for optimizers to rewrite. Fix it by flattening with a common table expression.

## Limitations

A correlated subquery cannot be evaluated in isolation, so you cannot test the inner query by running it alone. That makes debugging slower than for an uncorrelated subquery, where you can paste the inner `SELECT` into a new window and inspect its output.

Performance is the other limit. The nested-loop execution model means cost scales with the product of outer rows and inner work, and the optimizer may or may not rewrite the query into something set-based. MySQL's own documentation warns about the inefficiency and points to joins and window functions as alternatives [2]. On large tables, always compare execution plans before committing to the correlated form.

There is also a definitional trap. MySQL's optimizer documentation notes that its internal definition of a correlated subquery is made for implementation work and is incompatible with the normal SQL definition [3]. So a query that looks correlated to you may be classified differently by the engine, and the reverse can happen. Trust the execution plan, not the label.

## Frequently Asked Questions

### What is a correlated subquery in simple terms?

It is a subquery that uses a value from the query around it. Because that value changes with each outer row, the subquery has to run again for every row. The link between the two queries is called the correlation.

### How do I know if a subquery is correlated?

Look for a column reference that belongs to the outer query. In `WHERE e2.dept = e.dept`, the `e.dept` part comes from outside the subquery, so the subquery is correlated. If every column in the subquery resolves to a table in its own `FROM` clause, it is uncorrelated.

### Are correlated subqueries slow?

They can be, because they may execute once per outer row. MySQL's documentation describes them as potentially inefficient and suggests uncorrelated joins or common table expressions with window functions as alternatives [2]. The actual cost depends on indexes, table sizes, and whether the optimizer rewrites the query.

### Can I always rewrite a correlated subquery as a join?

Often, but not always. Group-wise maximum problems have well-known join and window-function equivalents [2]. Cases involving `EXISTS` with early termination can be harder to match, since the subquery stops at the first match [1]. Test both versions on your data.

### What is the difference between a correlated subquery and a nested query?

A nested query is any query inside another query. A correlated nested query is the specific case where the inner query references the outer one. All correlated subqueries are nested queries, but most nested queries are not correlated.

For more practice with the surrounding syntax, see [SQL Query Examples: 10 Practical SELECT Statements](/blog/data-analysis/sql-query-examples) and [Using CASE in SQL: Syntax and Examples](/blog/data-analysis/using-case-in-sql).

## References

1. [PostgreSQL: Documentation: 18: 9.24. Subquery Expressions](https://www.postgresql.org/docs/current/functions-subquery.html)
2. [MySQL :: MySQL Tutorial :: 7.4 The Rows Holding the Group-wise Maximum of a Certain Column](https://dev.mysql.com/doc/mysql-tutorial-excerpt/8.0/en/example-maximum-column-group-row.html)
3. [MySQL :: WL#4389: Subquery optimizations: Make IN optimizations also handle EXISTS](https://dev.mysql.com/worklog/task/?id=4389)

## Further Reading

- [Codd EF (1970). A relational model of data for large shared data banks. Communications of the ACM](https://doi.org/10.1145/362384.362685)
- [PostgreSQL Documentation: SELECT](https://www.postgresql.org/docs/current/sql-select.html)
- [SQLite: SQL As Understood By SQLite](https://www.sqlite.org/lang.html)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)

## Related Articles

- [Subqueries in SQL: Types, Syntax and Examples](/blog/data-analysis/subqueries-in-sql-types-examples)
- [SQL UNION Operator: Syntax, Examples and Differences](/blog/data-analysis/sql-union-operator-syntax-examples)
- [SQL Query Examples: 10 Practical SELECT Statements](/blog/data-analysis/sql-query-examples)
- [Common Table Expression SQL: Syntax and Examples](/blog/data-analysis/common-table-expression-sql)
- [SQL FULL OUTER JOIN: Syntax and Examples](/blog/data-analysis/sql-full-outer-join-syntax-examples)