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, ...)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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, ...)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

value = value1 OR value = value2 OR ...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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);Code language: plaintext (plaintext)

Output:

    name     | quantity
-------------+----------
 Pixel 7 Pro |        8
 Xperia 1 IV |        7Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

     name      | price
---------------+--------
 iPhone 14 Pro | 999.99
 Pixel 7 Pro   | 899.99Code language: plaintext (plaintext)

PostgreSQL NOT IN operator

The NOT operator negates the IN operator:

value NOT IN (value1, value2, ...)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 ...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.