PostgreSQL IN operator (original) (raw)

Last Updated : 12 Jul, 2025

The **IN operator in **PostgreSQL is a powerful and efficient tool used to filter records based on a predefined set of values. When used with the **WHERE clause, it simplifies **SQL queries and enhances readability, making it a key component of **SQL query optimization for **data retrieval and **database manipulation.

In this article, we’ll explain how the **IN operator works, how to use it with **subqueries, and provide practical examples to help you understand its functionality. Additionally, we will discuss **performance considerations and **best practices for using the **IN operator effectively in **PostgreSQL.

PostgreSQL - IN operator

The IN operator in **PostgreSQL is used to test whether a value matches any value in a **list of values or a **subquery. It simplifies **complex queries, replacing multiple **OR conditions with a single **IN clause. This makes our **SQL queries more **concise, **readable, and easier to maintain

Syntax of **IN Operator in PostgreSQL

**1. Checking Against a List of Values:

The syntax for using the IN operator with the **WHERE clause to check against a list of values which returns a **boolean value depending upon the match is as below:

value IN (value1, value2, ...)

**2. Using a Subquery:

The syntax for using the IN operator to return the matching values in contrast with the **SELECT statement is as follows. We can also use the **IN operator with a **subquery to filter records based on results from another query. The syntax for this is:

value IN (SELECT value FROM tbl_name);

Examples of PostgreSQL IN operator

Let us take a look at some of the examples of **IN operator in PostgreSQL to better understand the concept. For the understanding this article we will be using the **sample DVD rental database, which is explained here and can be downloaded by clicking on this link.

Example 1: Filtering Rentals by Customer IDs

In this example, we’ll retrieve rental records for **customer_id 10 and **customer_id 12 from the **rental table using the **IN operator. This query will return rental details for these specific customers, sorted by **return_date.

**Query:

SELECT
customer_id,
rental_id,
return_date
FROM
rental
WHERE
customer_id IN (10, 12)
ORDER BY
return_date DESC;

**Output

Filtering Rentals by Customer IDs

**Explanation:

The IN operator filters the rental records to include only those where the 'customer_id'is either 10 or 12, and orders the results by 'return_date' in descending order.

Example 2: Using a Subquery with the IN Operator

Let’s now use the **IN operator with a subquery to find customers who had rentals returned on a specific date, **2005-05-27. In this case, we will first select the **customer_ids from the **rental table where the **return_date matches, and then use these values to filter the customers in the **customer table.

**Query:

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

**Output

Using a Subquery with the IN Operator

**Explanation:

The subquery selects 'customer_id' values from the 'rental' table where the 'return_date' is '2005-05-27'. The main query then uses theIN** operator to filter customers based on these 'customer_id' values.

Important Points About IN Operator in PostgreSQL

The PostgreSQL IN operator is used to filter records based on a specified list of values.
The IN operator allows us to compare a column value against multiple values without using multiple OR conditions.
If the list of values or subquery contains NULL, the IN operator will not match any rows unless the column value being compared is also NULL.
The IN operator is case-sensitive. To perform a case-insensitive comparison, use the **ILIKE operator with pattern matching.
For very large datasets, consider using **EXISTSor **JOINoperations as an alternative to the IN operator.

Conclusion

The **PostgreSQL IN operator is a **flexible and **powerful tool for **data filtering. Whether we’re matching values against a list or using **subqueries, the IN operator simplifies our SQL queries, enhances **readability, and reduces the complexity of our WHERE clauses. By following **best practices for performance and understanding how to effectively use **IN, we can optimize our queries and ensure efficient **data retrieval