EXISTS, and the subquery that looks at the outer row

The subqueries so far ran once and were done. A correlated subquery looks at the current row of the outer query and is evaluated again for every row: "does this author have at least one book?"

1SELECT a.name
2FROM authors AS a
3WHERE EXISTS (
4  SELECT 1 FROM books AS b WHERE b.author_id = a.id
5);

EXISTS looks at whether the inner query returns at least one row; what it returns does not matter, hence SELECT 1. The a.id inside comes from the outer row: that is what makes the connection. For the opposite, NOT EXISTS.

The same idea finds "the most expensive book of each genre": pick a book if its price equals the highest price in its own genre. The subquery works out, for each book, the highest price of that book's genre: WHERE b.price = (SELECT MAX(price) FROM books WHERE genre = b.genre).

Tasks