Correlated Nested Queries

tags: #sql/complex_queries

A correlated nested query is a type of subquery that depends on the outer query for its value, i.e., the inner query references a table or column from the outer query.

This is evaluated for each tuple in the outer query. As a result, correlated nested queries can be slower than non-correlated nested queries as the inner query has to be executed for each row in the outer query.

Example:

SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE E.Salary > ( // computing the avg salary of dep corr. to emp in that iter.
  SELECT AVG(Salary)
  FROM EMPLOYEE
  WHERE Dno = E.Dno 
  // referencing the Dno from the employee relation in outer.
  // This creates a correlation between the nested query and the outer query.
);

In this query, we want to retrieve the first and last names of employees whose salaries are greater than the average salary of their department.

• For each tuple in the outer query (i.e., each employee in the EMPLOYEE table), the inner query calculates the average salary of the employees in the same department as the employee in the outer query.
• Then, the WHERE clause in the outer query checks if the salary of the employee in the outer query is greater than the average salary of their department (which was calculated in the inner query).
• If it is, then that tuple is returned in the result set.