Subqueries in the WHERE Clause

You know how to find the average price of a product using AVG(). You know how to find products over $100 using a WHERE clause. But what if you want to find all products that are more expensive than the average price?

You can’t write WHERE price > AVG(price) because aggregate functions aren’t allowed in the WHERE clause. You could run a query to get the average (say, $330), and then manually type a second query (WHERE price > 330), but that’s inefficient and won’t adapt when new data is added.

The solution is a Subquery.

What is a Subquery?

A subquery is simply a query nested inside another query. It allows you to pass the results of one SELECT statement directly into another.

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

When the database engine runs this, it executes the inner query first. It calculates the average price. Then, it replaces the inner query with that single calculated value, and executes the outer query.

The IN Operator with Subqueries

If your inner query returns exactly one value (like a single average), you use operators like >, <, or =.

But what if your inner query returns a list of values? For example, what if you want to find all orders placed by users who live in the USA?

You can use the IN operator, which checks if a value matches any item in a list.

SELECT * FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE country = 'USA'
);

In this case, the subquery returns a list of IDs (e.g., 1, 4, 8). The outer query then checks every order to see if its user_id is inside that list.

[!TIP] Subqueries in the WHERE clause are incredibly common and often provide a more readable alternative to complex JOIN statements when you only need data from the primary table.

Try writing a subquery in the interactive lab to find our premium, above-average priced products!