CORRELATED SUBQUERYQuery Techniques
Buy Me A Coffee

A subquery that references columns from the outer query. The subquery is re-evaluated once per row of the outer query, creating a row-by-row dependency. Powerful but potentially slow on large datasets — often replaceable with a JOIN or window function.

A subquery that returns exactly one value (one column, one row), usable anywhere a single value is expected. If the subquery returns multiple rows, execution halts. Wrap in COALESCE or use aggregation to guarantee a single value when the result may be empty.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 4.1 Correlated subqueries supported since 4.1. MySQL historically had poor optimization of correlated subqueries (particularly NOT IN / NOT EXISTS patterns), materializing the outer loop inefficiently. MySQL 8.0 significantly improved the optimizer with hash semi-join and anti-join transformations. For large tables, rewriting as a JOIN or using a CTE is often much faster in MySQL.
PostgreSQL ✓ Supported 6.0 Fully supported. PostgreSQL's optimizer can decorrelate many correlated subqueries automatically, converting them to hash joins or merge joins. EXISTS and NOT EXISTS correlated subqueries are well-optimized. LATERAL joins (PostgreSQL 9.3+) are often a cleaner and more performant alternative for correlated subqueries in the FROM clause.
SQL Server ✓ Supported 6.5 Fully supported. SQL Server's optimizer generally handles correlated subqueries well, transforming them into apply or join operations internally. APPLY (CROSS APPLY / OUTER APPLY) is the SQL Server equivalent of LATERAL and is often preferred for complex correlated logic in the FROM clause.
Oracle ✓ Supported 7 Fully supported. Oracle's cost-based optimizer (CBO) can decorrelate subqueries. The LATERAL keyword (Oracle 12c+) and CROSS APPLY / OUTER APPLY (also 12c+) provide alternatives. Correlated subqueries in SELECT list, WHERE, and HAVING are all valid.
SQLite ✓ Supported 3.0 Correlated subqueries supported. SQLite does not decorrelate them automatically — each row triggers a subquery execution, so performance degrades on large datasets. Rewriting as a JOIN (SQLite 3.8.3+ with improved query planner) or a window function (3.25+) is strongly recommended for production queries over large tables.

Details

A correlated subquery is logically re-executed once per outer row, making it O(n) on the outer table — the classic performance risk. All engines can optimize common patterns (the query plan may show a hash join or semi-join rather than a nested loop), but the optimization is engine-specific and not guaranteed. The most reliable performance fix is rewriting as a window function (supported everywhere in post-2012 engines), which processes the aggregation in a single pass. LATERAL (PostgreSQL 9.3, Oracle 12c) and CROSS APPLY (SQL Server, Oracle 12c) are more expressive alternatives for complex correlated FROM-clause logic.

Standard Syntax

-- Find employees earning more than their department average: SELECT name, dept, salary FROM employees e_outer WHERE salary > ( SELECT AVG(salary) FROM employees e_inner WHERE e_inner.dept = e_outer.dept );

Version Support

MySQL: Since 4.1 PostgreSQL: Since 6.0 SQL Server: Since 6.5 Oracle: Since 7 SQLite: Since 3.0

Per-Database Syntax & Notes

MySQL

Correlated subqueries supported since 4.1. MySQL historically had poor optimization of correlated subqueries (particularly NOT IN / NOT EXISTS patterns), materializing the outer loop inefficiently. MySQL 8.0 significantly improved the optimizer with hash semi-join and anti-join transformations. For large tables, rewriting as a JOIN or using a CTE is often much faster in MySQL.

-- Above-average salary per dept: SELECT name, dept, salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE dept = e.dept ); -- Correlated EXISTS (efficient anti-join pattern): SELECT name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY) ); -- Equivalent JOIN (often faster in older MySQL): SELECT c.name FROM customers c JOIN orders o ON o.customer_id = c.id AND o.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY c.id, c.name;

PostgreSQL

Fully supported. PostgreSQL's optimizer can decorrelate many correlated subqueries automatically, converting them to hash joins or merge joins. EXISTS and NOT EXISTS correlated subqueries are well-optimized. LATERAL joins (PostgreSQL 9.3+) are often a cleaner and more performant alternative for correlated subqueries in the FROM clause.

-- Above-average salary per dept: SELECT name, dept, salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE dept = e.dept ); -- LATERAL alternative (often cleaner): SELECT e.name, e.dept, e.salary, dept_avg.avg_salary FROM employees e CROSS JOIN LATERAL ( SELECT AVG(salary) AS avg_salary FROM employees WHERE dept = e.dept ) dept_avg WHERE e.salary > dept_avg.avg_salary; -- Window function alternative (usually fastest): SELECT name, dept, salary FROM ( SELECT name, dept, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg FROM employees ) t WHERE salary > dept_avg;

SQL Server

Fully supported. SQL Server's optimizer generally handles correlated subqueries well, transforming them into apply or join operations internally. APPLY (CROSS APPLY / OUTER APPLY) is the SQL Server equivalent of LATERAL and is often preferred for complex correlated logic in the FROM clause.

-- Correlated subquery: SELECT name, dept, salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE dept = e.dept ); -- CROSS APPLY alternative: SELECT e.name, e.dept, e.salary, da.avg_salary FROM employees e CROSS APPLY ( SELECT AVG(salary) AS avg_salary FROM employees WHERE dept = e.dept ) da WHERE e.salary > da.avg_salary; -- Window function (often optimal): SELECT name, dept, salary FROM ( SELECT name, dept, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg FROM employees ) t WHERE salary > dept_avg;

Oracle

Fully supported. Oracle's cost-based optimizer (CBO) can decorrelate subqueries. The LATERAL keyword (Oracle 12c+) and CROSS APPLY / OUTER APPLY (also 12c+) provide alternatives. Correlated subqueries in SELECT list, WHERE, and HAVING are all valid.

-- Correlated subquery: SELECT name, dept, salary FROM employees e_outer WHERE salary > ( SELECT AVG(salary) FROM employees WHERE dept = e_outer.dept ); -- LATERAL alternative (12c+): SELECT e.name, e.dept, e.salary, da.avg_salary FROM employees e, LATERAL ( SELECT AVG(salary) AS avg_salary FROM employees WHERE dept = e.dept ) da WHERE e.salary > da.avg_salary; -- Correlated in SELECT list: SELECT name, (SELECT dept_name FROM departments WHERE id = e.dept_id) AS dept_name FROM employees e;

SQLite

Correlated subqueries supported. SQLite does not decorrelate them automatically — each row triggers a subquery execution, so performance degrades on large datasets. Rewriting as a JOIN (SQLite 3.8.3+ with improved query planner) or a window function (3.25+) is strongly recommended for production queries over large tables.

-- Correlated subquery: SELECT name, dept, salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE dept = e.dept ); -- Window function alternative (3.25+, better performance): SELECT name, dept, salary FROM ( SELECT name, dept, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg FROM employees ) WHERE salary > dept_avg;