MySQL subqueries

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

A subquery is a query inside another query. It lets you use an answer you do not know yet: the longest film, the average rating, the set of ids that match something. Without one you would run 2 queries and paste the first result into the second by hand. Open the client with mysql -u root -p sakila before you start.

How a subquery works

SELECT COLUMN_LIST
FROM TABLE_NAME
WHERE COLUMN_NAME = (SELECT AGGREGATE_FUNCTION(COLUMN_NAME) FROM TABLE_NAME);

Replace COLUMN_LIST with the columns you want, TABLE_NAME with the table, COLUMN_NAME with the column to test, and AGGREGATE_FUNCTION with the function that produces the value you are comparing against. The server runs the subquery, takes its result, and uses it in the outer query. A subquery in brackets that returns exactly 1 row and 1 column is a scalar subquery, and it behaves like a value. That is the form below, and the form that goes wrong most often.

Examples

1) Use a value you cannot write down

You do not know the longest film’s length, so ask for it in place.

SELECT title, length
FROM film
WHERE length = (SELECT MAX(length) FROM film)
ORDER BY title;

+--------------------+--------+
| title              | length |
+--------------------+--------+
| CHICAGO NORTH      |    185 |
| CONTROL ANTHEM     |    185 |
| DARN FORRESTER     |    185 |
...
+--------------------+--------+
10 rows in set (0.00 sec)

MAX(length) on its own would give 185 and nothing else. The subquery feeds it back into a WHERE so you get the films as well.

2) A subquery in the select list

The same value can go in the select list, where it repeats on every row.

SELECT title, length, (SELECT AVG(length) FROM film) AS catalog_mean
FROM film
ORDER BY title
LIMIT 3;

+------------------+--------+--------------+
| title            | length | catalog_mean |
+------------------+--------+--------------+
| ACADEMY DINOSAUR |     86 |     115.2720 |
| ACE GOLDFINGER   |     48 |     115.2720 |
| ADAPTATION HOLES |     50 |     115.2720 |
+------------------+--------+--------------+
3 rows in set (0.00 sec)

The subquery runs once and its answer is copied down the column, which is how you show a value beside the rows you are comparing against it.

3) A subquery that returns a list

A subquery feeding IN is allowed to return many rows, because IN expects a list rather than a value.

SELECT first_name, last_name
FROM actor
WHERE actor_id IN (SELECT actor_id FROM film_actor WHERE film_id = 1)
ORDER BY last_name
LIMIT 5;

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| JOHNNY     | CAGE      |
| ROCK       | DUKAKIS   |
| CHRISTIAN  | GABLE     |
| PENELOPE   | GUINESS   |
| MARY       | KEITEL    |
+------------+-----------+
5 rows in set (0.00 sec)

The inner query returns the ids of everyone in film 1, and IN tests each actor against that whole list.

ERROR 1242 means the subquery returned too many rows

= compares 1 thing to 1 thing. Give it a subquery that returns 1,000 rows and the server cannot go on.

SELECT title FROM film WHERE length = (SELECT length FROM film);

ERROR 1242 (21000): Subquery returns more than 1 row

3 ways out, depending on what you meant:

Use IN instead of =, when a list is what you wanted.
Add an aggregate such as MAX(), when you wanted 1 value from the set.
Add LIMIT 1, when any 1 row will do and you can say which with ORDER BY.

ERROR 1242 at least fails loudly. The version of this mistake that does not is a subquery returning no rows at all: that gives NULL, the comparison gives NULL, and the outer query silently returns nothing.

Summary

Use a subquery to feed one query’s answer into another.
Expect a scalar subquery, 1 row and 1 column, to behave like a value.
Read ERROR 1242 as = against a subquery that returned many rows.
Use IN rather than = when the subquery returns a list.
Suspect an empty subquery when an outer query returns nothing and no error.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How a subquery works

Examples

1) Use a value you cannot write down

2) A subquery in the select list

3) A subquery that returns a list

ERROR 1242 means the subquery returned too many rows

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How a subquery works

​Examples

​1) Use a value you cannot write down

​2) A subquery in the select list

​3) A subquery that returns a list

​ERROR 1242 means the subquery returned too many rows

​Summary

​See also

How a subquery works

Examples

1) Use a value you cannot write down

2) A subquery in the select list

3) A subquery that returns a list

ERROR 1242 means the subquery returned too many rows

Summary

See also