CORRELATED SUBQUERY SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

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

SQL CORRELATED SUBQUERY Compatibility Across Databases
Database System	Support Status	Since Version	Notes
MySQL	Native	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	Native	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	Native	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	Native	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	Native	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: Native since 4.1 PostgreSQL: Native since 6.0 SQL Server: Native since 6.5 Oracle: Native since 7 SQLite: Native since 3.0

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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;