SQL / SELECTING ROWS
Matching sets of values with IN
Filter rows against a list of values with IN and NOT IN, and predict how NULLs in the column or the list change which rows come back.
What you will learn
- Replace repeated OR equality tests on one column with a single IN list
- Combine IN with AND safely, since an IN list already counts as one predicate
- Explain why a single NULL inside a NOT IN list empties the result
- Keep NULL-valued rows using NOT IN (...) OR col IS NULL
Understanding Matching sets of values with IN
IN answers exactly one question: is the value in this column equal to any of the values I listed? The engine treats col IN (a, b, c) as col = a OR col = b OR col = c, so everything true of equality is true here — the comparison is exact, it follows the column's type and collation rules, and it cannot express a range or a pattern. That expansion is also why an IN list needs no parentheses of its own: to the surrounding AND and OR it is a single condition, so writing AND total > 50 beside it means what it looks like.
The list is a set, not a sequence. Repeating a value changes nothing, listing a value that no row contains is not an error, and the order of the list has no effect on the order of the result, because rows still come back in whatever order the engine reads them. Each element is compared to the column separately, so mixing types leans on the engine's implicit conversion rules, which differ between products; quote text values, leave numbers bare, and the question never arises.
Because IN is an OR of equalities and NOT IN is an AND of inequalities, NULL behaves asymmetrically. col IN (1, NULL) can still be true, since a row where col is 1 gives TRUE OR UNKNOWN, which is TRUE. But col NOT IN (1, NULL) contains col <> NULL, which is UNKNOWN for every row, so the whole AND is UNKNOWN at best and FALSE at worst, never TRUE, and WHERE keeps only TRUE. A NULL in the column itself is dropped by IN and by NOT IN alike, which is why those two row counts rarely add up to the table's total.
CREATE TABLE orders (
id INTEGER,
customer TEXT,
status TEXT
);
INSERT INTO orders VALUES
(1, 'Ada', 'shipped'),
(2, 'Brij', 'pending'),
(3, 'Chen', 'cancelled'),
(4, 'Dara', 'refunded'),
(5, 'Eli', 'shipped');
SELECT id, customer, status
FROM orders
WHERE status IN ('cancelled', 'refunded')
ORDER BY id;IN is shorthand for a chain of ORed equality tests, and that expansion explains both its convenience and its NULL behaviour.
Worked examples
IN next to AND
Shows that an IN list counts as one condition, so combining it with AND needs no extra parentheses.
WITH orders(id, status, total) AS (
VALUES (1, 'shipped', 40), (2, 'pending', 90),
(3, 'cancelled', 10), (4, 'refunded', 120)
)
SELECT id, status, total
FROM orders
WHERE status IN ('cancelled', 'refunded')
AND total > 50
ORDER BY id;Example explained
Line 1status IN ('cancelled', 'refunded') is a single predicate, so AND total > 50 applies to the whole membership test.
Line 2Row 3 is cancelled but its total is 10, so it fails the AND and is filtered out.
Line 3Written as status = 'cancelled' OR status = 'refunded' AND total > 50, AND binds tighter and row 3 comes back as well; the IN form cannot be misread that way.
Line 4The WITH clause just builds a throwaway table so the example runs without CREATE TABLE.
One NULL empties a NOT IN
Compares the row count of a clean NOT IN list against the same list with a NULL added.
WITH orders(id, status) AS (
VALUES (1, 'shipped'), (2, 'pending'), (3, 'cancelled')
)
SELECT
(SELECT count(*) FROM orders WHERE status NOT IN ('shipped')) AS clean_list,
(SELECT count(*) FROM orders WHERE status NOT IN ('shipped', NULL)) AS list_with_null;Example explained
Line 1The first count negates a clean list and finds the pending and cancelled rows.
Line 2status NOT IN ('shipped', NULL) expands to status <> 'shipped' AND status <> NULL.
Line 3status <> NULL is UNKNOWN for every row, so the AND is never TRUE and WHERE keeps nothing.
Line 4The reverse test is not symmetric: status IN ('shipped', NULL) still returns the shipped row, because TRUE OR UNKNOWN is TRUE.
The list is a set, not a plan
Demonstrates that duplicates and unmatched values are harmless and that the list order does not order the result.
WITH product(id, name) AS (
VALUES (1, 'bolt'), (2, 'nut'), (3, 'washer'), (4, 'screw')
)
SELECT id, name
FROM product
WHERE id IN (4, 1, 1, 99);Example explained
Line 1id IN (4, 1, 1, 99) is a membership test, so the repeated 1 adds nothing and cannot duplicate a row.
Line 299 matches no row and raises no error; IN never requires the listed values to exist.
Line 3The rows arrive as 1 then 4, the order they were scanned in, not the list order 4 then 1.
Line 4Add ORDER BY when the order matters, because no engine promises to follow your list.
Important notes
col IN (SELECT ...) is the same predicate with the list computed at runtime, and the NULL trap is worse there because you cannot see the values; NOT EXISTS is the safer negation once subqueries are on the table.
Very long literal lists are also a practical limit: Oracle rejects more than 1000 expressions in a single IN list, and other engines pay for it at planning time, so load the values into a table instead.
Common mistakes
Quoting the whole list as one string, as in status IN ('cancelled, refunded'): that is a one-element list, so it matches nothing and returns zero rows with no error to explain why.
Leaving a NULL in a NOT IN list, often a NULL arriving from a nullable column: the predicate can never be TRUE, the query returns nothing, and the data gets blamed.
Assuming NOT IN returns every row IN did not: rows whose column is NULL are excluded by both, so the two counts do not add up to the table size.
Try it yourself
Change, predict, then run
Create a five-row tickets table with a priority column where one row's priority is NULL, then run priority IN ('high','urgent') and priority NOT IN ('high','urgent') and confirm the counts total four, not five. Rewrite the second query so the NULL row is included.
Open the SQL workspaceCheck your understanding
A table has 10 rows, three of them with a NULL status. WHERE status IN ('a','b') returns 4 rows. How many rows does WHERE status NOT IN ('a','b') return?
- 3 rows
- 6 rows
- 7 rows
- 0 rows
Show answer
NOT IN expands to status <> 'a' AND status <> 'b'; for the three NULL rows both comparisons are UNKNOWN, so those rows satisfy neither query. That leaves 10 - 4 - 3 = 3 rows. The tempting 6 assumes NOT IN is the exact complement of IN, which only holds when the column has no NULLs; 0 would be right only if a NULL were inside the list rather than in the column.