Correlated subqueries in filter
Let’s say we want to filter out
courses for which the
average price of
books belonging to that course is
more than 500.
We already know how to do it. Using a
GROUP BY query to calculate the average, over a
LEFT JOIN of
books, and then using
HAVING to filter out the cap over average price of books. Something like the query below:
However there is another way to do it, using a subquery. Have a look at the query below:
Key thing to notice is the
courses.id at the right hand side of
WHERE clause in the subquery. What
courses does it exactly points to, when there is only
books in the
FROM clause? Let’s add a few aliases to the query that make it easier to understand.
c.id at the right hand side of filter in the subquery, is pointing to the
id from the
parent query (i.e. the one on
courses). So, this subquery, calculates the AVG price of all the books that belong to the course, by using the id of the course from the parent query, for each course, and then compares it with 500 to get the final results.
These kind of subqueries, that employ a column/derived column from the parent query they are a part of, are called