SQL Notes

Hub Advantages and Disadvantages of SQL Features of SQL History of SQL SQL vs NoSQL What is SQL?SQL Comments SQL Data Types SQL Keywords SQL Operators SQL Syntax CHECK Constraint in SQL Composite Keys in SQL DEFAULT Constraint in SQL FOREIGN KEY Constraint in SQL NOT NULL Constraint in SQL PRIMARY KEY Constraint in SQL SQL Constraints UNIQUE Key Constraint in SQL DELETE Data INSERT Data SELECT Data SQL Aliases UPDATE Data BETWEEN Operator DISTINCT Keyword GROUP BY Clause HAVING Clause IN Operator LIKE Operator LIMIT Clause ORDER BY Clause TOP Clause WHERE Clause Wildcard Characters EXCEPT Operator in SQL INTERSECT Operator in SQL MINUS Operator in SQL UNION Operator in SQL UNION ALL Operator in SQL CROSS JOIN in SQL FULL OUTER JOIN INNER JOIN JOIN vs UNION LEFT JOIN RIGHT JOIN SELF JOIN SQL Joins Overview ANY and ALL Operators CASE Expression in SQL Correlated Subquery EXISTS Operator Nested Subqueries in SQL Scalar Subqueries Subquery Introduction Aggregate Functions AVG Function Conversion Functions COUNT Function Date Functions MIN and MAX Functions Numeric Functions String Functions SUM Function ALTER TABLE Create Table DROP TABLE Rename Table Temporary Table TRUNCATE TABLE Backup Database Create Database Drop Database Restore Database CREATE VIEW in SQL DROP VIEW in SQL Materialized Views in SQL Update and Modify Views in SQL Create Stored Procedure in SQL Execute Stored Procedures in SQL Parameters in Stored Procedures Stored Procedure vs Function in SQL Stored Procedure Introduction AFTER Trigger BEFORE Trigger INSTEAD OF Trigger Trigger Examples Trigger Introduction ACID Properties in SQL COMMIT in SQL Isolation Levels in SQL ROLLBACK in SQL SAVEPOINT in SQL Introduction to SQL Transactions Boyce-Codd Normal Form (BCNF)Database Normalization Denormalization First Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)Clustered Index in SQL Index Performance Non-Clustered Index Introduction to SQL Indexes Unique Index SQL Execution Plans EXPLAIN Plan Indexing Strategies Partitioning Query Optimization SQL Performance Tips Common Table Expressions (CTE)Dynamic SQL Functions in SQL Materialized Views MERGE Statement PIVOT and UNPIVOT RANK(), DENSE_RANK(), and ROW_NUMBER()Recursive CTE Stored Procedures Triggers in SQL Views in SQL Window Functions SQL Challenges SQL Exercises (Advanced)SQL Exercises (Beginner)SQL Exercises (Intermediate)SQL Projects SQL Interview Questions (Advanced)SQL Interview Questions (Beginner)SQL Interview Questions (Intermediate)SQL Output-Based Interview Questions SQL Scenario-Based Interview Questions Create Your First Database Install MySQL on Your Computer Install PostgreSQL Install SQLite SQL Online Compilers SQL Roadmap

Correlated Subquery

Learn SQL Correlated Subqueries in detail, understand row-by-row execution, how they differ from normal subqueries, and solve advanced business problems efficiently.

We covered simple subqueries and nested subqueries before. In those, the inner query ran independently—it didn't care about the outer query.

But real-world problems often need something different. You need the inner query to reference the current row being processed.

For example:

Find employees earning more than their department's average salary.
Find products priced higher than the average in their category.
Find students scoring above their class average.
Find customers spending more than their city's average.

With a regular subquery, you can't do this because the comparison changes for each row.

Enter Correlated Subqueries.

A correlated subquery is a subquery that references columns from the outer query. Because it depends on the outer query, it runs once for every row.

Why "Correlated"?

The term means connected, dependent, related.

The inner query depends on the outer query.

Example:

sql example

SELECT EmployeeName
FROM Employees E
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = E.DepartmentID
);

See E.DepartmentID inside the subquery? That's the correlation. The inner query reads from the outer query's current row.

How They Actually Work

Let's walk through this:

sql example

SELECT EmployeeName
FROM Employees E
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = E.DepartmentID
);

Execution flow:

text example

Row 1 → Calculate Department Average
↓
Compare Salary

Row 2 → Calculate Department Average
↓
Compare Salary

Row 3 → Calculate Department Average
↓
Compare Salary

For each employee, the subquery recalculates the average for that employee's department.

Visual Flow

text example

Outer Query Row
      │
      ↓
Correlated Subquery Executes
      │
      ↓
Returns Result
      │
      ↓
Row Evaluated
      │
      ↓
Next Row

This repeats until all rows are checked.

Setting Up Sample Tables

sql example

CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10,2)
);

Add some data:

sql example

INSERT INTO Employees VALUES
(1, 'Rahul', 1, 50000),
(2, 'Priya', 1, 70000),
(3, 'Amit', 2, 60000),
(4, 'Neha', 2, 90000);

Your First Correlated Subquery

Goal: Find employees earning more than their department average.

Query:

sql example

SELECT EmployeeName,
       Salary
FROM Employees E
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = E.DepartmentID
);

Step-by-Step Breakdown

Department 1:

Employee	Salary
Rahul	50000
Priya	70000

Average:

Output

Who's above average?

text example

Priya

Department 2:

Employee	Salary
Amit	60000
Neha	90000

Average:

text example

75000

Who's above?

text example

Neha

Final result:

EmployeeName
--------------
Priya
Neha

Why a Simple Subquery Won't Work

Simple subquery approach:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

This calculates:

Output

One Average
For Entire Company

But you need:

text example

Different Average
For Each Department

Only a correlated subquery solves this.

Correlated Subquery with EXISTS

Very common pattern. Check for existence:

sql example

SELECT CustomerName
FROM Customers C
WHERE EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerID =
          C.CustomerID
);

Translation:

Output

Return Customers
Who Have Orders

Each customer is checked individually.

Correlated Subquery with NOT EXISTS

Flip the logic:

sql example

SELECT CustomerName
FROM Customers C
WHERE NOT EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerID =
          C.CustomerID
);

Translation:

Output

Return Customers
Without Orders

Real-World: E-Commerce

Products:

Product	Category	Price
Laptop	Electronics	60000
Phone	Electronics	30000
Chair	Furniture	5000
Sofa	Furniture	20000

Want: Products more expensive than their category average.

Query:

sql example

SELECT ProductName
FROM Products P
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
    WHERE CategoryID =
          P.CategoryID
);

Real-World: Education

Want: Students scoring above their class average.

Query:

sql example

SELECT StudentName
FROM Students S
WHERE Marks >
(
    SELECT AVG(Marks)
    FROM Students
    WHERE ClassID =
          S.ClassID
);

Real-World: Banking

Want: Customers with balance higher than their branch average.

Query:

sql example

SELECT CustomerName
FROM Accounts A
WHERE Balance >
(
    SELECT AVG(Balance)
    FROM Accounts
    WHERE BranchID =
          A.BranchID
);

Correlated Subquery in UPDATE

You can update conditionally using correlation:

sql example

UPDATE Employees E
SET Bonus = 5000
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID =
          E.DepartmentID
);

Employees above their department average get a bonus.

Correlated Subquery in DELETE

Delete based on a condition:

sql example

DELETE FROM Employees E
WHERE Salary <
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID =
          E.DepartmentID
);

Removes employees below their department average.

Correlated vs Simple Subquery

Simple Subquery:

Output

Executes Once

Example:

sql example

SELECT *
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Correlated Subquery:

Output

Executes For Every Row

Example:

sql example

SELECT *
FROM Employees E
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = E.DepartmentID
);

Performance Consideration

Correlated subqueries can be slower for large datasets because they execute repeatedly. In some cases, you might get better performance with JOINs.

But they're excellent for readability and certain problems are just easier to solve this way.

Key Takeaways

✅ Correlated subqueries reference the outer query

✅ They execute once per outer row

✅ Perfect for department/category/group comparisons

✅ Works with EXISTS, UPDATE, DELETE

✅ May be slower than JOINs on huge datasets

✅ Highly readable for business logic

Use correlated subqueries when you need per-row context and comparisons. They're a powerful tool.

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 Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, subqueries, correlated, subquery

Continue learning this concept

SubqueriesSubquery IntroductionLearn SQL Subqueries in detail, understand how queries can be nested inside other queries, and discover real-world use cases for filtering, comparisons, and advanced data retrieval.SubqueriesANY and ALL OperatorsLearn SQL ANY and ALL Operators in detail, understand how they compare values against multiple rows returned by subqueries, and solve advanced filtering problems.SubqueriesCASE Expression in SQLLearn how to use the CASE expression in SQL for conditional logic within queries, understand simple and searched CASE syntax, and apply it in real-world scenarios.SubqueriesEXISTS OperatorLearn SQL EXISTS Operator in detail, understand how it checks for the existence of rows in a subquery, improves query efficiency, and is used in real-world database applications.SubqueriesNested Subqueries in SQLLearn how to write nested subqueries (subqueries within subqueries) in SQL, understand multiple levels of nesting, and solve complex data retrieval problems.SubqueriesScalar SubqueriesLearn SQL Scalar Subqueries in detail, understand how they return a single value, how they are used in SELECT, WHERE, HAVING, and ORDER BY clauses, and solve advanced SQL problems efficiently.

PreviousCASE Expression in SQL NextEXISTS Operator

SQL Notes

Correlated Subquery

Learn SQL Correlated Subqueries in detail, understand row-by-row execution, how they differ from normal subqueries, and solve advanced business problems efficiently.

We covered simple subqueries and nested subqueries before. In those, the inner query ran independently—it didn't care about the outer query.

But real-world problems often need something different. You need the inner query to reference the current row being processed.

For example:

Find employees earning more than their department's average salary.
Find products priced higher than the average in their category.
Find students scoring above their class average.
Find customers spending more than their city's average.

With a regular subquery, you can't do this because the comparison changes for each row.

Enter Correlated Subqueries.

A correlated subquery is a subquery that references columns from the outer query. Because it depends on the outer query, it runs once for every row.

Why "Correlated"?

The term means connected, dependent, related.

The inner query depends on the outer query.

Example:

sql example

SELECT EmployeeName
FROM Employees E
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = E.DepartmentID
);

See E.DepartmentID inside the subquery? That's the correlation. The inner query reads from the outer query's current row.

How They Actually Work

Let's walk through this:

sql example

SELECT EmployeeName
FROM Employees E
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = E.DepartmentID
);

Execution flow:

text example

Row 1 → Calculate Department Average
↓
Compare Salary

Row 2 → Calculate Department Average
↓
Compare Salary

Row 3 → Calculate Department Average
↓
Compare Salary

For each employee, the subquery recalculates the average for that employee's department.

Visual Flow

text example

Outer Query Row
      │
      ↓
Correlated Subquery Executes
      │
      ↓
Returns Result
      │
      ↓
Row Evaluated
      │
      ↓
Next Row

This repeats until all rows are checked.

Setting Up Sample Tables

sql example

CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10,2)
);

Add some data:

sql example

INSERT INTO Employees VALUES
(1, 'Rahul', 1, 50000),
(2, 'Priya', 1, 70000),
(3, 'Amit', 2, 60000),
(4, 'Neha', 2, 90000);

Your First Correlated Subquery

Goal: Find employees earning more than their department average.

Query:

sql example

SELECT EmployeeName,
       Salary
FROM Employees E
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = E.DepartmentID
);

Step-by-Step Breakdown

Department 1:

Employee	Salary
Rahul	50000
Priya	70000

Average:

Output

Who's above average?

text example

Priya

Department 2:

Employee	Salary
Amit	60000
Neha	90000

Average:

text example

75000

Who's above?

text example

Neha

Final result:

EmployeeName
--------------
Priya
Neha

Why a Simple Subquery Won't Work

Simple subquery approach:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

This calculates:

Output

One Average
For Entire Company

But you need:

text example

Different Average
For Each Department

Only a correlated subquery solves this.

Correlated Subquery with EXISTS

Very common pattern. Check for existence:

sql example

SELECT CustomerName
FROM Customers C
WHERE EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerID =
          C.CustomerID
);

Translation:

Output

Return Customers
Who Have Orders

Each customer is checked individually.

Correlated Subquery with NOT EXISTS

Flip the logic:

sql example

SELECT CustomerName
FROM Customers C
WHERE NOT EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerID =
          C.CustomerID
);

Translation:

Output

Return Customers
Without Orders

Real-World: E-Commerce

Products:

Product	Category	Price
Laptop	Electronics	60000
Phone	Electronics	30000
Chair	Furniture	5000
Sofa	Furniture	20000

Want: Products more expensive than their category average.

Query:

sql example

SELECT ProductName
FROM Products P
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
    WHERE CategoryID =
          P.CategoryID
);

Real-World: Education

Want: Students scoring above their class average.

Query:

sql example

SELECT StudentName
FROM Students S
WHERE Marks >
(
    SELECT AVG(Marks)
    FROM Students
    WHERE ClassID =
          S.ClassID
);

Real-World: Banking

Want: Customers with balance higher than their branch average.

Query:

sql example

SELECT CustomerName
FROM Accounts A
WHERE Balance >
(
    SELECT AVG(Balance)
    FROM Accounts
    WHERE BranchID =
          A.BranchID
);

Correlated Subquery in UPDATE

You can update conditionally using correlation:

sql example

UPDATE Employees E
SET Bonus = 5000
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID =
          E.DepartmentID
);

Employees above their department average get a bonus.

Correlated Subquery in DELETE

Delete based on a condition:

sql example

DELETE FROM Employees E
WHERE Salary <
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID =
          E.DepartmentID
);

Removes employees below their department average.

Correlated vs Simple Subquery

Simple Subquery:

Output

Executes Once

Example:

sql example

SELECT *
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Correlated Subquery:

Output

Executes For Every Row

Example:

sql example

SELECT *
FROM Employees E
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = E.DepartmentID
);

Performance Consideration

Correlated subqueries can be slower for large datasets because they execute repeatedly. In some cases, you might get better performance with JOINs.

But they're excellent for readability and certain problems are just easier to solve this way.

Key Takeaways

✅ Correlated subqueries reference the outer query

✅ They execute once per outer row

✅ Perfect for department/category/group comparisons

✅ Works with EXISTS, UPDATE, DELETE

✅ May be slower than JOINs on huge datasets

✅ Highly readable for business logic

Use correlated subqueries when you need per-row context and comparisons. They're a powerful tool.

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 Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, subqueries, correlated, subquery

Continue learning this concept

PreviousCASE Expression in SQL NextEXISTS Operator