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.
PostgreSQL IN operator syntax
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,...)
IN operator returns true if the
value matches any value in the list i.e.,
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;
The following query uses the equal (
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 (
OR operators. In addition, PostgreSQL executes the query with the
IN operator much faster than the same query that uses a list of
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);
Similar to the
IN operator, you can use the not equal (
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
SELECT customer_id FROM rental WHERE CAST (return_date AS DATE) = '2005-05-27' ORDER BY customer_id;
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;
For more information on the subquery, check it out the subquery tutorial.
In this tutorial, you have learned how to use the PostgreSQL
IN operator to check if a value matches any value in a list of values.