IN
An operator that is true when a value equals any value in a list or in the rows a subquery returns.
In more detail
WHERE status IN ('paid', 'pending') is shorter and clearer than a chain of ORs. NOT IN has a trap: if the list contains a NULL, the comparison can never be true, so the query returns no rows at all. Use NOT EXISTS or an anti join instead.
Example
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'pending')Related terms
- NULL — SQL's marker for a missing or unknown value, which is not zero, not empty text and not equal to anything, even another NULL.
- subquery — A query written in brackets inside another query, whose result the outer query uses.
- EXISTS — An operator that is true when a subquery returns at least one row, whatever the row contains.
- anti join — A query that returns the rows of one table that have no matching row in another, such as products nobody has ordered.
Taught in
- Filtering With The WHERE Clause — PostgreSQL, Indexing & Relational Data Modeling
- Subqueries — PostgreSQL, Indexing & Relational Data Modeling