Correlated subquery

From Wikipedia, the free encyclopedia
Jump to: navigation, search

In a 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 outer query. The subquery is evaluated once for each row processed by the outer query

Here is an example for a typical correlated subquery. In this example we are finding 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. (A sufficiently smart implementation may cache the inner query's result on a department-by-department basis, but even in the best case the inner query must be executed once per department. See "Optimizing correlated subqueries" below.)

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;

Optimizing correlated subqueries[edit]

The effect of correlated subqueries can in some cases be obtained using joins. For example, the queries above (which use inefficient correlated subqueries) may be rewritten as follows.

 -- This subquery is not correlated with the outer query, and is therefore
 -- executed only once, regardless of the number of employees.
 SELECT employees.employee_number, employees.name
   FROM employees INNER JOIN
     (SELECT department, AVG(salary) AS department_average
       FROM employees
       GROUP BY department) AS temp ON employees.department = temp.department
   WHERE employees.salary > temp.department_average;

Another way of improving performance is to create a view (which is computed once), and then query the view:

 CREATE VIEW dept_avg AS
   SELECT department, AVG(salary) AS department_average
   FROM employees
   GROUP BY department;
 
 -- List employees making more than their department average.
 SELECT employees.employee_number, employees.name
   FROM employees INNER JOIN dept_avg ON employees.department = dept_avg.department
   WHERE employees.salary > dept_avg.department_average;
 
 -- List employees alongside their respective department averages.
 SELECT employees.employee_number, employees.name, dept_avg.department_average
   FROM employees INNER JOIN dept_avg ON employees.department = dept_avg.department;
 
 DROP VIEW dept_avg;

External links[edit]