About correlated sub-query

A correlated sub-query is a term used for specific types of queries in SQL. It is a sub-query (a query nested inside another query) that uses values from the outer query in its WHERE clause. The sub-query is evaluated once for each row processed by the outer query.

Here is an example for a typical correlated sub-query. In this example we are finding the list of employees (employee number and names) having more salary than the average salary of all employees in that employee's department.

SELECT employee_number, name
FROM employee AS e1
WHERE salary > (SELECT avg(salary)
    FROM employee
    WHERE department = e1.department);

In the above query the outer query is,

SELECT employee_number, name
FROM employee AS e1
WHERE salary >

And the inner query is,

(SELECT avg(salary)
    FROM employee
    WHERE department = e1.department);

In the above nested query the inner query has to be executed for every employee as the department will change for every row. Hence the average salary will also change.