What is correlated subquery?
Explanation
Correlated subquery (also known synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query.
Consider the following example:
````SELECT * FROM users WHERE users.id IN (SELECT vip_users.id FROM vip_users WHERE vip_users.id = users.id) `The inner query depends on the values from the outer query.
Moreover the `IN (...)` statement cannot be executed by itself because it depends on the values from users table. Such query is called a correlated subquery.
```
Theory
• #### 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.
• #### Correlated subquery

Correlated subqueries may appear elsewhere besides the WHERE clause; for example, this query uses a correlated subquery in the SELECT clause to print the entire list of employees alongside the average salary for each employee's department.
Again, because the subquery is correlated with a column of the outer query, it must be re-executed for each row of the result.
``````SELECT
employee_number,
name,
(SELECT AVG(salary)
FROM employees
WHERE department = Bob.department) AS department_average
FROM employees AS Bob
group by employee_number,name ;
``````