17. Correlated Subqueries & EXISTS

Core Idea: A correlated subquery depends on values from the current candidate row of the outer query. It evaluates repeatedly for every row processed by the parent query.

The EXISTS Operator

EXISTS returns true as soon as the subquery finds at least one matching row, stopping further scanning immediately (short-circuit boolean check).

            SELECT c.CustomerID, c.CustomerName
            FROM Customers AS c
            WHERE EXISTS (
                SELECT 1 
                FROM Orders AS o 
                WHERE o.CustomerID = c.CustomerID 
                  AND o.OrderDate >= '2024-01-01'
            );
        

NOT EXISTS vs NOT IN

NOT EXISTS safely handles nulls, unlike NOT IN which can fail to return results if the subquery produces even a single NULL value.

            SELECT c.CustomerName
            FROM Customers AS c
            WHERE NOT EXISTS (
                SELECT 1 FROM Orders AS o WHERE o.CustomerID = c.CustomerID
            );
        

Correlated Comparison Queries

            SELECT p1.ProductName, p1.Category, p1.Price
            FROM Products AS p1
            WHERE p1.Price > (
                SELECT AVG(p2.Price) 
                FROM Products AS p2 
                WHERE p2.Category = p1.Category
            );
        

Advanced Deep Dive ADVANCED

SELECT 1 in EXISTS: Conventionally SELECT 1 or SELECT * is used in EXISTS because the optimizer only tests for the existence of rows without retrieving column data.

The NOT IN / NULL Trap: If any row returned by a subquery is NULL, column NOT IN (subquery) evaluates to UNKNOWN and yields zero rows! Always prefer NOT EXISTS for anti-semi-joins.

Semi-Join Transformation: The SQL Server Query Optimizer converts EXISTS queries into Semi-Join or Anti-Semi-Join plan operators for optimal performance.