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.