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:

SELECT * FROM actor

WHERE actor_id IN 

(SELECT actor_id FROM film_actor

WHERE film_id = 2);

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.

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

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:

SELECT * FROM actor

WHERE actor_id IN 

(SELECT actor_id FROM film_actor

WHERE film_id = 

(SELECT film_id FROM film 

WHERE title = 'Ace Goldfinger')

);

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:

INSERT INTO ace_goldfinger_actors (first_name, last_name)

   SELECT first_name, last_name FROM actor a

INNER JOIN film_actor fa ON

a.actor_id = fa.actor_id

WHERE fa.film_id = 

(SELECT film_id FROM film 

WHERE title = 'Ace Goldfinger');

So we could create a table and insert data from a subquery (that contains another subquery). Here's what that might look like:

-- Create a fresh table

DROP TABLE IF EXISTS ace_goldfinger_actors;

CREATE TABLE ace_goldfinger_actors

(first_name VARCHAR(45), last_name VARCHAR(45));

​

-- Insert Data (from the following subqueries)

INSERT INTO ace_goldfinger_actors (first_name, last_name)

   -- Subquery

   SELECT first_name, last_name FROM actor a

INNER JOIN film_actor fa ON

a.actor_id = fa.actor_id

WHERE fa.film_id = 

           -- Nested Subquery

(SELECT film_id FROM film 

WHERE title = 'Ace Goldfinger');

-- Check the data that we just inserted

SELECT * FROM ace_goldfinger_actors;

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:

SELECT AVG(a) FROM 

(SELECT 

customer_id,

SUM(amount) a

FROM payment

GROUP BY customer_id) AS totals;

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: