Correlated Subquery
Learn how a correlated subquery works with the outer query and is evaluated for each row.
What is a Correlated Subquery?
A Correlated Subquery is a subquery that depends on a value from the outer query.
Unlike a normal subquery, a correlated subquery cannot work independently because it references a column from the outer query.
The subquery is evaluated for each row processed by the outer query.
Basic Structure
SELECT column_name
FROM table_name AS outer_table
WHERE condition
(
SELECT column_name
FROM table_name AS inner_table
WHERE inner_table.column = outer_table.column
);
Notice that the inner query refers to a column from the outer query. This dependency makes the subquery correlated.
Example Table
Consider the following Employees table:
| EmployeeId | EmployeeName | DepartmentId | Salary |
|---|---|---|---|
| 1 | Rahul | 101 | 30000 |
| 2 | Priya | 101 | 45000 |
| 3 | Amit | 101 | 50000 |
| 4 | Sneha | 102 | 40000 |
| 5 | Neha | 102 | 55000 |
Example: Employees Above Department Average
Suppose we want to find employees whose salary is greater than the average salary of their own department.
SELECT
e.EmployeeName,
e.DepartmentId,
e.Salary
FROM Employees e
WHERE e.Salary >
(
SELECT AVG(e2.Salary)
FROM Employees e2
WHERE e2.DepartmentId = e.DepartmentId
);
How Does a Correlated Subquery Work?
- The outer query selects an employee.
- The inner query finds the average salary for that employee's department.
-
The inner query uses
e.DepartmentIdfrom the outer query. - The employee's salary is compared with the calculated department average.
- The process is repeated for the other employees.
Normal Subquery vs Correlated Subquery
| Normal Subquery | Correlated Subquery |
|---|---|
| Can generally execute independently. | Depends on the outer query. |
| Does not normally reference the outer query. | References a column from the outer query. |
| Generally evaluated independently of individual outer rows. | Evaluated in relation to rows of the outer query. |
Example: Highest Paid Employee in Each Department
The following correlated subquery finds employees whose salary is equal to the maximum salary in their department.
SELECT
e.EmployeeName,
e.DepartmentId,
e.Salary
FROM Employees e
WHERE e.Salary =
(
SELECT MAX(e2.Salary)
FROM Employees e2
WHERE e2.DepartmentId = e.DepartmentId
);
The inner query calculates the maximum salary for the department of the current employee.
Important Points
- A correlated subquery depends on the outer query.
- The inner query references a column from the outer query.
- Table aliases make correlated subqueries easier to read.
- Correlated subqueries are useful for row-by-row comparisons.
- They can be used with aggregate functions such as AVG(), MAX() and MIN().
- For large datasets, query performance should be considered when using correlated subqueries.
Summary
A correlated subquery is a subquery that depends on the outer query. It references a column from the outer query and is evaluated in relation to the rows being processed. It is especially useful for comparing each row with aggregated or related data from the same group.