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.

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback
Keep exploring
SQL snippets
Cosmo
Sign Up Now
or Subscribe for future quizzes