> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL subqueries > How a MySQL subquery lets one query use the answer of another, where a subquery can appear, and what ERROR 1242 means when one returns too many rows. 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 ```sql theme={null} 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. ```sql theme={null} SELECT title, length FROM film WHERE length = (SELECT MAX(length) FROM film) ORDER BY title; ``` ```text theme={null} +--------------------+--------+ | 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. ```sql theme={null} SELECT title, length, (SELECT AVG(length) FROM film) AS catalog_mean FROM film ORDER BY title LIMIT 3; ``` ```text theme={null} +------------------+--------+--------------+ | 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](/docs/tutorial/in) is allowed to return many rows, because `IN` expects a list rather than a value. ```sql theme={null} 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; ``` ```text theme={null} +------------+-----------+ | 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. ```sql theme={null} SELECT title FROM film WHERE length = (SELECT length FROM film); ``` ```text theme={null} 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. ## See also * [Correlated subqueries](/docs/tutorial/correlated-subqueries) — a subquery that reads the outer row * [IN](/docs/tutorial/in) — the operator that accepts a list from a subquery * [Derived tables](/docs/tutorial/derived-tables) — a subquery used as a table * [Subqueries vs JOINs in MySQL](/docs/guides/subqueries-vs-joins) — which shape to pick, and what each costs