Subqueries
A query inside a query.
Helen asks a simple sounding question. Which books cost more than the average price? To answer it, you need two steps. First, work out the average. Then, find the books above it. You could run two queries, and copy the number across by hand. But there's a neater way. Put one query inside the other.

- Books above the average price
- Step one: find the average
- Step two: compare to it
Step one, by hand
Here's the first step on its own. The average book price. Run it. Eighteen eleven. Now we could write, where price is greater than eighteen point one one. But if Helen changes a price tomorrow, that number is out of date.
SELECT ROUND(AVG(price), 2) AS average_price
FROM books;โโโโโโโโโโโโโโโโโ โ average_price โ โโโโโโโโโโโโโโโโโค โ 18.11 โ โโโโโโโโโโโโโโโโโ
A subquery
Instead, we put the first query inside brackets, right where the number would go. That's called a subquery. SQL runs the inner query first, gets eighteen eleven, and then uses it in the outer query. Run it. These are the five most expensive books above average. And it will always use today's average.
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books)
ORDER BY price DESC
LIMIT 5;โโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโ โ title โ price โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโค โ Python for Curious Minds โ 34.99 โ โ Data for Everyone โ 31.99 โ โ SQL in a Weekend โ 29.99 โ โ Spice Roads โ 27.99 โ โ The Quiet Kitchen โ 24.99 โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
The most expensive book
Here's another classic. Which book has the highest price? The subquery finds the maximum price. The outer query finds the book with that price. Run it. Python for Curious Minds, at thirty four ninety nine.
SELECT title, price
FROM books
WHERE price = (SELECT MAX(price) FROM books);โโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโ โ title โ price โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโค โ Python for Curious Minds โ 34.99 โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
Rules for subqueries
A few simple rules. A subquery always goes in brackets. When you compare with equals, or greater than, the subquery must return just one value. The inner query runs first. And you can read it inside out. Start with the brackets, then read the outside.
Summary
A subquery is a query in brackets, used inside another query. It replaces a number you'd otherwise copy by hand. Next, subqueries that return a whole list.
Key takeaways- Query in brackets
- Inner runs first
- Always up to date