Chapter 5: Subqueries

A subquery is a query nested inside another SQL statement. The outer statement can be SELECT, INSERT, UPDATE, or DELETE. The inner query runs first, produces a result, and the outer query uses that result.

---

5.1 Subquery Architecture

Where a Subquery Can Appear

SELECT  (subquery)              ← computed column per row
FROM    (subquery) AS alias     ← derived table
WHERE   col > (subquery)        ← filter condition
WHERE   col IN (subquery)       ← list membership
WHERE   EXISTS (subquery)       ← existence check

Subquery Type Taxonomy

                      SUBQUERY
                          │
         ┌────────────────┼──────────────────┐
         │                │                  │
  Non-correlated     Correlated          Derived Table
  (runs ONCE)        (runs per row)      (in FROM)
         │
   ┌─────┴──────┐
   │            │
 Scalar      Multi-row
 (1 value)   (list)
                 │
          ┌──────┴──────┐
          │             │
         IN           EXISTS
      ANY / ALL

How SQL Evaluates a Subquery - Inside Out

Step 1:  Inner query runs FIRST
         ┌──────────────────────────────────┐
         │ SELECT customer_id               │
         │ FROM sales.customers             │
         │ WHERE city = 'New York'          │
         │ → returns: [1, 5, 8, 22]         │
         └──────────────────────────────────┘
                           │
                           ▼  result substituted in
Step 2:  Outer query runs with the result
         ┌──────────────────────────────────┐
         │ SELECT order_id FROM sales.orders│
         │ WHERE customer_id IN (1,5,8,22)  │
         └──────────────────────────────────┘
                           │
                           ▼
Step 3:  Final result returned to client

---

5.2 Subquery in WHERE Clause

The most common placement. The subquery filters the outer query's rows.

Scalar Subquery - Single Value Comparison

Returns exactly one value, used directly in a comparison.

-- Products priced above the overall average
SELECT
    product_name,
    list_price
FROM production.products
WHERE list_price > (
    SELECT AVG(list_price)
    FROM production.products
);

product_name              | list_price
----------------------------+-----------
Trek Fuel EX 8 29            | 2899.99
Heller Shagamaw Frame        | 1320.99
Trek Domane SLR 9 Disc        | 11999.99

The subquery computes AVG(list_price) = 1410.45 once. The outer query filters all rows against that one number.

⚠ Common Mistake - A scalar subquery must return exactly one row. If it returns multiple rows, SQL Server raises an error. Use MIN(), MAX(), or AVG() to guarantee a single value.

---

IN - List Membership

Returns a list of values. The outer query checks whether the column value is in that list.

-- Products in Mountain Bikes or Road Bikes categories
SELECT
    product_id,
    product_name
FROM production.products
WHERE category_id IN (
    SELECT category_id
    FROM production.categories
    WHERE category_name = 'Mountain Bikes'
       OR category_name = 'Road Bikes'
);

product_id | product_name
-----------+-----------------------------
6          | Trek 820 - 2016
23         | Trek Domane SLR 9 Disc

The inner query returns a list of category IDs that match those two names. The outer query then returns all products whose category_id is in that list.

---

NOT IN - Exclusion

-- Customers who have never placed an order
SELECT
    customer_id,
    first_name,
    last_name
FROM sales.customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM sales.orders
    WHERE customer_id IS NOT NULL
);

⚠ Common Mistake - NOT IN with NULLs If the subquery returns even one NULL, NOT IN returns zero rows silently. Always add WHERE column IS NOT NULL inside the subquery, or switch to NOT EXISTS.

---

5.3 Subquery in FROM Clause - Derived Table

A subquery in FROM acts as a temporary virtual table. The outer query reads from it like any real table. The subquery must have an alias.

-- Average number of orders per staff member
SELECT
    AVG(order_count) AS average_order_count_per_staff
FROM (
    SELECT
        staff_id,
        COUNT(order_id) AS order_count
    FROM sales.orders
    GROUP BY staff_id
) AS staff_order_counts;

average_order_count_per_staff
-----------------------------
322

How Derived Table Execution Works

Step 1:  Subquery in FROM executes first
         ┌────────────────────────────────────┐
         │ SELECT staff_id, COUNT(order_id)   │
         │ FROM sales.orders GROUP BY staff_id│
         │                                    │
         │  staff_id | order_count            │
         │  ---------|------------            │
         │     1     |   283                  │
         │     2     |   321                  │
         │     3     |   362                  │
         └────────────────────────────────────┘
                          │
                          ▼  becomes a virtual table
Step 2:  Outer query reads the virtual table
         ┌────────────────────────────────────┐
         │ SELECT AVG(order_count)            │
         │ FROM staff_order_counts            │
         └────────────────────────────────────┘

---

5.4 Subquery in SELECT Clause

A scalar subquery in SELECT computes one value per row of the outer query. It creates a computed column.

-- Each order with the maximum item price in that order
SELECT
    order_id,
    order_date,
    (
        SELECT MAX(list_price)
        FROM sales.order_items i
        WHERE i.order_id = o.order_id
    ) AS max_item_price
FROM sales.orders o
ORDER BY order_date DESC;

order_id | order_date  | max_item_price
---------+-------------+---------------
1615     | 2018-12-28  | 11999.99
1614     | 2018-12-27  | 3999.99
1613     | 2018-12-26  | 1049.99

This is a correlated subquery in the SELECT clause - o.order_id from the outer query is referenced inside. For each order row, the subquery finds the maximum price in that specific order.

---

5.5 Correlated Subquery

A correlated subquery references columns from the outer query. It cannot run independently - it depends on the outer query for its values. Because of this, it runs once per row that the outer query processes.

-- Products with the highest price in their category
SELECT
    product_name,
    list_price,
    category_id
FROM production.products p1
WHERE list_price IN (
    SELECT MAX(p2.list_price)
    FROM production.products p2
    WHERE p2.category_id = p1.category_id  -- ← references outer query
    GROUP BY p2.category_id
)
ORDER BY category_id, product_name;

product_name                    | list_price | category_id
---------------------------------+------------+-------------
Strider Classic 12 Balance Bike  | 89.99      | 1
Electra Amsterdam Original 3i... | 1049.99    | 2
Trek Domane SLR 9 Disc            | 11999.99   | 7

Correlated vs Non-Correlated Execution

NON-CORRELATED - inner query runs ONCE:
────────────────────────────────────────────────────────
  All 321 products processed
  Inner query AVG() runs: 1 time
  Total inner executions: 1
────────────────────────────────────────────────────────

CORRELATED - inner query runs ONCE PER ROW:
────────────────────────────────────────────────────────
  321 products in outer query
  For product 1 (category 6): inner MAX() runs → 5299.99
  For product 2 (category 6): inner MAX() runs → 5299.99
  For product 3 (category 1): inner MAX() runs → 489.99
  ...
  Total inner executions: 321
────────────────────────────────────────────────────────

On small tables this is invisible. On millions of rows, consider rewriting as a derived table + JOIN for performance.

---

5.6 EXISTS

EXISTS returns TRUE if the subquery returns any rows at all - it does not matter what those rows contain. It stops scanning as soon as one matching row is found.

-- Customers who placed at least one order in 2017
SELECT
    customer_id,
    first_name,
    last_name,
    city
FROM sales.customers c
WHERE EXISTS (
    SELECT 1
    FROM sales.orders o
    WHERE o.customer_id = c.customer_id
      AND YEAR(o.order_date) = 2017
);

customer_id | first_name | last_name | city
-------------+------------+-----------+-----------
1           | Debra      | Burks     | Orchard Park
2           | Kasha      | Todd      | Campbell

The SELECT 1 is a convention - EXISTS only checks whether a row exists, not what the row contains. Selecting a constant is the clearest way to communicate intent.

NOT EXISTS reverses it - returns rows where no matching rows exist in the subquery:

WHERE NOT EXISTS (
    SELECT 1 FROM sales.orders o
    WHERE o.customer_id = c.customer_id
      AND YEAR(o.order_date) = 2017
)

EXISTS Flow

For each row in the outer query:

  EXISTS check:
  ┌─────────────────────────────────┐
  │  subquery runs                  │
  │  first match found? → TRUE ──▶ row included
  │  no match found?   → FALSE ──▶ row excluded
  └─────────────────────────────────┘
  ↑ stops at first match - faster than IN on large subqueries

Interview Angle NOT EXISTS is the safer alternative to NOT IN when the subquery might return NULL values. NOT IN with a NULL in the list silently returns zero rows. NOT EXISTS is immune to this - it evaluates row existence, not value equality.

---

5.7 ANY

ANY compares a value against a set of values returned by a subquery. Returns TRUE if the comparison is satisfied by at least one value in the set.

-- syntax
scalar_expression comparison_operator ANY (subquery)

-- Products whose list price >= the average price of ANY brand
SELECT
    product_name,
    list_price
FROM production.products
WHERE list_price >= ANY (
    SELECT AVG(list_price)
    FROM production.products
    GROUP BY brand_id
);

product_name                    | list_price
---------------------------------+-----------
Trek 820 - 2016                   | 379.99
Surly Wednesday Frameset          | 999.99
Trek Domane SLR 9 Disc             | 11999.99

The subquery returns one average price per brand. ANY returns TRUE for a product if its price is greater than or equal to at least one of those brand averages - a low bar that includes many products.

ANY Logic Diagram

Subquery returns: [245, 890, 1410, 2250, 3600]  (avg per brand)

Product list_price = 379.99
  379.99 >= 245  → TRUE   ← stops here, row included
  (no need to check the rest)

Product list_price = 50.00
  50.00 >= 245   → FALSE
  50.00 >= 890   → FALSE
  50.00 >= 1410  → FALSE
  50.00 >= 2250  → FALSE
  50.00 >= 3600  → FALSE  → row excluded

---

5.8 ALL

ALL compares a value against every value returned by the subquery. Returns TRUE only if the comparison holds for every value in the set.

-- syntax
scalar_expression comparison_operator ALL (subquery)

-- Products whose list price >= ALL brand averages (the highest bar)
SELECT
    product_name,
    list_price
FROM production.products
WHERE list_price >= ALL (
    SELECT AVG(list_price)
    FROM production.products
    GROUP BY brand_id
);

product_name              | list_price
----------------------------+-----------
Trek Domane SLR 9 Disc       | 11999.99
Trek Powerfly 8 FS Plus       | 4999.99

Only products priced above every brand's average survive - a very high bar that returns very few rows.

ANY vs ALL Compared

Subquery returns: [245, 890, 1410, 2250, 3600]

  ANY  → price >= at least ONE value   → low bar, many rows
  ALL  → price >= EVERY value          → high bar, few rows

Product: 379.99
  ANY: 379.99 >= 245 → TRUE  → included
  ALL: 379.99 >= 3600 → FALSE → excluded

Operator	Passes when	Equivalent
= ANY	Equals at least one value	IN
<> ALL	Not equal to any value	NOT IN
> ANY	Greater than the minimum	> MIN(subquery)
> ALL	Greater than the maximum	> MAX(subquery)

---

5.9 CROSS APPLY

CROSS APPLY performs an inner join between a table and a correlated subquery (or table-valued function). It applies the subquery to each row of the outer table and returns only rows where the subquery produces results - rows with no match are excluded.

CROSS APPLY  ≈  INNER JOIN + correlated subquery
OUTER APPLY  ≈  LEFT JOIN  + correlated subquery

-- Top 2 most expensive products per category
SELECT
    c.category_name,
    p.product_name,
    p.list_price
FROM production.categories c
CROSS APPLY (
    SELECT TOP 2
        product_name,
        list_price
    FROM production.products
    WHERE category_id = c.category_id
    ORDER BY list_price DESC
) AS p
ORDER BY c.category_name, p.list_price DESC;

category_name       | product_name                                    | list_price
--------------------+-------------------------------------------------+-----------
Children Bicycles   | Electra Straight 8 3i (20-inch) - Boy's - 2017  | 489.99
Children Bicycles   | Electra Townie 3i EQ (20-inch) - Boys' - 2017   | 489.99
Mountain Bikes      | Trek Fuel EX 9.8 27.5 Plus - 2017               | 5299.99
Mountain Bikes      | Trek Remedy 9.8 - 2017                          | 5299.99
Road Bikes          | Trek Domane SLR 9 Disc - 2018                   | 11999.99
Road Bikes          | Trek Domane SLR 8 Disc - 2018                   | 7499.99

CROSS APPLY Execution Flow

For each row in production.categories:

  category_name = "Mountain Bikes"
      ↓
  APPLY runs correlated subquery:
      SELECT TOP 2 ... WHERE category_id = 6
      → returns: [Trek Fuel EX, Trek Remedy]
      ↓
  2 rows joined into result

  category_name = "Children Bicycles"
      ↓
  APPLY runs correlated subquery:
      SELECT TOP 2 ... WHERE category_id = 1
      → returns: [Electra Straight 8, Electra Townie]
      ↓
  2 rows joined into result

This is exactly what INNER JOIN cannot do here - JOIN cannot use the category row to limit which products it returns.

---

5.10 OUTER APPLY

OUTER APPLY performs a left join - it keeps every row from the left table even when the correlated subquery returns no rows. Empty results from the subquery appear as NULL.

-- All categories with their top 1 product (NULL if no products)
SELECT
    c.category_name,
    p.product_name,
    p.list_price
FROM production.categories c
OUTER APPLY (
    SELECT TOP 1
        product_name,
        list_price
    FROM production.products
    WHERE category_id = c.category_id
    ORDER BY list_price DESC
) AS p;

category_name        | product_name              | list_price
---------------------+---------------------------+-----------
Children Bicycles    | Electra Straight 8 3i...  | 489.99
Comfort Bicycles     | Electra Townie Go! 8i     | 2599.99
Mountain Bikes       | Trek Fuel EX 9.8 27.5...  | 5299.99
Clearance            | NULL                      | NULL

The Clearance category has no products - OUTER APPLY keeps the row and fills product columns with NULL. CROSS APPLY would have dropped that row entirely.

CROSS APPLY vs OUTER APPLY vs JOIN

         CROSS APPLY          OUTER APPLY
         ┌──────────┐         ┌──────────┐
  Left:  │ A B C D  │  Left:  │ A B C D  │
         └──────────┘         └──────────┘
  Subquery returns:            Subquery returns:
    A → rows                     A → rows ✓
    B → rows                     B → rows ✓
    C → no rows (dropped)        C → NULL  ✓  kept
    D → rows                     D → rows ✓

  ≈ INNER JOIN                ≈ LEFT JOIN

Tool	Behaviour	Use when
INNER JOIN	Match rows from two fixed tables	Simple table-to-table joins
CROSS APPLY	Inner join with correlated subquery	Top-N per group, complex per-row logic
OUTER APPLY	Left join with correlated subquery	Top-N per group, keep unmatched rows

---

5.11 Nesting Subqueries

Subqueries can be nested up to 32 levels deep. SQL Server evaluates from the innermost outward.

-- Products priced above the average for Strider and Trek brands
SELECT
    product_name,
    list_price
FROM production.products
WHERE list_price > (
    SELECT AVG(list_price)
    FROM production.products
    WHERE brand_id IN (
        SELECT brand_id
        FROM production.brands
        WHERE brand_name = 'Strider'
           OR brand_name = 'Trek'
    )
)
ORDER BY list_price;

Three-Level Execution

Level 3 (innermost):
  SELECT brand_id FROM production.brands
  WHERE brand_name IN ('Strider','Trek')
  → [6, 9]

Level 2:
  SELECT AVG(list_price) FROM production.products
  WHERE brand_id IN (6, 9)
  → 2285.50

Level 1 (outermost):
  SELECT product_name, list_price
  FROM production.products
  WHERE list_price > 2285.50

---

Summary

A subquery is a SELECT nested inside another SQL statement. It can appear in WHERE (scalar comparison, IN, EXISTS, ANY, ALL), FROM (derived table), or SELECT (computed column). Non-correlated subqueries run once; correlated subqueries run once per outer row - expensive on large tables. EXISTS checks row existence and stops at the first match - prefer it over NOT IN when NULLs may be present. ANY returns true if the comparison holds for at least one value; ALL requires it to hold for every value. CROSS APPLY performs an inner join between a table and a correlated subquery or table-valued function; OUTER APPLY performs a left join - rows with no matches from the subquery appear as NULL rather than being dropped.

---

Exercises

5.1 - Write a query using a scalar subquery that returns all products with a list_price above the average price in their brand. Use a correlated subquery in WHERE.

5.2 - Write a query using IN that returns all orders placed by customers living in New York or California.

5.3 - The following query is meant to find customers who never ordered, but has a NULL trap. Fix it:

SELECT customer_id FROM sales.customers
WHERE customer_id NOT IN (SELECT customer_id FROM sales.orders);

5.4 - Using a derived table in FROM, write a query that finds the average number of items per order across all orders.

5.5 - Rewrite the EXISTS example from section 8.6 using IN instead. Which version is safer and why?

5.6 - Use CROSS APPLY to return the top 3 most recent orders for each customer. Show customer_id, first_name, order_id, and order_date.

5.7 - Think About It: = ANY (subquery) is functionally identical to IN (subquery). Given that, when would you choose ANY over IN, and when would you choose ALL? What business question naturally maps to ALL that cannot be expressed cleanly with IN?