SQL and MySQL course Β· Module 8: Subqueries
Correlated subqueries
In this lesson3
We go deeper. So far the subquery ran once and returned a ready result. A correlated subquery is different: it references a column from the outer query and runs anew for each row processed on the outside.
A reference to the outer row
SELECT a.imie, a.nazwisko
FROM autorzy AS a
WHERE 3 < (
SELECT COUNT(*)
FROM ksiazki AS k
WHERE k.autor_id = a.id
);Notice the condition k.autor_id = a.id. The subquery knows a.id - the value from the current row of the outer query. For each author the Library counts their books and keeps only those with more than three.
How it works step by step
- The outer query takes the first author.
- The subquery counts that author's books.
- The condition checks the result - the author stays or drops out.
- The Library moves on to the next author and repeats everything from the start.
Plain or correlated?
- A plain subquery is self-contained - you can run it on its own.
- A correlated one makes no sense without the outer query - it needs the value
a.id.
Correlated subqueries can be slower (they run many times), but they are remarkably powerful - they let you ask questions like "for each row, check something on the other side".
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What characterizes a correlated subquery?
2. How does a correlated subquery differ from a plain one?
Hands-on tasks in the game
- Vertical ordering
Arrange (top to bottom) the steps of running a correlated subquery for each author:
- Code editor
Write a query that retrieves from the autorzy table (alias a) the imie and nazwisko columns of those authors who have more than 3 books. Use a correlated COUNT(*) subquery on the ksiazki table (alias k), where the condition is k.autor_id = a.id.