SQL IN and NOT IN: Match Against a List of Values

IN checks whether a value is in a list. It is a tidy replacement for several OR conditions on the same column, and it also works with a subquery that produces the list.

The example table#

IN with a list of values#

SELECT name, city
FROM employees
WHERE city IN ('Mumbai', 'Pune')
ORDER BY name;

name  | city
------+-------
Asha  | Mumbai
John  | Mumbai
Meera | Pune

This is exactly the same as WHERE city = 'Mumbai' OR city = 'Pune', but shorter and easier to extend. The list can contain numbers, text, or dates, as long as they match the column's type.

NOT IN#

SELECT name, city
FROM employees
WHERE city NOT IN ('Mumbai', 'Pune')
ORDER BY name;

name  | city
------+----------
Priya | Delhi
Ravi  | Bengaluru

IN with a subquery#

The list can come from another query. Employees who are someone's manager:

SELECT name, department
FROM employees
WHERE id IN (SELECT manager_id FROM employees);

name  | department
------+------------
Meera | Engineering
Priya | Sales

The subquery returns the list (5, 3, NULL, 5, NULL), and IN keeps employees whose id is in it. Duplicates in the list do not matter.

The NOT IN trap#

Now the opposite question: employees who are not anyone's manager. The obvious query:

SELECT name
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

name
----
(0 rows)

Zero rows, even though Asha, Ravi and John clearly manage nobody. The reason is the NULLs in the list. NOT IN (5, 3, NULL) means id <> 5 AND id <> 3 AND id <> NULL. Comparing with NULL is never true, so the whole condition can never be true, and every row is dropped.

The same thing happens with a written list:

SELECT name FROM customers WHERE customer_id NOT IN (1, 2, NULL);

name
----
(0 rows)

IN (without NOT) is safe: a NULL in the list simply never matches, and the other values still work.

IN vs JOIN vs EXISTS#

WHERE id IN (subquery) is a filter: it keeps each outer row at most once and cannot show columns from the subquery. Use a JOIN when you need columns from the other table, and EXISTS for "has related rows" checks.

Long lists#

IN with a handful of values is perfect. If you find yourself pasting thousands of ids into an IN list, load them into a table (or a temporary table) and join instead; very long lists are slow to parse and some databases limit their size.

Common mistakes#

Practice#

SELECT name, category
FROM products
WHERE category IN ('Furniture', 'Stationery')
ORDER BY name;

name       | category
-----------+-----------
Bookshelf  | Furniture
Desk Chair | Furniture
Notebook   | Stationery

Key takeaway#