MySQL IN

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

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

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

SELECT title, rating
FROM film
WHERE rating IN ('G', 'PG')
ORDER BY title
LIMIT 5;

+------------------+--------+
| 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

SELECT title, rating
FROM film
WHERE rating NOT IN ('G', 'PG')
ORDER BY title
LIMIT 5;

+------------------+--------+
| 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

SELECT first_name, last_name
FROM actor
WHERE actor_id IN (1, 5, 10)
ORDER BY actor_id;

+------------+--------------+
| 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.

SELECT title
FROM film
WHERE film_id IN (SELECT film_id FROM film_actor WHERE actor_id = 1)
ORDER BY title
LIMIT 5;

+-----------------------+
| 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.

SELECT title, rating
FROM film
WHERE rating IN ('G', NULL)
ORDER BY title
LIMIT 3;

+------------------+--------+
| 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.

SELECT title, rating
FROM film
WHERE rating NOT IN ('G', NULL)
ORDER BY title
LIMIT 3;

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 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.

Getting Started

Server Development

Using Extensions

Creating Extensions

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

How IN works

Examples

1) Match any of several values

2) Match none of them

3) A list of numbers

4) A list the server works out for itself

A NULL in the list breaks NOT IN

Summary

See also

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

​How IN works

​Examples

​1) Match any of several values

​2) Match none of them

​3) A list of numbers

​4) A list the server works out for itself

​A NULL in the list breaks NOT IN

​Summary

​See also

How IN works

Examples

1) Match any of several values

2) Match none of them

3) A list of numbers

4) A list the server works out for itself

A NULL in the list breaks NOT IN

Summary

See also