This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Write correlated queries

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Correlated queries are among the most important advanced T-SQL concepts tested on the DP-800: Developing AI-Enabled Database Solutions certification exam. They allow a query to compare data from one row with data from another table or from the same table by referencing values from the outer query. Correlated queries are commonly used for row-by-row comparisons, filtering, existence checks, aggregate comparisons, and complex business logic.

Unlike standard subqueries, correlated queries are dependent on the outer query and are evaluated repeatedly—once for each row processed by the outer query. Although they can be more computationally expensive than non-correlated queries, they provide elegant solutions to many complex querying problems.

For the DP-800 exam, you should understand how correlated queries work, when to use them, how to optimize them, and how they compare to joins and window functions.

What Is a Correlated Query?

A correlated query (also called a correlated subquery) is a subquery that references one or more columns from the outer query.

Because of this dependency, the subquery cannot execute independently.

General syntax:

SELECT columns
FROM TableA A
WHERE expression
(
    SELECT ...
    FROM TableB B
    WHERE B.Column = A.Column
);

The subquery references A.Column, which belongs to the outer query.

How Correlated Queries Work

Execution occurs in this order:

1. SQL Server reads one row from the outer query.
2. The correlated subquery executes using values from that row.
3. SQL Server evaluates the result.
4. The process repeats for every row returned by the outer query.

Unlike regular subqueries, correlated queries are evaluated multiple times.

Correlated Query Example

Suppose two tables exist:

Customers

Orders

Retrieve customers who have placed at least one order.

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

The subquery references C.CustomerID, making it a correlated query.

Result:

Charlie is excluded because no matching order exists.

Comparing Correlated and Non-Correlated Queries

Non-Correlated Query

Runs once.

SELECT *
FROM Products
WHERE CategoryID IN
(
    SELECT CategoryID
    FROM Categories
);

The subquery is independent.

Correlated Query

Runs once for every outer row.

SELECT *
FROM Products P
WHERE EXISTS
(
    SELECT *
    FROM Inventory I
    WHERE I.ProductID=P.ProductID
);

The subquery depends on P.ProductID.

EXISTS with Correlated Queries

EXISTS is one of the most common operators used with correlated queries.

It returns TRUE when the subquery finds at least one row.

Example:

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

Benefits:

• Stops after finding the first matching row.
• Often performs better than IN for large datasets.
• Excellent for existence checks.

NOT EXISTS

Returns rows where no matching records exist.

Example:

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

Result:

Customers without orders.

Correlated Aggregate Query

Correlated queries frequently use aggregate functions.

Example:

Return employees earning above their department average.

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

For every employee, SQL Server calculates the average salary within that employee’s department.

Correlated MAX Example

Find employees with the highest salary in each department.

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

Correlated MIN Example

Find products with the lowest price within each category.

SELECT ProductName,
       Price
FROM Products P
WHERE Price =
(
    SELECT MIN(Price)
    FROM Products
    WHERE CategoryID=P.CategoryID
);

Correlated COUNT Example

Return customers who placed more than three orders.

SELECT CustomerName
FROM Customers C
WHERE
(
    SELECT COUNT(*)
    FROM Orders O
    WHERE O.CustomerID=C.CustomerID
) > 3;

Correlated SUM Example

Find salespeople whose total sales exceed $100,000.

SELECT SalesPersonName
FROM SalesPeople S
WHERE
(
    SELECT SUM(TotalAmount)
    FROM Orders O
    WHERE O.SalesPersonID=S.SalesPersonID
) > 100000;

Correlated UPDATE

Correlated queries are not limited to SELECT statements.

Example:

UPDATE Products
SET AveragePrice =
(
    SELECT AVG(UnitPrice)
    FROM Sales
    WHERE Sales.ProductID=Products.ProductID
);

Each product receives its own calculated average.

Correlated DELETE

Example:

Delete customers with no orders.

DELETE
FROM Customers
WHERE NOT EXISTS
(
    SELECT *
    FROM Orders
    WHERE Orders.CustomerID=Customers.CustomerID
);

Correlated INSERT

Correlated logic can also appear during INSERT operations.

Example:

INSERT INTO VIPCustomers
SELECT *
FROM Customers C
WHERE
(
    SELECT SUM(TotalAmount)
    FROM Orders O
    WHERE O.CustomerID=C.CustomerID
) > 50000;

Using EXISTS vs IN

Both operators may return similar results.

EXISTS

• Stops after first match.
• Efficient on large datasets.
• Ideal for correlated queries.

Example:

WHERE EXISTS
(
    SELECT *
    FROM Orders O
    WHERE O.CustomerID=C.CustomerID
)

IN

Works well for smaller lookup lists.

Example:

WHERE CustomerID IN
(
    SELECT CustomerID
    FROM Orders
)

Correlated Queries vs Joins

Many correlated queries can be rewritten as joins.

Correlated query:

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

Equivalent join:

SELECT DISTINCT
    C.CustomerName
FROM Customers C
INNER JOIN Orders O
ON C.CustomerID=O.CustomerID;

Both produce similar results, but performance depends on indexes, data volume, and execution plans.

Correlated Queries vs Window Functions

Sometimes a window function is a better solution.

Correlated query:

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

Window function:

SELECT EmployeeName,
       Salary
FROM
(
    SELECT *,
           AVG(Salary)
           OVER(PARTITION BY DepartmentID) AS AvgSalary
    FROM Employees
) E
WHERE Salary > AvgSalary;

Window functions often perform better because the aggregate is calculated once per partition instead of once per row.

Performance Considerations

Correlated queries can become expensive because the inner query executes repeatedly.

Performance depends on:

• Number of rows
• Index availability
• Query complexity
• Join selectivity
• Execution plan

SQL Server’s optimizer may transform some correlated queries into more efficient execution plans automatically.

Optimizing Correlated Queries

Best practices include:

• Create indexes on correlated columns.
• Use EXISTS instead of COUNT(*) > 0 when checking for existence.
• Avoid unnecessary correlated calculations.
• Review execution plans for repeated scans.
• Replace correlated aggregates with window functions when appropriate.
• Rewrite some queries as joins if performance improves.
• Filter outer rows before executing the correlated subquery.
• Avoid scalar user-defined functions inside correlated subqueries.

Common Business Scenarios

Correlated queries are commonly used for:

• Customers with orders
• Employees earning above department averages
• Highest-priced products in each category
• Duplicate detection
• Missing related records
• Parent-child relationships
• Inventory validation
• Sales performance analysis
• Financial reporting
• Data quality checks

Common Exam Tips

For the DP-800 exam, remember the following:

• A correlated query references columns from the outer query.
• The correlated subquery executes once for each outer row.
• EXISTS and NOT EXISTS are common correlated query operators.
• Correlated queries are frequently used with aggregate functions such as AVG, SUM, COUNT, MIN, and MAX.
• Correlated queries can appear in SELECT, UPDATE, DELETE, and INSERT statements.
• Some correlated queries can be rewritten as joins or window functions for better performance.
• Proper indexing significantly improves correlated query performance.

10 Practice Exam Questions

Question 1

What distinguishes a correlated subquery from a regular subquery?

A. It always returns multiple rows.

B. It references one or more columns from the outer query.

C. It can only be used with the EXISTS operator.

D. It cannot contain aggregate functions.

Answer: B

Explanation: A correlated subquery depends on values from the outer query by referencing its columns, causing it to execute in the context of each outer row.

Question 2

Which operator is most commonly used to determine whether related rows exist in a correlated query?

A. LIKE

B. BETWEEN

C. EXISTS

D. UNION

Answer: C

Explanation: EXISTS evaluates to TRUE when the correlated subquery returns at least one row and is optimized for existence checks.

Question 3

How many times is a correlated subquery typically evaluated?

A. Once for the entire query.

B. Once per database.

C. Once per table.

D. Once for each row processed by the outer query.

Answer: D

Explanation: Because the subquery references values from the current outer row, it is evaluated repeatedly as each outer row is processed.

Question 4

Which correlated query returns customers who have never placed an order?

A.

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

B.

SELECT *
FROM Customers
WHERE CustomerID IN
(
    SELECT CustomerID
    FROM Orders
);

C.

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

D.

SELECT *
FROM Customers
ORDER BY CustomerID;

Answer: C

Explanation: NOT EXISTS returns rows from the outer query for which the correlated subquery finds no matching records.

Question 5

Which aggregate function is commonly used in a correlated query to find employees earning more than the average salary in their department?

A. MAX()

B. MIN()

C. COUNT()

D. AVG()

Answer: D

Explanation: AVG() calculates the departmental average salary, allowing comparison against each employee’s salary.

Question 6

Which statement about correlated queries is true?

A. They cannot be used in UPDATE statements.

B. They cannot contain aggregate functions.

C. They can be used in SELECT, UPDATE, DELETE, and INSERT statements.

D. They always perform better than joins.

Answer: C

Explanation: Correlated subqueries are supported in multiple DML statements and are often used to calculate or validate row-specific values.

Question 7

When checking whether matching rows exist, why is EXISTS often preferred over COUNT(*) > 0?

A. EXISTS automatically creates indexes.

B. EXISTS stops searching after finding the first matching row.

C. EXISTS sorts the results automatically.

D. EXISTS returns all matching rows.

Answer: B

Explanation: EXISTS can stop processing as soon as a qualifying row is found, reducing unnecessary work.

Question 8

Which feature can often replace correlated aggregate queries while improving performance?

A. Temporary tables

B. Triggers

C. Foreign keys

D. Window functions

Answer: D

Explanation: Window functions calculate aggregates across partitions in a single pass, often making them more efficient than repeatedly executing correlated aggregate subqueries.

Question 9

Which factor most directly improves the performance of correlated queries?

A. Increasing the database compatibility level

B. Creating indexes on the correlated columns

C. Using larger transaction log files

D. Increasing the database recovery model

Answer: B

Explanation: Indexes on the columns used to correlate the outer and inner queries allow SQL Server to locate matching rows much more efficiently.

Question 10

Which business scenario is a good use case for a correlated query?

A. Displaying all rows from a single table without filtering

B. Sorting products alphabetically

C. Finding the highest-paid employee within each department

D. Creating a new database

Answer: C

Explanation: Correlated queries are well suited for row-by-row comparisons against aggregates or related data, such as identifying the highest-paid employee in each department.

Go to the DP-800 Exam Prep Hub main page