Correlated Subqueries

A correlated subquery is a type of subquery that depends on the outer query for its values. Unlike a regular subquery, which can run independently, a correlated subquery executes once for each row selected by the outer query.

Key Features

• The inner query references columns from the outer query.
• Evaluates row by row, making it dependent on the outer query.
• Useful for comparisons, filtering, and conditional aggregation.

Syntax

SELECT column1, column2
FROM table1
WHERE column3 = (SELECT column4
                 FROM table2
                 WHERE table2.column5 = table1.column5);

• table1 is the outer query table.
• table2 is the inner query table.
• table2.column5 = table1.column5 shows the dependency of the inner query on the outer query.

Example 1: Finding Employees with Above Average Salary in Their Department

SELECT e1.employee_id, e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (SELECT AVG(e2.salary)
                   FROM employees e2
                   WHERE e2.department_id = e1.department_id);

• The inner query calculates the average salary for each department.
• The outer query retrieves employees earning more than their department’s average.

Example 2: Departments with the Highest Paid Employee

SELECT d.department_name, e.name, e.salary
FROM departments d
JOIN employees e ON d.department_id = e.department_id
WHERE e.salary = (SELECT MAX(salary)
                  FROM employees
                  WHERE department_id = d.department_id);

• The inner query finds the maximum salary for each department.
• The outer query lists employees earning that maximum salary.

Advantages

• Allows complex row-by-row comparisons.
• Can perform conditional aggregations and ranking.
• Useful when joining alone cannot achieve the required logic.

Limitations

• Can be slower for large datasets because the inner query executes multiple times.
• Requires careful indexing for performance optimization.

Tips

• Use indexes on the columns referenced in the correlated subquery.
• Test performance on large tables; sometimes joins or window functions are faster alternatives.
• Understand the dependency of inner query on outer query to avoid errors.