> ## 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 IN > How the MySQL IN operator tests one column against a list of values, how NOT IN differs, and why a NULL anywhere in the list makes NOT IN return nothing. All examples on this page work on VillageSQL. Install Now → `IN` tests one column against several values at once. It says what a chain of `OR` says, in one clause, and it is the clause most people reach for once a filter grows past 2 values. Open the client with `mysql -u root -p sakila` before you start. ## How IN works ```sql theme={null} SELECT COLUMN_LIST FROM TABLE_NAME WHERE COLUMN_NAME IN (FIRST_VALUE, SECOND_VALUE); ``` Replace `COLUMN_NAME` with the column to test, and `FIRST_VALUE` and `SECOND_VALUE` with the values to accept. The condition is true when the column equals any value in the brackets. Separate the values with commas, and use as many as you need. `x IN (a, b)` means the same as `x = a OR x = b`. Write whichever reads better, and expect `IN` to read better past 2 values. ## Examples ### 1) Match any of several values ```sql theme={null} SELECT title, rating FROM film WHERE rating IN ('G', 'PG') ORDER BY title LIMIT 5; ``` ```text theme={null} +------------------+--------+ | title | rating | +------------------+--------+ | ACADEMY DINOSAUR | PG | | ACE GOLDFINGER | G | | AFFAIR PREJUDICE | G | | AFRICAN EGG | G | | AGENT TRUMAN | PG | +------------------+--------+ 5 rows in set (0.00 sec) ``` ### 2) Match none of them ```sql theme={null} SELECT title, rating FROM film WHERE rating NOT IN ('G', 'PG') ORDER BY title LIMIT 5; ``` ```text theme={null} +------------------+--------+ | title | rating | +------------------+--------+ | ADAPTATION HOLES | NC-17 | | AIRPLANE SIERRA | PG-13 | | AIRPORT POLLOCK | R | | ALABAMA DEVIL | PG-13 | | ALADDIN CALENDAR | NC-17 | +------------------+--------+ 5 rows in set (0.00 sec) ``` ### 3) A list of numbers ```sql theme={null} SELECT first_name, last_name FROM actor WHERE actor_id IN (1, 5, 10) ORDER BY actor_id; ``` ```text theme={null} +------------+--------------+ | first_name | last_name | +------------+--------------+ | PENELOPE | GUINESS | | JOHNNY | LOLLOBRIGIDA | | CHRISTIAN | GABLE | +------------+--------------+ 3 rows in set (0.00 sec) ``` Numbers take no quotes. The rows come back in `actor_id` order because `ORDER BY` asked for it, not because the list is in that order. ### 4) A list the server works out for itself The brackets can hold a query instead of a list. `film_actor` is the table that pairs each film with each actor in it, so this asks for the films actor 1 appears in, without you knowing any film id. ```sql theme={null} SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor WHERE actor_id = 1) ORDER BY title LIMIT 5; ``` ```text theme={null} +-----------------------+ | title | +-----------------------+ | ACADEMY DINOSAUR | | ANACONDA CONFESSIONS | | ANGELS LIFE | | BULWORTH COMMANDMENTS | | CHEAPER CLYDE | +-----------------------+ 5 rows in set (0.00 sec) ``` A query written inside another query is a subquery. A later section covers them properly. It is worth meeting one here because an `IN` list is where most people first write one. ## A NULL in the list breaks NOT IN `IN` and `NOT IN` do not treat a NULL in the list the same way, and neither one warns you. With `IN`, a NULL in the list is harmless. A row that matches a real value is still kept. ```sql theme={null} SELECT title, rating FROM film WHERE rating IN ('G', NULL) ORDER BY title LIMIT 3; ``` ```text theme={null} +------------------+--------+ | title | rating | +------------------+--------+ | ACE GOLDFINGER | G | | AFFAIR PREJUDICE | G | | AFRICAN EGG | G | +------------------+--------+ 3 rows in set (0.00 sec) ``` With `NOT IN`, the same list returns nothing at all. ```sql theme={null} SELECT title, rating FROM film WHERE rating NOT IN ('G', NULL) ORDER BY title LIMIT 3; ``` ```text theme={null} Empty set (0.01 sec) ``` Every film has a rating, and 822 of them are not rated `G`, so an empty result is a surprise. The reason is that `NOT IN` asks whether the column differs from every value in the list, and nothing can be shown to differ from NULL. Comparing anything to NULL gives NULL rather than true or false, so the whole condition is never true and no row survives. This matters most when the list is a subquery, because a subquery you did not write by hand can return a NULL you did not expect. Add `WHERE COLUMN_NAME IS NOT NULL` inside the subquery when that is possible. The [IS NULL](/docs/tutorial/is-null) lesson covers how NULL compares. ## Summary * Use `IN` to test one column against a list of values in one clause. * Read `x IN (a, b)` as `x = a OR x = b`. * Quote strings in the list and leave numbers bare. * Put a subquery in the brackets when the list is itself an answer. * Keep NULL out of a `NOT IN` list, because it returns no rows at all. ## See also * [AND, OR and NOT](/docs/tutorial/and-or-not) — the OR chain IN replaces * [IS NULL](/docs/tutorial/is-null) — why comparing to NULL is neither true nor false * [BETWEEN](/docs/tutorial/between) — testing a range rather than a list * [Subqueries](/docs/tutorial/subqueries) — the query form used in example 4