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.
    Read more.
  • 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 ;
    

    Read more.

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback