IN

The IN operator in PostgreSQL allows you to check whether a value matches any value in a list of values. It produces cleaner, more readable SQL than chaining multiple OR conditions, and PostgreSQL can execute it more efficiently.

IN operator syntax

Basic syntax:

value IN (value1, value2, ...)

The operator returns true if value equals any item in the list. It is equivalent to:

value = value1 OR value = value2 OR ...

To negate, use NOT IN:

value NOT IN (value1, value2, ...)

This is equivalent to:

value <> value1 AND value <> value2 AND ...

PostgreSQL IN operator examples

Using IN with a list of numbers — retrieve films with specific IDs:

SELECT film_id, title
FROM film
WHERE film_id IN (1, 2, 3);

film_id |      title
---------+------------------
      1 | Academy Dinosaur
      2 | Ace Goldfinger
      3 | Adaptation Holes
(3 rows)

Using IN with a list of strings — find actors with specific last names:

SELECT first_name, last_name
FROM actor
WHERE last_name IN ('Allen', 'Chase', 'Davis')
ORDER BY last_name;

first_name | last_name
------------+-----------
Cuba       | Allen
Kim        | Allen
Meryl      | Allen
Ed         | Chase
Jon        | Chase
Jennifer   | Davis
Susan      | Davis
(7 rows)

Using IN with dates — the payment_date column is a timestamp, so cast it to date first:

SELECT payment_id, amount, payment_date
FROM payment
WHERE payment_date::date IN ('2007-02-15', '2007-02-16')
ORDER BY payment_date;

Using NOT IN — retrieve films excluding specific IDs:

SELECT film_id, title
FROM film
WHERE film_id NOT IN (1, 2, 3)
ORDER BY film_id;

film_id |       title
---------+--------------------
      4 | Affair Prejudice
      5 | African Egg
      6 | Agent Truman
...

Practical tips for using IN

• Avoid NOT IN with NULLs: if the list contains any NULL, NOT IN returns NULL (not true) for all rows, effectively excluding every row. Use NOT EXISTS or filter out NULLs from the list when nullability is possible.
• IN with subqueries: IN also accepts a subquery — WHERE customer_id IN (SELECT customer_id FROM vip_customers). For large subquery results, EXISTS or a JOIN can be more efficient.
• Large lists: PostgreSQL handles IN lists of hundreds of values efficiently using hash lookups. For very large dynamic sets, consider a temporary table joined with INNER JOIN instead.
• Index usage: an index on the filtered column is used by IN just as it would be for equality conditions, as long as the list is reasonably sized.

Reference: PostgreSQL documentation — Row and Array Comparisons.