MySQL in Operator: Match Any Value in a List

IN checks whether a value matches any value in a list. It is shorter and clearer than many ORs.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

query.sql

SQL

SELECT name, category FROM products WHERE category IN ('bags', 'home') ORDER BY name;

Output

OUTPUT

+---------------+----------+
| name          | category |
+---------------+----------+
| Backpack      | bags     |
| Desk Lamp     | home     |
| Laptop Sleeve | bags     |
| Water Bottle  | home     |
+---------------+----------+
4 rows in set

That is the same as category = 'bags' OR category = 'home', but easier to read and to extend.

NOT IN

query.sql

SQL

SELECT name, category FROM products WHERE category NOT IN ('bags', 'home') ORDER BY name;

Output

OUTPUT

+--------------+------------+
| name         | category   |
+--------------+------------+
| Gel Pen      | stationery |
| Marker Set   | stationery |
| Notebook     | stationery |
| Sticky Notes | stationery |
+--------------+------------+
4 rows in set

Numbers and dates work too

query.sql

SQL

1
2

SELECT id, name FROM products WHERE id IN (2, 4, 6);
SELECT name, added_on FROM products WHERE added_on IN ('2026-01-10', '2026-04-18') ORDER BY added_on;

Output

OUTPUT

+----+--------------+
| id | name         |
+----+--------------+
|  2 | Gel Pen      |
|  4 | Marker Set   |
|  6 | Water Bottle |
+----+--------------+
3 rows in set

+--------------+------------+
| name         | added_on   |
+--------------+------------+
| Notebook     | 2026-01-10 |
| Sticky Notes | 2026-04-18 |
+--------------+------------+
2 rows in set

IN with a subquery

The list can come from another query (Subqueries):

query.sql

SQL

SELECT name, price
FROM products
WHERE category IN (SELECT category FROM products WHERE price > 500)
ORDER BY name;

Output

OUTPUT

+---------------+--------+
| name          | price  |
+---------------+--------+
| Backpack      | 999.00 |
| Desk Lamp     | 650.00 |
| Laptop Sleeve | 450.00 |
| Water Bottle  | 220.00 |
+---------------+--------+
4 rows in set

The subquery finds the categories that contain a product over 500 (bags). The outer query lists all products in those categories.

The NOT IN and NULL trap

NOT IN has a nasty surprise: if the list contains a NULL, the result is never true, so you get no rows.

query.sql

SQL

1
2
3

SELECT 5 IN (1, 2, 5) AS in_list,
       5 NOT IN (1, 2, 3) AS not_in_list,
       5 NOT IN (1, 2, NULL) AS not_in_with_null;

Output

OUTPUT

+---------+-------------+------------------+
| in_list | not_in_list | not_in_with_null |
+---------+-------------+------------------+
|       1 |           1 |             NULL |
+---------+-------------+------------------+
1 row in set

The last result is NULL, not 1. Why? 5 NOT IN (1, 2, NULL) means 5 <> 1 AND 5 <> 2 AND 5 <> NULL. The last part is unknown, so the whole thing is unknown, and WHERE drops the row.

This often happens with subqueries:

query.sql

SQL

CREATE TABLE teams (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE players (id INT PRIMARY KEY, team_id INT NULL);
INSERT INTO teams VALUES (1, 'Red'), (2, 'Blue'), (3, 'Green');
INSERT INTO players VALUES (10, 1), (11, 1), (12, NULL);

SELECT name FROM teams WHERE id NOT IN (SELECT team_id FROM players);

Output

OUTPUT

Empty set

You expected Blue and Green (teams without players). The NULL in players.team_id made the result empty. Two safe fixes:

query.sql

SQL

1
2

SELECT name FROM teams WHERE id NOT IN (SELECT team_id FROM players WHERE team_id IS NOT NULL) ORDER BY name;
SELECT name FROM teams t WHERE NOT EXISTS (SELECT 1 FROM players p WHERE p.team_id = t.id) ORDER BY name;

Output

OUTPUT

+-------+
| name  |
+-------+
| Blue  |
| Green |
+-------+
2 rows in set

+-------+
| name  |
+-------+
| Blue  |
| Green |
+-------+
2 rows in set

NOT EXISTS (EXISTS) is the safest form because it handles NULL the way you expect.

How long can the list be?

MySQL handles lists with thousands of values, but each value must be parsed, and very long lists are slow to plan. For a list of more than a few hundred values, put them in a temporary table and join. IN with constant values can use an index, and MySQL sorts the list and does a quick lookup for each value (a range scan).

Type consistency

Keep the types in the list the same as the column. WHERE id IN ('1', '2') on an integer column still works, but forces conversions.

lightbulb

NOT IN with a subquery

Prefer NOT EXISTS, or add WHERE column IS NOT NULL inside the subquery. This avoids the silent empty result.