Summary: in this tutorial, you will learn how to use the PostgreSQL IN operator to check if a value matches any value in a list.
Create a free Postgres Database in 0.3 seconds on Neon. Ship faster with database branching. Handle peaks efficiently with autoscaling.
Sponsored
Introduction to PostgreSQL IN operator
The IN
operator allows you to check whether a value matches any value in a list of values.
Here’s the basic syntax of the IN
operator:
value IN (value1,value2,...)
Code language: SQL (Structured Query Language) (sql)
The IN
operator returns true if the value
is equal to any value in the list such as value1
and value2
.
The list of values can be a list of literal values including numbers and strings.
In addition to literal values, the IN
operator also accepts a list of values returned from a query. You’ll learn more about how to use the IN
operator with a query in the subquery tutorial.
Functionally, the IN
operator is equivalent to combining multiple boolean expressions with the OR operators:
value = value1 OR value = value2 OR ...
PostgreSQL IN operator examples
We’ll use the film
table from the sample database:

1) Using the PostgreSQL IN operator with a list of numbers
The following example uses the IN
operator to retrieve information about the film with id 1, 2, and 3:
SELECT
film_id,
title
FROM
film
WHERE
film_id in (1, 2, 3);
Code language: SQL (Structured Query Language) (sql)
Output:
film_id | title
---------+------------------
1 | Academy Dinosaur
2 | Ace Goldfinger
3 | Adaptation Holes
(3 rows)
The following statement uses the equal (=
) and OR
operators instead of the IN
operator, which is equivalent to the query above:
SELECT
film_id,
title
FROM
film
WHERE
film_id = 1
OR film_id = 2
OR film_id = 3;
Code language: SQL (Structured Query Language) (sql)
The query that uses the IN
operator is shorter and more readable than the query that uses equal (=
) and OR
operators.
Additionally, PostgreSQL executes the query with the IN
operator much faster than the same query that uses a list of OR
operators.
2) Using the PostgreSQL IN operator with a list of strings
We’ll use the actor
table from the sample database:

The following example uses the IN
operator to find the actors who have the last name in the list 'Allen'
, 'Chase'
, and 'Davis'
:
SELECT
first_name,
last_name
FROM
actor
WHERE
last_name IN ('Allen', 'Chase', 'Davis')
ORDER BY
last_name;
Code language: JavaScript (javascript)
Output:
first_name | last_name
------------+-----------
Meryl | Allen
Cuba | Allen
Kim | Allen
Jon | Chase
Ed | Chase
Susan | Davis
Jennifer | Davis
Susan | Davis
(8 rows)
3) Using the PostgreSQL IN operator with a list of dates
The following statement uses the IN operator to find payments whose payment dates are in a list of dates: 2007-02-15
and 2007-02-16
:
SELECT
payment_id,
amount,
payment_date
FROM
payment
WHERE
payment_date::date IN ('2007-02-15', '2007-02-16');
Code language: PHP (php)
Output:
payment_id | amount | payment_date
------------+--------+----------------------------
17503 | 7.99 | 2007-02-15 22:25:46.996577
17504 | 1.99 | 2007-02-16 17:23:14.996577
17505 | 7.99 | 2007-02-16 22:41:45.996577
17512 | 4.99 | 2007-02-16 00:10:50.996577
...
In this example, the payment_date
column has the type timestamp
that consists of both date and time parts.
To match the values in the payment_date
column with a list of dates, you need to cast them to date values that have the date part only.
To do that you use the ::
cast operator:
payment_date::date
Code language: CSS (css)
For example, if the timestamp value is 2007-02-15 22:25:46.996577
, the cast operator will convert it to 2007-02-15
.
PostgreSQL NOT IN operator
To negate the IN
operator, you use the NOT IN
operator. Here’s the basic syntax of the NOT IN
operator:
value NOT IN (value1, value2, ...)
Code language: SQL (Structured Query Language) (sql)
The NOT IN
operator returns true
if the value
is not equal to any value in the list such as value1
and value2
; otherwise, the NOT IN
operator returns false
.
The NOT IN
operator is equivalent to a combination of multiple boolean expressions with the AND operators:
value <> value1 AND value <> value2 AND ...
Code language: HTML, XML (xml)
PostgreSQL NOT IN operator example
The following example uses the NOT IN
operator to retrieve films whose id is not 1, 2, or 3:
SELECT
film_id,
title
FROM
film
WHERE
film_id NOT IN (1, 2, 3)
ORDER BY
film_id;
Code language: SQL (Structured Query Language) (sql)
Output:
film_id | title
---------+-----------------------------
4 | Affair Prejudice
5 | African Egg
6 | Agent Truman
7 | Airplane Sierra
8 | Airport Pollock
...
The following query retrieves the same set of data but uses the not-equal (<>
) and AND
operators:
SELECT
film_id,
title
FROM
film
WHERE
film_id <> 1
AND film_id <> 2
AND film_id <> 3
ORDER BY
film_id;
Code language: SQL (Structured Query Language) (sql)
Summary
- Use the
IN
operator to check if a value matches any value in a list of values. - Use the
NOT
operator to negate theIN
operator.