SQL Server Interview Question #72

What is a correlated subquery?

Joins, Subqueries, CTEs & Set Operators Mid-Level Intermediate

Detailed Explanation

A correlated subquery references a column from the outer query. Logically, it is evaluated in relation to each outer row, although the SQL Server optimizer may transform the physical execution.

Correlated subqueries are commonly used with EXISTS and NOT EXISTS to test whether related rows are present.

Code Example

SELECT c.Id, c.Name
FROM dbo.Categories AS c
WHERE EXISTS
(
    SELECT 1
    FROM dbo.Products AS p
    WHERE p.CategoryId = c.Id
      AND p.IsActive = 1
);