Find rows that (don’t) contain a value from a list

How to find rows that contain, or don’t contain, a value from a list. The list may be written in the query, or stored in a table.

The trivial way

The ways that any developer can easily guess are the following:

SELECT id, email FROM user
    WHERE id = 1 OR id = 5 OR id = 24 OR id = 99;
SELECT id, email FROM user
    WHERE id != 1 AND id != 5 AND id != 24 AND id != 99;

The SQL way

However, SQL defined a less verbose, more expressive syntax to achieve the same result:

SELECT id, email FROM user WHERE id IN (1, 5, 24, 99);
SELECT id, email FROM user WHERE id NOT IN (1, 5, 24, 99);

JOINs and subqueries

What if the list of values that we want to search must be retrieved with a query? Example: Find the customers that made an order since January 2021.

In this case, see the article Find rows that have a match in another table.