SQL Correlated Subqueries

A correlated subquery is a subquery that depends on values from the outer query. Unlike a regular (non-correlated) subquery, it is evaluated once for every row in the outer query. This makes correlated subqueries dynamic and highly useful for solving complex database problems like row-by-row comparisons, filtering, and conditional updates.

• Executes once for each row of the outer query.
• Subquery uses values from the outer query.
• Ideal for ranking, row-specific calculations, and conditional logic.

Syntax:

SELECT column1, column2, ...
FROM table1 t1
WHERE column1 operator
      (SELECT column
       FROM table2
       WHERE expr1 = t1.expr2);

Correlated Subquery Examples

Correlated subqueries are best understood through practical use cases. Below are some common scenarios where they are used to filter, update, or compare data row by row.

Consider the following two tables for the examples below:

1. Fetching Data Based on Row-Specific Conditions

Correlated subqueries are often used when you need to filter data based on a condition that involves comparing values from the outer query.

 Query:

SELECT last_name, salary, department_id
FROM employees AS outer
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = outer.department_id
);

Output:

• Selects employees whose salary is above the average salary of their department.
• Uses a subquery to calculate the department’s average salary for comparison.

2. Using Correlated Subqueries with UPDATE

Correlated subqueries can also be used with UPDATE statements to modify data based on related information from another table.

Query:

UPDATE employees
SET salary = ROUND(
    (SELECT AVG(salary)
     FROM employees
     WHERE department_id = employees.department_id), 2
)
WHERE department_id = 101;

Output:

• Updates the salary of employees in department 101 to the average salary of their department.
• Uses a subquery to calculate the average salary for each department and rounds it to two decimal places.

3. Using Correlated Subqueries with DELETE

We can use a correlated subquery within a DELETE statement to remove rows from one table based on conditions in another table. 

Query:

DELETE FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM employees
    WHERE department_id = 101
);

Output:

• Deletes employees who belong to department 101.
• Uses a subquery to select the employee_ids of employees in department 101.

4. Using EXISTS with Correlated Subqueries

The EXISTS operator is often used in correlated subqueries to test if a subquery returns any rows. It returns TRUE if the subquery has at least one row.

Query:

SELECT e.employee_id, e.last_name, e.job_id, e.department_id
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM employees sub
    WHERE sub.manager_id = e.employee_id
);

Output:

• Selects employees who are managers of at least one other employee.
• Uses a subquery to check if any employee has manager_id matching the outer employee’s employee_id.

5. Using NOT EXISTS with Correlated Subqueries

The NOT EXISTS operator is used to check if a subquery does not return any rows. This is useful for finding records that do not match specific criteria.

Query:

SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

Output:

• Selects departments that have no employees.
• Uses a subquery to check for the absence of matching employees in each department.

Nested vs. Correlated Subqueries

Here are the differences between nested and correlated subqueries:

Performance Considerations

WHERE col > (SELECT AVG(col) FROM table2 WHERE table2.id = outer.id)

• Can be slow on large datasets due to repeated execution.
• Use JOINs when possible for better performance.
• Ensure proper indexes on columns used in filtering.