How can I include or exclude several exact values with an IN filter?

Use IN with a comma-separated list in parentheses. It selects a row when the compared value matches any item in that list. For example, imagine this invented SQLite table:

To keep new and paid orders, write:

SELECT order_id, status
FROM orders
WHERE status IN ('new', 'paid');

From the example rows, the result is (101, new) and (102, paid). SQLite’s IN operator compares the left-hand expression with the listed values. [S1]

Use NOT IN to select rows whose status matches neither listed value:

SELECT order_id, status
FROM orders
WHERE status NOT IN ('new', 'paid');

For the invented rows, the result is (103, shipped) and (104, cancelled). It does not include (105, NULL): in SQLite, when the status is NULL, the NOT IN comparison evaluates to NULL rather than true, so the row does not pass the WHERE condition. The same NULL status is not selected by the IN filter either. [S1]

If you want to exclude new and paid but keep rows with a missing status, make that explicit:

SELECT order_id, status
FROM orders
WHERE status NOT IN ('new', 'paid') OR status IS NULL;

That returns shipped, cancelled, and the NULL-status row in this example.

Check the values that end up in the list, especially if it could contain NULL. In SQLite, a NULL in the right-hand list can make a nonmatching comparison evaluate to NULL. For example, if the list is ('new', 'paid', NULL), a status that matches neither named value may not pass a NOT IN condition. [S1]

The meaning of NULL in a list is separate from whether a row’s status is NULL. Decide what you want those cases to do, and make sure the list contains only the values intended for the filter. The behavior described here is for SQLite; check the documentation for the database you use if you are using a different system.

Use this rule of thumb:

1. Choose IN when the listed values are the ones you want to keep.
2. Choose NOT IN when the listed values are the ones you want to exclude.
3. Decide separately whether rows with a NULL status should be included. For the SQLite exclusion example, add OR status IS NULL when they should be kept.
4. Check that the list does not contain an unintended NULL.

For the invented orders, IN ('new', 'paid') returns orders 101 and 102. NOT IN ('new', 'paid') returns 103 and 104; adding OR status IS NULL also returns 105. [S1]