Correlated Subqueries

Reference the outer query's alias inside the subquery.

SELECT column_name
FROM table_name AS outer_alias
WHERE column_name operator (
    SELECT aggregate_function(column_name)
    FROM table_name AS inner_alias
    WHERE inner_alias.group_key = outer_alias.group_key
);

Correlated subquery combined with EXISTS.

SELECT column_name
FROM table_name AS a
WHERE EXISTS (
    SELECT 1
    FROM other_table AS b
    WHERE b.foreign_key = a.primary_key
      AND b.condition_col = 'value'
);

The following members table is used in the examples below.

Retrieve members whose score is higher than the average score of their own role.

sample_correlated_subquery.sql

SELECT m.name, m.role, m.score
FROM members AS m
WHERE m.score > (
    SELECT AVG(sub.score)
    FROM members AS sub
    WHERE sub.role = m.role
)
ORDER BY m.role, m.score DESC;

+--------+-----------+-------+
| name   | role      | score |
+--------+-----------+-------+
| user_a | senior  |   480 |
| user_e | junior |   620 |
+--------+-----------+-------+
2 rows in set

Use EXISTS to retrieve members who have at least one colleague in the same role with a higher score. The SELECT 1 in the subquery has no special meaning — it is simply a way to check whether any row exists.

sample_correlated_subquery.sql

SELECT m.name, m.role, m.score
FROM members AS m
WHERE EXISTS (
    SELECT 1
    FROM members AS sub
    WHERE sub.role = m.role
      AND sub.score > m.score
);

+--------+-----------+-------+
| name   | role      | score |
+--------+-----------+-------+
| user_c | senior  |   320 |
| user_b | junior |   390 |
+--------+-----------+-------+
2 rows in set

Use the SELECT clause to calculate each member's rank within their role.

sample_correlated_subquery.sql

SELECT
    m.name,
    m.role,
    m.score,
    (
        SELECT COUNT(*)
        FROM members AS sub
        WHERE sub.role = m.role
          AND sub.score > m.score
    ) + 1 AS rank_in_role
FROM members AS m
ORDER BY m.role, rank_in_role;

+--------+-----------+-------+--------------+
| name   | role      | score | rank_in_role |
+--------+-----------+-------+--------------+
| user_a | senior  |   480 |            1 |
| user_c | senior  |   320 |            2 |
| user_e | junior |   620 |            1 |
| user_b | junior |   390 |            2 |
+--------+-----------+-------+--------------+
4 rows in set

The correlated subquery syntax works across all major databases.

SELECT m.name, m.role, m.score
FROM members AS m
WHERE m.score > (
    SELECT AVG(sub.score)
    FROM members AS sub
    WHERE sub.role = m.role
)
ORDER BY m.role, m.score DESC;

In Oracle, the AS keyword cannot be used for table aliases. Omit AS when specifying a table alias.

SELECT m.name, m.role, m.score
FROM members m
WHERE m.score > (
    SELECT AVG(sub.score)
    FROM members sub
    WHERE sub.role = m.role
)
ORDER BY m.role, m.score DESC;

A correlated subquery executes once for every row of the outer query. If the outer query returns N rows, the subquery runs N times, so using a correlated subquery on large datasets can significantly degrade performance. The same result can often be rewritten using window functions (OVER / PARTITION BY) or a JOIN, and a rewrite is worth considering for large tables.

When combined with EXISTS, evaluation stops as soon as the subquery finds one matching row, making it more efficient than IN. A correlated subquery in the SELECT clause is easy to read but carries a higher execution cost, so use it with care.

For the basics of subqueries, see Subquery (scalar / row). For how to use EXISTS, see IN / EXISTS.