MySQL のサブクエリ

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

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)

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)

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)

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

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