1. Nested query
So far we only used subqueries which appear in the FROM clause. Now, we will introduce a new type of subquery called a nested query.
2. Nested query
A nested query is a query where a complete SELECT block appears in the WHERE clause or the HAVING clause of another query.
This inner block or subquery is typically executed first by the query optimizer. It can have single or multiple values as a result, which are used by the outer query.
Let's first have a look at a nested query with multiple values in the inner block.
3. The inner query
We would like to know which customers were really disappointed by a movie they watched. Let's say a rating smaller than or equal to 3 shows that the customer really didn't like the movie. We use SELECT DISTINCT customer_id, since we don't want a customer listed several times if he or she rated several movies with 3 or lower. From the table renting we can extract only the list of customer IDs. So what can be done if we want to know the names of the customers?
4. Result in the WHERE clause
We could use now the result form the inner query to select the names of the customers with the corresponding IDs in the WHERE clause. Of course it is very impractical to copy the result form one query to another query.
5. The outer query
Instead we can solve the problem in a single query by using the inner query directly in the WHERE clause of the outer query. In the outer query we select all names of customers whose customer ID is IN the result table of the inner query.
Note that you need to use IN in this WHERE clause since the inner query returns multiple values.
As a result we obtain the names of all customers who gave a rating of 3 or lower.
6. Nested query in the HAVING clause
Next we will see how the subquery can appear in the HAVING clause of the outer query.
We select the earliest date when an account was created by a customer from Austria. The result is a single value.
7. Nested query in the HAVING clause
In the outer query we group the customer data by country and list the country name and the earliest date when an account was created in this country. In the HAVING clause we use the restriction to report only countries where the date of the first account is smaller than the result from the inner query, which is the date of the first account created in Austria.
We get a list of countries and dates where accounts were created earlier than in Austria.
8. Who are the actors in the movie Ray?
Here we go back to nested queries in the WHERE clause. It is possible to have several levels of nesting. Let's start with the lowest-level inner block. There we select the movie ID of the movie 'Ray'. This is a single value. On the next level we select the actor IDs of actors who play in the movie with ID equal to the result from the lowest-level inner block. This list of actor IDs is used in the outer query where the name of actors is reported with actor_id IN the result of the inner query.
9. Let's practice!
Now, it's your turn to use nested queries.