Lesson 10: Subqueries

We learned how to connect tables with JOINs. Now we learn about subqueries -- queries nested inside other queries. You can use the result of one query as a condition in another, like finding "products more expensive than the average price".

A subquery is a SELECT statement nested inside another query. It can be used in WHERE, FROM, or SELECT clauses. Subqueries let you break down complex questions into small, readable steps.

The inner query runs first, and its result is passed to the outer query.

Scalar Subqueries in WHERE

A scalar subquery returns a single value (1 row, 1 column). It can be used anywhere a literal value would go.

-- Products more expensive than overall average price
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products WHERE is_active = 1)
  AND is_active = 1
ORDER BY price ASC;

Result:

The inner query (SELECT AVG(price) FROM products WHERE is_active = 1) calculates the average once, then the outer query compares each product price against that value.

-- Customers who signed up before the first order
SELECT name, created_at
FROM customers
WHERE created_at < (SELECT MIN(ordered_at) FROM orders)
LIMIT 5;

IN Subqueries

When a subquery can return multiple rows, use IN instead of =.

-- Customers who have left a 1-star review
SELECT name, email, grade
FROM customers
WHERE id IN (
    SELECT DISTINCT customer_id
    FROM reviews
    WHERE rating = 1
)
ORDER BY name;

Result:

-- Active products currently in shopping carts
SELECT name, price, stock_qty
FROM products
WHERE id IN (
    SELECT DISTINCT product_id
    FROM cart_items
)
  AND is_active = 1
ORDER BY name;

NOT IN

NOT IN finds rows that are not in the subquery result -- similar to the LEFT JOIN ... IS NULL anti-join pattern.

-- Products that have never been ordered
SELECT name, price
FROM products
WHERE id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
)
  AND is_active = 1;

Warning: If the subquery returns even one NULL value, NOT IN behaves unexpectedly (returning no rows at all). When NULLs may be present, use NOT EXISTS (Lesson 20) instead.

FROM Subqueries (Derived Tables)

A subquery in the FROM clause creates a temporary inline table. This is called a derived table or inline view.

-- Average order amount by customer grade
SELECT
    grade,
    ROUND(AVG(avg_order), 2) AS avg_order_value
FROM (
    SELECT
        c.grade,
        o.customer_id,
        AVG(o.total_amount) AS avg_order
    FROM orders AS o
    INNER JOIN customers AS c ON o.customer_id = c.id
    WHERE o.status NOT IN ('cancelled', 'returned')
    GROUP BY c.grade, o.customer_id
) AS customer_avgs
GROUP BY grade
ORDER BY avg_order_value DESC;

Result:

-- Top 3 months by revenue with order count
SELECT
    monthly.year_month,
    monthly.revenue,
    monthly.order_count
FROM (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS year_month,
        SUM(total_amount)        AS revenue,
        COUNT(*)                 AS order_count
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned')
    GROUP BY SUBSTR(ordered_at, 1, 7)
) AS monthly
ORDER BY revenue DESC
LIMIT 3;

SELECT
    monthly.year_month,
    monthly.revenue,
    monthly.order_count
FROM (
    SELECT
        DATE_FORMAT(ordered_at, '%Y-%m') AS year_month,
        SUM(total_amount)                AS revenue,
        COUNT(*)                         AS order_count
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned')
    GROUP BY DATE_FORMAT(ordered_at, '%Y-%m')
) AS monthly
ORDER BY revenue DESC
LIMIT 3;

SELECT
    monthly.year_month,
    monthly.revenue,
    monthly.order_count
FROM (
    SELECT
        TO_CHAR(ordered_at, 'YYYY-MM') AS year_month,
        SUM(total_amount)              AS revenue,
        COUNT(*)                       AS order_count
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned')
    GROUP BY TO_CHAR(ordered_at, 'YYYY-MM')
) AS monthly
ORDER BY revenue DESC
LIMIT 3;

Result:

Scalar Subqueries in SELECT

A subquery in the SELECT list executes once for each output row.

-- Most recent order date for each customer
SELECT
    c.name,
    c.grade,
    (
        SELECT MAX(ordered_at)
        FROM orders
        WHERE customer_id = c.id
    ) AS last_order_date
FROM customers AS c
WHERE c.is_active = 1
ORDER BY last_order_date DESC
LIMIT 8;

Result:

Scalar subqueries in SELECT execute per row, so they can be slow on large tables. When performance matters, use LEFT JOIN with aggregation instead.

Summary

Practice Problems

Problem 1

Find orders that have never had a completed payment. Use a NOT IN subquery to exclude order_ids from the payments table where status = 'completed'. Return order_number, total_amount, status, sorted by total_amount descending, limited to 10 rows.

SELECT order_number, total_amount, status
FROM orders
WHERE id NOT IN (
    SELECT order_id
    FROM payments
    WHERE status = 'completed'
)
ORDER BY total_amount DESC
LIMIT 10;

Problem 2

Query orders with amounts greater than the overall average order amount. Return order_number, total_amount, sorted by total_amount descending, limited to 10 rows. Use a scalar subquery in the WHERE clause.

SELECT order_number, total_amount
FROM orders
WHERE total_amount > (
    SELECT AVG(total_amount) FROM orders
)
ORDER BY total_amount DESC
LIMIT 10;

Problem 3

Use a scalar subquery in the SELECT clause to find each product's name and its review count. Return product_name, price, review_count, sorted by review_count descending, limited to 10 rows. Only include active products.

SELECT
    p.name  AS product_name,
    p.price,
    (
        SELECT COUNT(*)
        FROM reviews AS r
        WHERE r.product_id = p.id
    ) AS review_count
FROM products AS p
WHERE p.is_active = 1
ORDER BY review_count DESC
LIMIT 10;

Problem 4

Find all products priced above the average price within their own category. Use a correlated scalar subquery in the WHERE clause that references the outer query's category_id. Return product_name, price, category_id.

SELECT
    p.name        AS product_name,
    p.price,
    p.category_id
FROM products AS p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM products AS p2
    WHERE p2.category_id = p.category_id
      AND p2.is_active = 1
)
  AND p.is_active = 1
ORDER BY p.category_id, p.price DESC;

Problem 5

Find products that are in at least one customer's wishlist but have never been ordered. Use IN and NOT IN subqueries and return product_name and price.

SELECT name AS product_name, price
FROM products
WHERE id IN (
    SELECT DISTINCT product_id FROM wishlists
)
  AND id NOT IN (
    SELECT DISTINCT product_id FROM order_items
)
ORDER BY price DESC;

Problem 6

Use a FROM subquery to first calculate the average product price per category, then join with the categories table in the outer query to return category_name and avg_price. Sort by avg_price descending.

SELECT
    cat.name       AS category_name,
    price_stats.avg_price
FROM (
    SELECT
        category_id,
        ROUND(AVG(price), 2) AS avg_price
    FROM products
    WHERE is_active = 1
    GROUP BY category_id
) AS price_stats
INNER JOIN categories AS cat ON price_stats.category_id = cat.id
ORDER BY price_stats.avg_price DESC;

Problem 7

VIP' grade customers. Use an IN subquery and return product_name and price. Sort by price descending.

SELECT p.name AS product_name, p.price
FROM products AS p
WHERE p.id IN (
    SELECT DISTINCT oi.product_id
    FROM order_items AS oi
    INNER JOIN orders AS o ON oi.order_id = o.id
    INNER JOIN customers AS c ON o.customer_id = c.id
    WHERE c.grade = 'VIP'
)
ORDER BY p.price DESC;

Problem 8

Use a FROM subquery to find the top 10 customers by number of completed orders. Count orders per customer in the inner query, then join with the customers table in the outer query to add name and grade.

SELECT
    c.name,
    c.grade,
    order_stats.order_count,
    order_stats.total_spent
FROM (
    SELECT
        customer_id,
        COUNT(*)            AS order_count,
        SUM(total_amount)   AS total_spent
    FROM orders
    WHERE status IN ('delivered', 'confirmed')
    GROUP BY customer_id
) AS order_stats
INNER JOIN customers AS c ON order_stats.customer_id = c.id
ORDER BY order_stats.order_count DESC
LIMIT 10;

Problem 9

Find customers whose order count exceeds the overall average order count per customer. Use a FROM subquery to first get the order count per customer, then use a scalar subquery in the WHERE clause to compare against the average. Return customer_id and order_count, sorted by order_count descending, limited to 10 rows.

SELECT
    customer_id,
    order_count
FROM (
    SELECT
        customer_id,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS cust_orders
WHERE order_count > (
    SELECT AVG(cnt)
    FROM (
        SELECT COUNT(*) AS cnt
        FROM orders
        GROUP BY customer_id
    ) AS avg_calc
)
ORDER BY order_count DESC
LIMIT 10;

Problem 10

Find the names, emails, and last order dates of the 5 most recently ordering customers. Use a FROM subquery to first get the latest order date (last_order) per customer, then join with the customers table in the outer query. Sort by last_order descending.

SELECT
    c.name,
    c.email,
    recent.last_order
FROM (
    SELECT
        customer_id,
        MAX(ordered_at) AS last_order
    FROM orders
    GROUP BY customer_id
) AS recent
INNER JOIN customers AS c ON recent.customer_id = c.id
ORDER BY recent.last_order DESC
LIMIT 5;

Scoring Guide

Problem Areas:

Next: Lesson 11: Date/Time Functions