1. Correlated nested queries
So far the nested subquery in the inner block could be entirely solved before processing the outer select block. This is no longer the case for correlated nested queries.
2. Correlated queries
In a correlated query the condition in the WHERE clause of the inner query references some column of a table declared in the outer query. The nested query is then evaluated once for each row in the outer query. Let's give some examples.
3. Example correlated query
Assume we want to know which movies were rented more than 5 times. To answer this question we need information from the table movies and from the table renting as you can see in this query. As before, first, the inner select block is evaluated. In the WHERE clause of the inner select block, we refer to the table movies by using 'm-dot-movie id'. So this is a correlated query. To solve this correlated query the solver iterates through each row of the table movies and evaluates the subquery for the given value of movie_id.
Note, that we need to use aliases for the table names in the inner and outer query.
4. Evaluate inner query
Here, we have the inner query evaluated for 'm-dot-movie_id' equal to one. The number of movie rentals for this movie is 8. This result is then passed to the outer query.
5. Evaluate outer query
In the outer query we check if 5 is smaller than the value from the inner query, which is the number of movie rentals.
For movie_id = 1 we counted 8 movie rentals, since 5 is smaller than 8 the movie with ID one is listed in the results table. It's the movie 'One Night at McCools's'.
Basically, a correlated query implements a looping mechanism. We are looping through the subquery for each row of the table defined in the outer query block.
6. Less than 5 movie rentals
We can also ask the question which movies were rented less than 5 times. Then we use in the WHERE clause of the outer query the expression 5 is larger than the number of movie rentals, which is the result of the inner query. The movie 'The Human Stain' with ID 17 is one of the movies with less than 5 rentals.
7. Let's practice!
Now you can try working with correlated nested queries yourself.