‹ BACK

Correlated subquery

Correlated subquery

A correlated subquery (or synchronized subquery) is a subquery that uses values from the outer query.
Because the subquery is evaluated once for each row processed by the outer query, it can be inefficient.

Example

The list of all employees whose salary is above average for their departments:
SELECT employee_number, name
 FROM employees AS Bob
 WHERE salary > (
   SELECT AVG(salary)
     FROM employees
     WHERE department = Bob.department);
In the above query the outer query is
SELECT employee_number, name
   FROM employees AS Bob
   WHERE salary > ...
 
and the inner query (the correlated subquery) is
SELECT AVG(salary)
 FROM employees
 WHERE department = Bob.department

In the above nested query the inner query has to be re-executed for each employee.
Read more.
sub-queries correlated-subquery synchronized-subquery

To check your knowledge the following tests are recommended:
Check your sql sql skills.