Subqueries

What is a subquery?

A subquery is a technique for embedding one SQL statement inside another, wrapped in parentheses. Think of it as: "first run the inner query, then use its result in the outer query."

A subquery in the WHERE clause

This is handy when you want to compare against an aggregated result. Things like "products above the average price" or "the product matching the maximum value" can be written in one go.

SELECT name, price
FROM products
WHERE price > (
    SELECT AVG(price) FROM products
);

A subquery with IN

This is the pattern of "filtering using a list of IDs that satisfy some condition in another table." You can get the same result with a JOIN, but sometimes this reads more naturally.

SELECT * FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE city = '東京'
);
-- '東京' = "Tokyo"

A subquery in the FROM clause (a derived table)

This is useful when you want to further filter a result that's already been aggregated once. For something like "categories whose average price is $200 or more," a derived table can sometimes be a cleaner fit than HAVING.

SELECT cat.category, cat.avg_price
FROM (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
) cat
WHERE cat.avg_price > 20000;