Introduction to PostgreSQL IN operator

TheIN operator allows you to check whether a value matches any value in a list of values.

Here’s the basic syntax of theIN operator:

value IN (value1,value2,...)

TheIN operator returns true if thevalue is equal to any value in the list such asvalue1 andvalue2.

The list of values can be a list of literal values including numbers and strings.

In addition to literal values, theIN operator also accepts a list of values returned from a query. You’ll learn more about how to use theIN operator with a query in thesubquery tutorial.

Functionally, theIN operator is equivalent to combining multiple boolean expressions with theOR operators:

value = value1OR value = value2OR ...

PostgreSQL IN operator examples

We’ll use thefilm table from thesample database:

1) Using the PostgreSQL IN operator with a list of numbers

The following example uses theIN operator to retrieve information about the film with id 1, 2, and 3:

SELECT  film_id,  titleFROM  filmWHERE  film_id in (1,2, 3);

Output:

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

The following statement uses the equal (=) andOR operators instead of theIN operator, which is equivalent to the query above:

SELECT  film_id,  titleFROM  filmWHERE  film_id = 1  OR film_id = 2  OR film_id = 3;

The query that uses theIN operator is shorter and more readable than the query that uses equal (=) andOR operators.

Additionally, PostgreSQL executes the query with theIN operator much faster than the same query that uses a list ofOR operators.

2) Using the PostgreSQL IN operator with a list of strings

We’ll use theactor table from thesample database:

The following example uses theIN operator to find the actors who have the last name in the list'Allen','Chase', and'Davis':

SELECT  first_name,  last_nameFROM  actorWHERE  last_nameIN ('Allen','Chase','Davis')ORDER BY  last_name;

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

We’ll use thepayment table from thesample database:

The following statement uses the IN operator to find payments whose payment dates are in a list of dates:2007-02-15 and2007-02-16:

SELECT  payment_id,  amount,  payment_dateFROM  paymentWHERE  payment_date::date IN ('2007-02-15','2007-02-16');

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, thepayment_date column has the typetimestamp that consists of both date and time parts.

To match the values in thepayment_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

For example, if the timestamp value is2007-02-15 22:25:46.996577, the cast operator will convert it to2007-02-15.

PostgreSQL NOT IN operator

To negate theIN operator, you use theNOT IN operator. Here’s the basic syntax of theNOT IN operator:

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

TheNOT IN operator returnstrue if thevalue is not equal to any value in the list such asvalue1 andvalue2; otherwise, theNOT IN operator returnsfalse.

TheNOT IN operator is equivalent to a combination of multiple boolean expressions with theAND operators:

value <> value1AND value <> value2AND ...

PostgreSQL NOT IN operator example

The following example uses theNOT IN operator to retrieve films whose id is not 1, 2, or 3:

SELECT  film_id,  titleFROM  filmWHERE  film_id NOT IN (1,2, 3)ORDER BY  film_id;

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 (<>) andAND operators:

SELECT  film_id,  titleFROM  filmWHERE  film_id <> 1  AND film_id <> 2  AND film_id <> 3ORDER BY  film_id;

Summary

• Use theIN operator to check if a value matches any value in a list of values.
• Use theNOT operator to negate theIN operator.

Last updated onJanuary 17, 2024

On this page

• Introduction to PostgreSQL IN operator
• PostgreSQL IN operator examples
• PostgreSQL NOT IN operator
• Summary