Last Updated: 2024-11-21

Say you want the names of every employee whose salary is above the average in that particular employee's department - and the salary must be calculated dynamically.

This calls for a "correlated subquery": In an SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query.

For example:

 SELECT employee_number, name
 FROM employees emp
 WHERE salary > (
   SELECT AVG(salary)
     FROM employees
     WHERE department = emp.department
 );

Notice the use of an alias ("emp") and how it is referenced within the (indented) subquery.

Note: Because the subquery may be evaluated once for each row processed by the outer query, this can be slow.