AI Hints
Homeโ€บSQL Tutorialโ€บSubqueries
SQL ยท Lesson 54 of 126

Subqueries

A query inside a query.

โฑ 2 min readโ–ถ Video at 1:16:59๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Subqueries
From the video: Subqueries
  • 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.

query.sqlsql
SELECT ROUND(AVG(price), 2) AS average_price
FROM books;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books)
ORDER BY price DESC
LIMIT 5;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          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.

query.sqlsql
SELECT title, price
FROM books
WHERE price = (SELECT MAX(price) FROM books);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          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.

Bracketsalways
One valuefor =, >, <
Runs firstinner, then outer
Read itinside out

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
Next up: Subqueries with IN โ€” matching against a list