PostgreSQL - IN operator

The IN operator in PostgreSQL is used to check whether a value matches any value in a specified list. It provides a simple way to compare a column against multiple values without using multiple OR conditions.

• Can be used with SELECT, UPDATE and DELETE statements.
• Can also be used with a subquery.

Syntax

SELECT column_1, column_2, ...
FROM table_name
WHERE column_name IN (value_1, value_2, value_3, ...);

Where:

• column_name: The column to compare.
• value_1, value_2, ...: The list of values to match.

Examples

Consider the following books table:

Example: Using the PostgreSQL IN Operator with a List of Numbers

The following query retrieves books whose IDs are 101, 103 and 105.

SELECT book_id,
       book_title
FROM books
WHERE book_id IN (101, 103, 105);

Output:

The following query produces the same result using the = and OR operators.

SELECT book_id,
       book_title
FROM books
WHERE book_id = 101
   OR book_id = 103
   OR book_id = 105;

The query using the IN operator is shorter and easier to read than using multiple OR conditions.

Example: Using the PostgreSQL IN Operator with a List of Strings

The following query retrieves books that belong to the Programming and AI categories.

SELECT book_title,
       category
FROM books
WHERE category IN ('Programming', 'AI')
ORDER BY category;

Output:

The IN operator simplifies comparisons with multiple string values.

Example: Using IN with NOT

The following query retrieves books except those in the Programming and AI categories.

SELECT book_id,
       book_title,
       category
FROM books
WHERE category NOT IN ('Programming', 'AI');

Output: