18. Subqueries: SELECT in WHERE

Core Idea: Subqueries can be used directly within the WHERE clause to dynamically filter records based on criteria computed from other tables or aggregations.

Syntax Shape with IN

            SELECT * 
            FROM Products 
            WHERE CategoryID IN (
                SELECT CategoryID FROM Categories WHERE Name = 'Books'
            );
        

Comparison with Aggregate Subquery

            SELECT ProductName, Price 
            FROM Products 
            WHERE Price > (
                SELECT AVG(Price) FROM Products
            );
        

Multiple Subquery Filters

            SELECT OrderID, CustomerID, TotalAmount
            FROM Orders
            WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = 'India')
              AND TotalAmount > (SELECT AVG(TotalAmount) FROM Orders);
        

Advanced Deep Dive ADVANCED

Correlated Subqueries: Subqueries referencing outer query columns run per candidate row; optimize these or refactor into joins when dealing with massive datasets.

IN vs EXISTS: EXISTS is typically faster when testing existence across large datasets because the database engine can stop evaluating once the first match is found.

Scalar Subqueries: A scalar subquery in WHERE must return exactly one value; returning multiple values causes runtime error 512 ("Subquery returned more than 1 value").

Query Plan Caching: Use parameterized subqueries so SQL Server can reuse compiled execution plans efficiently.