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

Correlated Subqueries

A subquery for every row.

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

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.

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

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          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.

โœ๏ธ Exercise: Above-average salaries

Staff earning more than the average salary

Show hint

WHERE salary > (SELECT AVG(salary) FROM staff)

Show solution
solution.sqlsql
SELECT name, role, salary
FROM staff
WHERE salary > (SELECT AVG(salary) FROM staff);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    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
Next up: Common Table Expressions โ€” WITH makes queries readable