MySQL subqueries

This article describes what MySQL subqueries are and how to write SQL statements using subqueries.

The MySQL subquery is a SELECT statement nested within another statement, and also known as an inner query. Subqueries are often used in the WHERE clause. E.g:

SELECT *
FROM language
WHERE EXISTS(
    SELECT *
    FROM film
    WHERE film.language_id = language.language_id
  );

In this statement:

SELECT *
FROM film
WHERE film.language_id = language.language_id

It is a subquery. It is used in EXISTS clause to filter rows from language table.

Subqueries and IN

In this example, we will use language and film tables from Sakila sample database as a demonstration.

SELECT *
FROM language
WHERE language_id IN (
    SELECT DISTINCT language_id
    FROM film
  );

+-------------+---------+---------------------+
| language_id | name    | last_update         |
+-------------+---------+---------------------+
|           1 | English | 2006-02-15 05:02:19 |
+-------------+---------+---------------------+
1 row in set (0.01 sec)

In this example, we used the following subquery:

SELECT DISTINCT language_id
FROM film

The processing steps of this statement in MySQL are as follows:

  1. First, execute the subquery SELECT DISTINCT language_id FROM film and get a result set:

    +-------------+
| language_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

  2. Then, according to the above result set, execute the SQL statement. It is equivalent to the following SQL statement:

    SELECT *
FROM language
WHERE language_id IN (1);

Derived table

When a subquery is in a FORM clause, the subquery is called derived table.

Let us look at a SQL statement:

SELECT *
FROM (
    SELECT last_name,
      COUNT(*) count
    FROM actor
    GROUP BY last_name
  ) t
WHERE t.last_name LIKE 'A%';

+-----------+-------+
| last_name | count |
+-----------+-------+
| AKROYD    |     3 |
| ALLEN     |     3 |
| ASTAIRE   |     1 |
+-----------+-------+
3 rows in set (0.00 sec)

Please note this subquery:

SELECT last_name,
  COUNT(*) count
FROM actor
GROUP BY last_name

It is a derived table, and it has an alias t. Derived tables must have aliases, because any table in the FORM clause must have a name in MySQL.

Note: Derived tables are not temporary tables.

Derived tables follow these rules:

  • A derived table must have an alias.
  • The column names of a derived table must be unique.

Conclusion

In this article, you learned what MySQL subqueries are and how to write SQL statements using subqueries. The following are the main points of MySQL subqueries:

  • A subquery is a query nested in another statement.
  • Subqueries are usually used in a WHERE clause.
  • Subqueries in FORM clause are called derived tables. A derived table must have an alias.