PostgreSQL IN: Checking If a Value Matches any Value in a List

Summary: in this tutorial, you’ll learn how to use the PostgreSQL IN operator in the WHERE clause to filter rows that are in a list of values.

PostgreSQL IN operator #

The IN operator returns true if a value is in a list of values.

Here’s the syntax of the IN operator:

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

In this syntax:

• First, specify the value you want to check on the left side of the IN operator.
• Second, list the values you want to verify to see if the value equals any of them.

The value can be integers, strings, dates, etc.

Typically, you’ll use the IN in the WHERE clause of the SELECT statement to filter rows of a table:

WHERE value IN (value1, value2, ...)Copy

The IN operator is equivalent to the following comparison with the OR operator:

value = value1 OR value = value2 OR ...Copy

However, the IN operator is more concise and readable than a long list of comparison expressions.

PostgreSQL IN operator example #

Suppose we have the following inventories table:

CREATE TABLE inventories (
  name VARCHAR(255),
  brand VARCHAR(50),
  quantity INT,
  price DECIMAL(19, 2)
);

INSERT INTO
  inventories (name, brand, quantity, price)
VALUES
  ('iPhone 14 Pro', 'Apple', 10, 999.99),
  ('Galaxy S23 Ultra', 'Samsung', 15, 1199.99),
  ('Pixel 7 Pro', 'Google', 8, 899.99),
  ('Xperia 1 IV', 'Sony', 7, 1299.99);Copy

The following statement uses the IN operator to retrieve the products whose quantity in the inventory is either 7 or 8:

SELECT
  name,
  quantity
FROM
  inventories
WHERE
  quantity IN (7, 8);Copy

Try it

Output:

    name     | quantity
-------------+----------
 Pixel 7 Pro |        8
 Xperia 1 IV |        7Copy

The output returns two products Pixel 7 Pro and Xperia 1 IV, with quantities of 8 and 7.

The following example uses the IN operator to retrieve products with the names in a list of predefined names:

SELECT
  name,
  price
FROM
  inventories
WHERE
  name IN ('iPhone 14 Pro', 'Pixel 7 Pro');Copy

Try It

Output:

     name      | price
---------------+--------
 iPhone 14 Pro | 999.99
 Pixel 7 Pro   | 899.99Copy

PostgreSQL NOT IN operator #

The NOT operator negates the IN operator:

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

The NOT IN operator returns true if a value is not in a list of values (value1, value2, …).

The NOT IN operator is equivalent to the following expression:

value != value1 AND value != value2 AND ...Copy

The following example uses the NOT INoperator to retrieve the products in the inventories with the names not in the list:

SELECT
  name,
  price
FROM
  inventories
WHERE
  name NOT IN ('iPhone 14 Pro', 'Pixel 7 Pro');Copy

Try it

Summary #

• Use the IN operator to check if a value equals any value in a list.
• Use the NOT IN operator to determine if a value is not equal to any values in a list.

Quiz #