IN and NOT IN
IN is shorthand for a chain of OR equality tests, and NOT IN returns nothing at all when the value list contains a NULL.
expr IN (value, ...) -- same as expr = value OR expr = value OR ...IN() tests whether a value appears in a list. It is shorthand: expr IN (a, b, c) is the same question as expr = a OR expr = b OR expr = c, just shorter and easier for the optimizer to turn into a range scan when the list is made of constants of one type. NOT IN (…) is defined as NOT (expr IN (…)).
The NULL trap
To comply with the SQL standard, IN() returns NULL not only when the left-hand expression is NULL, but also when no match is found and one of the list expressions is NULL. Nothing matched, but a NULL in the list might have matched, so the answer is unknown rather than false.
NOT IN then inherits that NULL, because NOT NULL is NULL. The result is a filter that returns zero rows and no error. It shows up most often with subqueries: WHERE id NOT IN (SELECT parent_id FROM child) returns an empty set the moment a single parent_id is NULL.
Mixed types
Never mix quoted and unquoted values in one list. The comparison rules differ, and MySQL converts both sides to floating-point when they disagree: SELECT 'a' IN (0), 0 IN ('b'); returns 1, 1.
Example
-- Goal: top-level categories, i.e. categories nobody lists as a parent.
-- categories.parent_id is nullable, and root rows carry NULL there.
-- Returns zero rows. The subquery contains NULLs, so every
-- "id NOT IN (...)" test evaluates to NULL instead of TRUE.
SELECT id, name
FROM categories
WHERE id NOT IN (SELECT parent_id FROM categories);
-- Fix 1: exclude the NULLs from the list.
SELECT id, name
FROM categories
WHERE id NOT IN (SELECT parent_id FROM categories WHERE parent_id IS NOT NULL);
-- Fix 2: NOT EXISTS, which is immune to the problem because it asks
-- about row existence rather than about equality with each value.
SELECT c.id, c.name
FROM categories AS c
WHERE NOT EXISTS (
SELECT 1 FROM categories AS k WHERE k.parent_id = c.id
);When to use it
- Filter orders down to a handful of status values without writing four OR conditions
- Find records that are missing from a related table, safely, when the join column is nullable
- Look up rows by a composite key using a row-constructor IN list
- Track down a NOT IN filter that returns an empty result set with no error
More examples
IN and the OR chain it stands for
Worth keeping in mind when you are reading an unfamiliar WHERE clause: an IN list is not a special construct, it is equality repeated. That is also why a NULL status never appears in either result — every one of those equality tests returns NULL for it.
-- Shorthand
SELECT id, status FROM orders
WHERE status IN ('paid', 'shipped', 'delivered');
-- Longhand: the same rows, the same plan
SELECT id, status FROM orders
WHERE status = 'paid' OR status = 'shipped' OR status = 'delivered';
-- NOT IN is defined as the negation of the whole IN test
SELECT id, status FROM orders
WHERE status NOT IN ('cancelled', 'refunded'); -- NOT (status IN (...))IN with a row constructor
Useful for batch lookups on a composite primary key, where an application has a list of (id, sku) pairs to fetch in one round trip. The MySQL optimizer can apply the range access method to IN() expressions with row-constructor arguments, so a composite index on (order_id, sku) is still usable.
-- Match two columns at once against a list of pairs.
SELECT *
FROM order_items
WHERE (order_id, sku) IN ((1001, 'ABC-1'), (1001, 'ABC-2'), (1007, 'XYZ-9'));
-- Longhand
SELECT *
FROM order_items
WHERE (order_id = 1001 AND sku = 'ABC-1')
OR (order_id = 1001 AND sku = 'ABC-2')
OR (order_id = 1007 AND sku = 'XYZ-9');Do not mix quoted and unquoted values
Reach for this when an IN filter over a string column returns rows that plainly do not belong. The manual is explicit that quoted and unquoted values follow different comparison rules and that mixing them leads to inconsistent results; keep every list entry the same type as the column.
SELECT 'a' IN (0), 0 IN ('b'); -- 1, 1
-- Both comparison values are converted to floating point, giving 0.0
-- on each side, so the comparison succeeds. On a VARCHAR column an
-- unquoted list turns unrelated text rows into matches:
SELECT * FROM legacy_rows WHERE ref_code IN (0, 1, 2); -- matches 'abc'
-- Quote the list to match the column's type.
SELECT * FROM legacy_rows WHERE ref_code IN ('0', '1', '2');
Discussion