PostgreSQL Tutorial: IN

August 1, 2023

Summary: in this tutorial, you will learn how to use the PostgreSQL IN operator in the WHERE clause to check if a value matches any value in a list.

Table of Contents

PostgreSQL IN operator syntax

You use IN operator in the WHERE clause to check if a value matches any value in a list of values.

The syntax of the IN operator is as follows:

value IN (value1,value2,...)

The IN operator returns true if the value matches any value in the list i.e., value1 , value2 , …

The list of values can be a list of literal values such as numbers, strings or a result of a SELECT statement like this:

value IN (SELECT column_name FROM table_name);

The query inside the parentheses is called a subquery, which is a query nested inside another query. Note that you will learn more about the subquery in the subsequent tutorial

PostgreSQL IN operator examples

Suppose you want to know the rental information of customer id 1 and 2, you can use the IN operator in the WHERE clause as follows:

SELECT customer_id,
	rental_id,
	return_date
FROM
	rental
WHERE
	customer_id IN (1, 2)
ORDER BY
	return_date DESC;

PostgreSQL IN example

The following query uses the equal (=) and OR operators instead of the IN operator. It is equivalent to the query above:

SELECT
	rental_id,
	customer_id,
	return_date
FROM
	rental
WHERE
	customer_id = 1 OR customer_id = 2
ORDER BY
	return_date DESC;

The query that uses the IN operator is shorter and more readable than the query that uses equal (=) and OR operators. In addition, PostgreSQL executes the query with the IN operator much faster than the same query that uses a list of OR operators.

PostgreSQL NOT IN operator

You can combine the IN operator with the NOT operator to select rows whose values do not match the values in the list.

For example, the following statement finds all rentals with the customer id is not 1 or 2.

SELECT
	customer_id,
	rental_id,
	return_date
FROM
	rental
WHERE
	customer_id NOT IN (1, 2);

PostgreSQL NOT IN example

Similar to the IN operator, you can use the not equal (<>) and AND operators to write the NOT IN operator:

SELECT
	customer_id,
	rental_id,
	return_date
FROM
	rental
WHERE
	customer_id <> 1
AND customer_id <> 2;

This query returns the same output as above query that use the NOT IN operator.

PostgreSQL IN with a subquery

The following query returns a list of customer ids from the rental table with the return date is 2005-05-27:

SELECT customer_id
FROM rental
WHERE CAST (return_date AS DATE) = '2005-05-27'
ORDER BY customer_id;

PostgreSQL IN - ID List

Because this query returns a list of values, you can use it as the input of the IN operator like this:

SELECT
	customer_id,
	first_name,
	last_name
FROM
	customer
WHERE
	customer_id IN (
		SELECT customer_id
		FROM rental
		WHERE CAST (return_date AS DATE) = '2005-05-27'
	)
ORDER BY customer_id;