MySQL Subquery

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:

Result:

In the above example, the subquery is enclosed in parentheses.

We retrieve the details of all actors who appeared in the film with a film_id of 2. We can do this because the film_actor table contains actor_id and film_id columns.

Nested Subqueries

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:

Result:

The above example uses a nested subquery to retrieve the film_id from the film table where the title equals 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.

You can use a subquery anywhere an expression is allowed. You can use a subquery within any of the following statements: SELECT, INSERT, UPDATE, DELETE, SET, or DO.

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:

Result:

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.

Derived Tables

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).

Example:

Result:

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.