Correlated Subqueries and EXISTS in SQL Server

A CORRELATED subquery uses a column from the OUTER query.

It is (logically) executed once for EACH row of the outer query.

SELECT ... FROM Employees e
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID)
                                                                       ^^^^^^^^^^^^^^
                                                                  outer column = correlation

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic table: ProductPriceHistory (price changes over time)

DROP TABLE IF EXISTS dbo.ProductPriceHistory;

CREATE TABLE dbo.ProductPriceHistory
(
    HistoryID     INT IDENTITY(1,1) PRIMARY KEY,
    ProductID     INT           NOT NULL REFERENCES dbo.Products(ProductID),
    Price         DECIMAL(10,2) NOT NULL,
    EffectiveFrom DATE          NOT NULL
);

INSERT INTO dbo.ProductPriceHistory (ProductID, Price, EffectiveFrom) VALUES
(1, 60000, '2024-01-01'), (1, 62000, '2024-07-01'), (1, 65000, '2025-01-01'),
(2, 80000, '2024-01-01'), (2, 85000, '2024-10-01'),
(3, 115000,'2024-01-01'), (3, 118000,'2024-06-01'), (3, 120000,'2025-03-01'),
(6, 27000, '2024-01-01'), (6, 25000, '2024-11-15'),
(7, 72000, '2024-01-01'), (7, 70000, '2025-02-01'),
(9, 1000,  '2024-01-01'), (9, 1200,  '2024-08-01'),
(13, 11000,'2024-01-01'), (13, 12000,'2025-01-15');
GO

(16 rows affected)

Part A: Correlated Subquery in WHERE

Employees earning more than the average of Their Own department

SELECT e.FirstName, e.DepartmentID, e.Salary
FROM dbo.Employees AS e
WHERE e.Salary > (SELECT AVG(e2.Salary)
                  FROM dbo.Employees AS e2
                  WHERE e2.DepartmentID = e.DepartmentID);

Highest paid employee in each department

SELECT e.DepartmentID, e.FirstName, e.Salary
FROM dbo.Employees AS e
WHERE e.Salary = (SELECT MAX(e2.Salary)
                  FROM dbo.Employees AS e2
                  WHERE e2.DepartmentID = e.DepartmentID)
ORDER BY e.DepartmentID;

Most expensive product in each category

SELECT p.CategoryID, p.ProductName, p.UnitPrice
FROM dbo.Products AS p
WHERE p.UnitPrice = (SELECT MAX(p2.UnitPrice) FROM dbo.Products p2 WHERE p2.CategoryID = p.CategoryID);

Each customer's Most Recent order

SELECT o.CustomerID, o.OrderID, o.OrderDate
FROM dbo.Orders AS o
WHERE o.OrderDate = (SELECT MAX(o2.OrderDate) FROM dbo.Orders o2 WHERE o2.CustomerID = o.CustomerID)
ORDER BY o.CustomerID;

Students who scored above their Class average in Maths

SELECT s.StudentName, s.ClassName, es.Marks
FROM dbo.Students AS s
JOIN dbo.ExamScores AS es ON es.StudentID = s.StudentID AND es.Subject = 'Maths'
WHERE es.Marks > (SELECT AVG(es2.Marks * 1.0)
                  FROM dbo.ExamScores es2
                  JOIN dbo.Students s2 ON s2.StudentID = es2.StudentID
                  WHERE s2.ClassName = s.ClassName AND es2.Subject = 'Maths');

Nth highest salary with a correlated subquery (N = 3)

"Count how many DISTINCT salaries are bigger than mine. If 2, I am 3rd."

SELECT DISTINCT e.Salary AS ThirdHighestSalary
FROM dbo.Employees AS e
WHERE 2 = (SELECT COUNT(DISTINCT e2.Salary) FROM dbo.Employees e2 WHERE e2.Salary > e.Salary);

Part B: EXISTS / NOT EXISTS (always correlated)

Employees who are managers (someone reports to them)

SELECT m.EmployeeID, m.FirstName, m.JobTitle
FROM dbo.Employees AS m
WHERE EXISTS (SELECT 1 FROM dbo.Employees e WHERE e.ManagerID = m.EmployeeID);

Customers who ordered a Laptop (CategoryID = 3)

SELECT c.CustomerName
FROM dbo.Customers AS c
WHERE EXISTS (SELECT 1
              FROM dbo.Orders o
              JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
              JOIN dbo.Products p    ON p.ProductID = oi.ProductID
              WHERE o.CustomerID = c.CustomerID AND p.CategoryID = 3);

Sales people who have No order in 2025

SELECT e.FirstName
FROM dbo.Employees AS e
WHERE e.DepartmentID = 2
  AND NOT EXISTS (SELECT 1 FROM dbo.Orders o
                  WHERE o.EmployeeID = e.EmployeeID AND o.OrderDate >= '2025-01-01');

Orders where Every item had a discount (relational division style)

SELECT o.OrderID
FROM dbo.Orders AS o
WHERE NOT EXISTS (SELECT 1 FROM dbo.OrderItems oi WHERE oi.OrderID = o.OrderID AND oi.Discount = 0)
  AND EXISTS     (SELECT 1 FROM dbo.OrderItems oi WHERE oi.OrderID = o.OrderID);

Part C: Correlated Subquery in SELECT

Number of orders and last order date per customer

SELECT c.CustomerName,
       (SELECT COUNT(*)         FROM dbo.Orders o WHERE o.CustomerID = c.CustomerID) AS TotalOrders,
       (SELECT MAX(o.OrderDate) FROM dbo.Orders o WHERE o.CustomerID = c.CustomerID) AS LastOrderDate
FROM dbo.Customers AS c;

Number of direct reports for each employee

SELECT e.FirstName, e.JobTitle,
       (SELECT COUNT(*) FROM dbo.Employees r WHERE r.ManagerID = e.EmployeeID) AS DirectReports
FROM dbo.Employees AS e
ORDER BY DirectReports DESC;

"Point in time" lookup: what was the product price on the order date?

SELECT o.OrderID, o.OrderDate, oi.ProductID,
       (SELECT TOP (1) h.Price
        FROM dbo.ProductPriceHistory h
        WHERE h.ProductID = oi.ProductID AND h.EffectiveFrom <= o.OrderDate
        ORDER BY h.EffectiveFrom DESC) AS PriceOnOrderDate,
       oi.UnitPrice AS PriceCharged
FROM dbo.Orders o
JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
WHERE oi.ProductID IN (1, 3, 7)
ORDER BY oi.ProductID, o.OrderDate;

Part D: Correlated UPDATE and DELETE

DROP TABLE IF EXISTS dbo.CustomerSummary;
CREATE TABLE dbo.CustomerSummary
(
    CustomerID   INT PRIMARY KEY,
    TotalOrders  INT NULL,
    TotalSpent   DECIMAL(14,2) NULL
);
INSERT INTO dbo.CustomerSummary (CustomerID) SELECT CustomerID FROM dbo.Customers;

(20 rows affected)

Correlated UPDATE: fill summary columns

UPDATE cs
SET TotalOrders = (SELECT COUNT(*) FROM dbo.Orders o WHERE o.CustomerID = cs.CustomerID),
    TotalSpent  = (SELECT ISNULL(SUM(oi.LineTotal), 0)
                   FROM dbo.Orders o JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
                   WHERE o.CustomerID = cs.CustomerID)
FROM dbo.CustomerSummary AS cs;

SELECT * FROM dbo.CustomerSummary ORDER BY TotalSpent DESC;

(20 rows affected)

Correlated DELETE: remove summary rows of customers with no orders

DELETE cs
FROM dbo.CustomerSummary AS cs
WHERE NOT EXISTS (SELECT 1 FROM dbo.Orders o WHERE o.CustomerID = cs.CustomerID);

SELECT COUNT(*) AS RemainingRows FROM dbo.CustomerSummary;

(3 rows affected)

Performance Note

• Correlated subqueries in SELECT run per row -> can be slow on big tables.
• The optimizer often rewrites EXISTS into a semi join (fast).
• Alternatives: JOIN + GROUP BY, window functions (file 08), or OUTER APPLY (file 13).

PRACTICE QUESTIONS 1. Find products priced below the average price of their own category. 2. For each department show the newest employee (latest HireDate). 3. Find customers whose every order is 'Delivered'. 4. Show each order with the number of items in it, using a correlated subquery. 5. Find employees whose salary is the lowest in their city.