Correlated Subquery

Correlated Subquery is a SQL statement in the Subqueries category. A subquery that references columns from the outer query. Evaluated for each row of the outer query. The syntax is SELECT * FROM table1 WHERE column operator (SELECT column FROM table2 WHERE table2.id = table1.id);. It returns filtered result set. A typical example: SELECT e.name, e.salary FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id); -- Employees earning above their department average A close relative is Scalar Subquery, which a subquery that returns a single value (one row, one column). Can be used in SELECT, WHERE, HAVING clauses. A close relative is EXISTS, which returns TRUE if the subquery returns at least one row. Often faster than IN for large datasets. A close relative is IN Subquery, which checks if a value matches any value returned by the subquery. More about this category: Nested queries — correlated, EXISTS, IN, ANY, ALL, scalar subqueries.