The subquery is a query that's nested inside another query — a query within a query.
Subqueries are a powerful concept that allow you to use the results of another query inside a
WHERE clause. This allows you to pass a dynamic value to the
WHERE clause, as the value will depend on the result of the query.
Here's an example of a subquery:
In the above example, the subquery is enclosed in parentheses.
We retrieve the details of all actors who appeared in the film with a
2. We can do this because the
film_actor table contains
A subquery can also be called an "inner query" or "inner select". The statement that contains the subquery can also be referred to as an "outer query" or "outer select".
You can nest a subquery within another subquery if required.
In the above example, we had to use the
film_id because the
film_actor table doesn't contain the film title. It would be nicer if we could use the film title instead of having to remember the film ID for every film we need to look up. This is where a nested subquery can come in handy.
We could use a nested subquery like this:
The above example uses a nested subquery to retrieve the
film_id from the
film table where the
Ace Goldfinger. This happens to be the same film as in the previous example (its
film_id is 2), therefore, we get the same list of actors as the previous example.
So, now all we need to do is think of a film title and we can retrieve its actors — no need to try to remember the film ID.
Subqueries within Other Statements
Subqueries are not necessarily limited to the
WHERE clause, or to the
SELECT statement for that matter.
For example, you could use a subquery inside an
INSERT statement to insert data based on the results of a subquery.
For example, here's an
INSERT statement containing a subquery and a nested subquery:
So we could create a table and insert data from a subquery (that contains another subquery). Here's what that might look like:
In the above example, we first create a new table (for our data to be inserted into). We use a subquery (and a nested subquery) inside the
INSERT statement to retrieve the data that we need, then insert it into the new table. Lastly, we select all records from the new table to verify that the correct data was inserted.
When a subquery starts at the
FROM clause (as opposed to the
WHERE clause), the result set is referred to as a derived table (also known as a materialized subquery).
The above derived table returns the total amount each customer has spent, then the outer query returns the average across all customers.
Note that you must provide an alias for all derived tables. In this case we gave our derived table an alias of
totals (we did this by adding
AS totals after the subquery).
If we run only the subquery, we can see the derived table:
The examples on this page use the Sakila sample database.