View Categories

What is a correlated subquery?

Parthiban Kannan
Updated on December 2, 2025

1 min read

A correlated subquery is a type of subquery that depends on the outer query for its values — meaning the inner query is executed once for every row of the outer query. Unlike a regular subquery, which runs independently, a correlated subquery dynamically uses values from the current row being processed by the outer query.

For example, if I want to find employees who earn more than the average salary of their own department,

🗃️
filename.sql 
SELECT e.EmployeeName, e.Department, e.Salary
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(Salary)
    FROM Employees
    WHERE Department = e.Department
);

Here, the subquery (SELECT AVG(Salary) FROM Employees WHERE Department = e.Department) depends on each row from the outer query (e.Department). So for every employee, it calculates the department’s average salary and compares it with that employee’s salary.

I used this concept in a performance benchmarking query where we needed to list products performing above their category’s average sales. The correlated subquery helped compare each product’s sales against its category dynamically.

One challenge with correlated subqueries is performance — since the inner query runs repeatedly for each row, it can be slow on large datasets. To optimize, I sometimes rewrite it using a JOIN or a CTE. For example, the same query can be written as:

🗃️
filename.sql 
SELECT e.EmployeeName, e.Department, e.Salary
FROM Employees e
JOIN (
    SELECT Department, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY Department
) d ON e.Department = d.Department
WHERE e.Salary > d.AvgSalary;

This approach precomputes the averages once, making it more efficient.

So, in simple terms — a correlated subquery is a subquery that uses values from the outer query, executing once per row, and it’s useful for comparisons that depend on each record’s context.

Scroll to Top