16. Subqueries: Queries Inside Queries

Core Idea: A subquery (or inner query) is a nested SELECT statement embedded inside a parent statement such as SELECT, INSERT, UPDATE, or DELETE.

Scalar Subqueries (Single Value)

A scalar subquery returns exactly one column and one row. It can be placed wherever an expression or literal value is valid.

            SELECT ProductName, Price,
                   Price - (SELECT AVG(Price) FROM Products) AS DifferenceFromAverage
            FROM Products;
        

Multi-Row Subqueries (IN / ANY / ALL)

Multi-row subqueries return a list of values evaluated by operators like IN:

            SELECT CustomerName, Country
            FROM Customers
            WHERE CustomerID IN (
                SELECT DISTINCT CustomerID FROM Orders WHERE TotalAmount > 1000
            );
        

Derived Tables (Subquery in FROM)

Subqueries in the FROM clause behave as temporary inline views and require an alias:

            SELECT Summary.Category, Summary.AvgPrice
            FROM (
                SELECT Category, AVG(Price) AS AvgPrice
                FROM Products
                GROUP BY Category
            ) AS Summary
            WHERE Summary.AvgPrice > 50;
        

Advanced Deep Dive ADVANCED

Correlated vs Non-Correlated: Non-correlated subqueries execute once for the entire query execution. Correlated subqueries reference columns from the outer query and evaluate per candidate row.

Common Table Expressions (CTEs): In modern T-SQL, complex derived table subqueries are often refactored with WITH cte AS (...) to enhance modularity and readability.

Execution Cost: The SQL Server Query Optimizer attempts to unnest subqueries into relational joins when optimal.