When should I use IN, and how is it different from writing a chain of OR equalities?
Use IN when you want the value to be one of a list of values, and it is better than a chain of OR equalities for two reasons.
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
title VARCHAR(160) NOT NULL,
status VARCHAR(20) NOT NULL,
priority INTEGER NOT NULL
);
INSERT INTO tickets (id, title, status, priority) VALUES
(1, 'Login is broken', 'open', 1),
(2, 'Slow export', 'open', 3),
(3, 'Typo on the pricing page', 'closed', 1);
-- The chain of OR equalities.
SELECT id FROM tickets WHERE status = 'open' OR status = 'closed' OR status = 'pending';
-- The same thing with IN.
SELECT id FROM tickets WHERE status IN ('open', 'closed', 'pending');
So how is it different? Three ways.
The first one is speed. When you write a chain of equalities, the engine has to test the value against each one in turn. When you write IN with a list, the engine can sort the list and look the value up, so the cost does not grow as fast as the list gets longer. With a long list, and especially with a list of numbers, the difference is real.
The second one is readability. The list is written down once, in one place, and the intention is obvious.
The third one is that IN works with things that are not simple values. You can write IN with a subquery, and then it means "is one of the values that this other query produces".
-- The customers who have an order in the list of big spenders.
SELECT id, name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 500);
There is one trap with IN that has caught everybody at least once. The expression NOT IN behaves very badly when the list on the right contains a missing value. If the list has a missing value in it, then NOT IN returns no rows at all, because every comparison with the missing value gives an unknown answer and an unknown answer is not true. That is why NOT EXISTS is the safer way to ask for something that is not in a list.
The next question in this topic looks at BETWEEN and at the alternatives to LIKE.