SQL Topics
Correlated Subquery
title: Correlated Subquery
In the previous lessons, you learned about simple subqueries and nested subqueries.
In those examples, the inner query could run independently of the outer query. SQL executed the subquery first, obtained a result, and then used that result in the outer query.
However, some business problems require the inner query to depend on the current row being processed by the outer query.
For example:
- Find employees earning more than the average salary of their own department.
- Find products priced higher than the average price in their category.
- Find students scoring above the average marks of their class.
- Find customers whose total spending is higher than the average spending in their city.
These problems cannot be solved using a simple independent subquery because the comparison changes for every row.
To solve this, SQL provides Correlated Subqueries.
A Correlated Subquery is a subquery that references columns from the outer query. Because of this dependency, the subquery executes once for each row processed by the outer query.
What is a Correlated Subquery?
A Correlated Subquery is a subquery that depends on values from the outer query.
Unlike a normal subquery:
Simple Subquery
ā
Executes Once
ā
Returns ResultA correlated subquery works like this:
Outer Row 1
ā
Run Subquery
Outer Row 2
ā
Run Subquery
Outer Row 3
ā
Run SubqueryThe subquery is executed repeatedly for each row.
Why is it Called "Correlated"?
The term correlated means:
Connected
Dependent
RelatedThe inner query depends on the outer query.
Example:
SELECT EmployeeName
FROM Employees E
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID = E.DepartmentID
);Notice:
E.DepartmentIDinside the subquery.
The inner query depends on the current row of the outer query.
This dependency creates the correlation.
How Correlated Subqueries Work
Consider:
SELECT EmployeeName
FROM Employees E
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID = E.DepartmentID
);Execution:
Row 1 ā Calculate Department Average
ā
Compare Salary
Row 2 ā Calculate Department Average
ā
Compare Salary
Row 3 ā Calculate Department Average
ā
Compare SalaryThe subquery runs separately for every employee.
Visual Representation
Outer Query Row
ā
ā¼
Correlated Subquery Executes
ā
ā¼
Returns Result
ā
ā¼
Row Evaluated
ā
ā¼
Next RowThis process repeats until all rows are processed.
Creating Sample Tables
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
DepartmentID INT,
Salary DECIMAL(10,2)
);Insert data:
INSERT INTO Employees VALUES
(1, 'Rahul', 1, 50000),
(2, 'Priya', 1, 70000),
(3, 'Amit', 2, 60000),
(4, 'Neha', 2, 90000);First Correlated Subquery Example
Find employees earning more than their department average.
Query:
SELECT EmployeeName,
Salary
FROM Employees E
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID = E.DepartmentID
);Step-by-Step Execution
Department 1:
| Employee | Salary |
|---|---|
| Rahul | 50000 |
| Priya | 70000 |
Average:
60000Result:
PriyaDepartment 2:
| Employee | Salary |
|---|---|
| Amit | 60000 |
| Neha | 90000 |
Average:
75000Result:
NehaFinal Output:
| EmployeeName |
|---|
| ------------- |
| Priya |
| Neha |
Why a Simple Subquery Cannot Solve This
Simple subquery:
SELECT EmployeeName
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
);This calculates:
One Average
For Entire CompanyBut our requirement is:
Different Average
For Each DepartmentOnly a correlated subquery can handle this.
Correlated Subquery with EXISTS
A very common pattern.
Example:
SELECT CustomerName
FROM Customers C
WHERE EXISTS
(
SELECT 1
FROM Orders O
WHERE O.CustomerID =
C.CustomerID
);Meaning:
Return Customers
Who Have OrdersThe subquery checks each customer individually.
Correlated Subquery with NOT EXISTS
Example:
SELECT CustomerName
FROM Customers C
WHERE NOT EXISTS
(
SELECT 1
FROM Orders O
WHERE O.CustomerID =
C.CustomerID
);Meaning:
Return Customers
Without OrdersReal-World Example: E-Commerce
Products Table:
| Product | Category | Price |
|---|---|---|
| Laptop | Electronics | 60000 |
| Phone | Electronics | 30000 |
| Chair | Furniture | 5000 |
| Sofa | Furniture | 20000 |
Requirement:
Products More Expensive
Than Their Category AverageQuery:
SELECT ProductName
FROM Products P
WHERE Price >
(
SELECT AVG(Price)
FROM Products
WHERE CategoryID =
P.CategoryID
);Real-World Example: Education System
Requirement:
Students Scoring Above
Their Class AverageQuery:
SELECT StudentName
FROM Students S
WHERE Marks >
(
SELECT AVG(Marks)
FROM Students
WHERE ClassID =
S.ClassID
);Real-World Example: Banking
Requirement:
Customers With Balance
Higher Than Branch AverageQuery:
SELECT CustomerName
FROM Accounts A
WHERE Balance >
(
SELECT AVG(Balance)
FROM Accounts
WHERE BranchID =
A.BranchID
);Correlated Subquery with UPDATE
Correlated subqueries can also be used in UPDATE statements.
Example:
UPDATE Employees E
SET Bonus = 5000
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID =
E.DepartmentID
);Employees above department average receive a bonus.
Correlated Subquery with DELETE
Example:
DELETE FROM Employees E
WHERE Salary <
(
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID =
E.DepartmentID
);Deletes employees earning below department average.
Correlated Subquery vs Simple Subquery
Simple Subquery:
Executes OnceExample:
SELECT *
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
);Correlated Subquery:
Executes For Every RowExample:
SELECT *
FROM Employees E
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID =
E.DepartmentID
);Comparison Table
| Feature | Simple Subquery | Correlated Subquery |
|---|---|---|
| Executes Once | Yes | No |
| Depends on Outer Query | No | Yes |
| Performance | Faster | Slower |
| Complexity | Lower | Higher |
| Business Logic | Simple | Advanced |
Performance Considerations
Correlated Subqueries can be expensive.
Why?
Subquery Executes
For Every RowExample:
1000 Rows
ā
1000 Subquery ExecutionsLarge tables may experience slower performance.
Improving Performance
Techniques include:
Indexes
JOINs
CTEs
Query OptimizationSometimes a JOIN can replace a correlated subquery and execute faster.
Common Errors
Forgetting Table Aliases
Wrong:
WHERE DepartmentID =
DepartmentIDAlways use aliases.
Correct:
WHERE DepartmentID =
E.DepartmentIDUsing Correlated Queries Unnecessarily
Simple subqueries are often faster.
Use correlated subqueries only when needed.
Poor Performance on Large Tables
Repeated execution can become expensive.
Confusing EXISTS and IN
Both are useful, but they work differently in certain scenarios.
Best Practices
Use Meaningful Aliases
Example:
Employees E
Orders O
Products PIndex Related Columns
Example:
DepartmentID
CustomerID
CategoryIDTest Performance
Large datasets may require optimization.
Consider JOIN Alternatives
Many correlated queries can be rewritten using joins.
Use EXISTS for Existence Checks
Often more efficient than alternative approaches.
Common Interview Questions
What is a Correlated Subquery?
A subquery that depends on values from the outer query.
How many times does a correlated subquery execute?
Once for every row processed by the outer query.
Why is it called correlated?
Because the inner query references columns from the outer query.
Is a correlated subquery slower than a normal subquery?
Usually yes, because it executes repeatedly.
Can a correlated subquery be replaced by a JOIN?
In many situations, yes.
Summary
Correlated Subqueries are advanced SQL queries where the inner query depends on values from the outer query. They are extremely useful for row-by-row comparisons, department-level calculations, category-based analysis, and complex business reporting.
In this lesson, you learned:
- What a Correlated Subquery is
- Why it is important
- How it works
- Row-by-row execution
- EXISTS and NOT EXISTS
- Real-world examples
- UPDATE and DELETE usage
- Performance considerations
- Common mistakes
- Best practices
Mastering Correlated Subqueries is essential for advanced SQL development because many business problems require dynamic comparisons that depend on each row being processed.
Next Step
Continue to the next lesson:
EXISTS Operator ā
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Correlated Subquery.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, subqueries, correlated
Related SQL Topics