Correlated Subqueries
A subquery for every row.
So far, every subquery ran just once. But what if Helen asks, which books cost more than the average for their own genre? A cookbook should be compared with cookbooks, not with technology books. So the average changes for every row.

- Above average, for their genre
- Cookbooks vs cookbooks
- The average changes per row
Linked to the outer row
Look at the subquery's where. B two dot genre equals b dot genre. B is the book in the outer query. So for each book, the subquery works out the average for that book's genre. That's called a correlated subquery. Run it. The Silent Orchard costs fourteen ninety nine, above the mystery average of about thirteen dollars.
SELECT b.title, b.genre, b.price
FROM books AS b
WHERE b.price > (SELECT AVG(b2.price)
FROM books AS b2
WHERE b2.genre = b.genre)
LIMIT 5;โโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโโ โ title โ genre โ price โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโผโโโโโโโโค โ The Silent Orchard โ Mystery โ 14.99 โ โ Python for Curious Minds โ Technology โ 34.99 โ โ The Last Lighthouse โ Mystery โ 13.99 โ โ Running on Empty โ Self-help โ 15.99 โ โ Dragons of Ember Bay โ Fantasy โ 17.99 โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโ
Above-average salaries
Your turn, with a simpler one. Which staff members earn more than the average salary? Show their name, role, and salary. Pause and try. Here's a solution. A subquery finds the average salary, and where keeps everyone above it. Helen and her two managers.
Staff earning more than the average salary
Show hint
WHERE salary > (SELECT AVG(salary) FROM staff)
Show solution
SELECT name, role, salary
FROM staff
WHERE salary > (SELECT AVG(salary) FROM staff);โโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโฌโโโโโโโโโ โ name โ role โ salary โ โโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโผโโโโโโโโโค โ Helen Ward โ Owner โ 72000 โ โ Marcus Lee โ Store Manager โ 54000 โ โ Priya Nair โ Online Manager โ 56000 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโดโโโโโโโโโ
Summary
A correlated subquery refers to the outer query, so it runs once per row. Powerful, but it can be slow on big tables. Next, a much tidier way to write multi step queries.
Key takeaways- Refers to the outer row
- Runs once per row
- Can be slow