Chapter 12: Subqueries and Nested Queries
A subquery (also called a nested query) is simply a SELECT statement inside another SELECT statement. It’s like asking a question, and inside the answer you ask another smaller question.
Subqueries make SQL incredibly powerful because they let you:
- Compare values with results from another query
- Filter rows based on complex logic
- Create temporary result sets on the fly
Today we’re going to master the three main places you can put a subquery:
- In the SELECT clause (returns a single value)
- In the FROM clause (creates a temporary table)
- In the WHERE clause (most common – filtering)
We’ll also cover correlated subqueries (the subquery depends on the outer query) and the super-useful EXISTS / NOT EXISTS operators.
We’ll continue using our bookshop database with authors and books tables.
Sample Data Reminder (books table – a bit more data for good examples)
| book_id | title | author_id | price | stock | category | published_year |
|---|---|---|---|---|---|---|
| 1 | The Room on the Roof | 1 | 250.00 | 45 | Fiction | 1956 |
| 2 | 2 States | 3 | 299.00 | 120 | Fiction | 2009 |
| 3 | The White Tiger | 2 | 399.00 | 35 | Fiction | 2008 |
| 4 | Wise and Otherwise | 5 | 199.00 | 80 | Non-Fiction | 2002 |
| 5 | The Immortals of Meluha | 4 | 349.00 | 65 | Fantasy | 2010 |
| 6 | The Blue Umbrella | 1 | 180.00 | 50 | Children | 1980 |
| 7 | Malgudi Days | 6 | 220.00 | 90 | Fiction | 1943 |
| 8 | Train to Pakistan | 7 | 280.00 | 60 | Historical | 1956 |
| 9 | Half Girlfriend | 3 | 280.00 | 95 | Fiction | 2014 |
| 10 | The Palace of Illusions | 8 | 450.00 | 25 | Mythology | 2008 |
1. Subqueries in the SELECT Clause (Scalar Subquery)
A subquery in SELECT must return exactly one value (one row, one column).
Example – Show each book and the average price of all books:
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT title, price, (SELECT ROUND(AVG(price), 2) FROM books) AS avg_book_price FROM books; |
Result (every row shows the same avg):
| title | price | avg_book_price |
|---|---|---|
| The Room on the Roof | 250.00 | 292.60 |
| 2 States | 299.00 | 292.60 |
| … | … | … |
More useful – Show each book and the average price of books by the same author:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT b.title, b.price, (SELECT ROUND(AVG(price), 2) FROM books b2 WHERE b2.author_id = b.author_id) AS avg_price_by_author FROM books b; |
This is a correlated subquery — it depends on the outer row (b.author_id).
2. Subqueries in the FROM Clause (Derived Table)
You treat the result of a subquery like a temporary table.
Example – Find the top 3 most expensive categories:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT category, avg_price, total_books FROM (SELECT category, ROUND(AVG(price), 2) AS avg_price, COUNT(*) AS total_books FROM books GROUP BY category) AS category_summary ORDER BY avg_price DESC LIMIT 3; |
Result might be:
| category | avg_price | total_books |
|---|---|---|
| Mythology | 450.00 | 1 |
| Fantasy | 349.00 | 1 |
| Fiction | 289.60 | 5 |
Pro Tip: Always give the subquery an alias (like AS category_summary) — some databases require it.
3. Subqueries in the WHERE Clause (Most Common)
This is where subqueries shine the most!
Example A – Books more expensive than the average price:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT title, price FROM books WHERE price > (SELECT AVG(price) FROM books); |
Example B – Books by authors who have written more than 1 book:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT title, author_id FROM books WHERE author_id IN ( SELECT author_id FROM books GROUP BY author_id HAVING COUNT(*) > 1 ); |
Result: Books by Ruskin Bond (author 1) and Chetan Bhagat (author 3)
4. Correlated Subqueries – The Subquery Knows About the Outer Row
A correlated subquery references columns from the outer query — it runs once for each row of the outer query.
Example – Find books that are more expensive than the average price of their own author:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT b.title, b.price, a.name AS author, (SELECT ROUND(AVG(price), 2) FROM books b2 WHERE b2.author_id = b.author_id) AS author_avg_price FROM books b INNER JOIN authors a ON b.author_id = a.author_id WHERE b.price > ( SELECT AVG(price) FROM books b2 WHERE b2.author_id = b.author_id ); |
Result: Books that are above their author’s personal average price.
Important: Correlated subqueries can be slower because they run repeatedly — but for small datasets they’re fine and very readable.
5. EXISTS / NOT EXISTS – Check for Existence (Very Efficient!)
Instead of comparing values, you just check if any row exists that matches a condition.
Syntax:
|
0 1 2 3 4 5 6 7 |
WHERE EXISTS (subquery) WHERE NOT EXISTS (subquery) |
Example A – Authors who have at least one book:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT name, country FROM authors a WHERE EXISTS ( SELECT 1 FROM books b WHERE b.author_id = a.author_id ); |
Example B – Authors who have NO books:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT name, country FROM authors a WHERE NOT EXISTS ( SELECT 1 FROM books b WHERE b.author_id = a.author_id ); |
Result: Chitra Banerjee, Vikram Seth, etc.
Why use EXISTS instead of IN?
- EXISTS stops as soon as it finds one match → usually faster
- Works well with correlated subqueries
- Handles NULLs better in some cases
Full Realistic Business Example
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
-- Report: Books that are priced higher than their author's average -- and where the author has written at least 2 books SELECT b.title, b.price, a.name AS author_name, ROUND(author_avg.avg_price, 2) AS author_avg_price, author_books.book_count AS books_by_author FROM books b INNER JOIN authors a ON b.author_id = a.author_id -- Subquery: average price per author INNER JOIN ( SELECT author_id, AVG(price) AS avg_price FROM books GROUP BY author_id ) author_avg ON b.author_id = author_avg.author_id -- Subquery: count of books per author INNER JOIN ( SELECT author_id, COUNT(*) AS book_count FROM books GROUP BY author_id HAVING COUNT(*) >= 2 ) author_books ON b.author_id = author_books.author_id WHERE b.price > author_avg.avg_price ORDER BY b.price DESC; |
