Subqueries

What is a subquery?

A subquery is a query nested inside another query. It is always wrapped in parentheses and runs first — the outer query then uses its result. The most common pattern is using a subquery in a WHERE clause to compare each row against a calculated value.

-- Find all orders priced above the overall average
SELECT *
FROM online_bookstore_orders
WHERE price_usd > (SELECT AVG(price_usd) FROM online_bookstore_orders);

The inner query runs first and returns a single number — the average price. The outer query then filters to only orders above that number.

Subqueries with AVG

A very common pattern is comparing each row's value against the table's average. Because the average can't be referenced directly in a WHERE clause, you calculate it in a subquery.

-- Gym visits longer than the overall average workout duration
SELECT visit_id, member_id, workout_duration_minutes
FROM gym_member_visits
WHERE workout_duration_minutes > (
    SELECT AVG(workout_duration_minutes)
    FROM gym_member_visits
);

Subqueries with MAX and MIN

MAX and MIN in subqueries let you find rows that match an extreme value — the most expensive item, the longest booking, the oldest record.

-- Find orders priced higher than the most expensive Mystery book
SELECT *
FROM online_bookstore_orders
WHERE price_usd > (
    SELECT MAX(price_usd)
    FROM online_bookstore_orders
    WHERE book_genre = 'Mystery'
);

-- Find all products below the cheapest Electronics price
SELECT *
FROM product_inventory
WHERE unit_cost < (
    SELECT MIN(unit_cost)
    FROM product_inventory
    WHERE category = 'Electronics'
);

Notice that the subquery can have its own WHERE clause — this lets you compare against the extreme value within a specific subset of data.

Subqueries filtering within a group

A subquery can target a specific group by including a WHERE clause of its own. This lets you compare rows against an aggregate computed for only a subset of the table — such as the average for a specific category, or the maximum within a particular restaurant.

-- Hotel bookings longer than the average Suite stay
SELECT *
FROM hotel_room_bookings
WHERE nights_stayed > (
    SELECT AVG(nights_stayed)
    FROM hotel_room_bookings
    WHERE room_type = 'Suite'
);

-- Delivery orders larger than Pizza Palace's average order
SELECT *
FROM food_delivery_orders
WHERE order_total_usd > (
    SELECT AVG(order_total_usd)
    FROM food_delivery_orders
    WHERE restaurant_name = 'Pizza Palace'
);

Common mistakes

Subquery returns more than one value — using = or > against a subquery only works when the subquery returns exactly one value. If it could return multiple rows, use IN instead of =.

Forgetting parentheses — the subquery must always be wrapped in parentheses. Without them the database will return a syntax error.

Referencing an alias from the outer query — the subquery runs independently and cannot reference column aliases defined in the outer SELECT list. It can only reference table and column names.

Using a subquery where a JOIN would be cleaner — subqueries that reference a different table are often better expressed as a JOIN. Subqueries work best when comparing against a single aggregated value from the same or a related table.