PostgreSQL – IN operator

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

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

Explanation:

The subquery selects ‘customer_id' values from the ‘rental' table where the ‘return_date' is ‘2005-05-27‘. The main query then uses the IN 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 EXISTS or JOIN operations 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

FAQs

How to use the in operator in PostgreSQL?

In PostgreSQL, the IN operator is used to filter records by matching a value against a list of possible values, making it useful for conditions in SELECT, UPDATE, and DELETE queries (e.g., SELECT * FROM table WHERE column IN (value1, value2, value3);).

What is the ~~ operator in PostgreSQL?

The ~~ operator in PostgreSQL performs a pattern match similar to LIKE. It’s often used with % and _ wildcards to match strings (e.g., column ~~ '%pattern%' is equivalent to column LIKE '%pattern%')

What are operators in PostgreSQL?

Operators in PostgreSQL are special symbols or keywords used to perform operations on data, such as arithmetic (+, -), comparison (=, <>), and logical operations (AND, OR), enabling flexible data manipulation and querying

R

RajuKumar19

Follow

Improve

Article Tags :

• Databases
• PostgreSQL
• Python
• postgreSQL-operators

Practice Tags :

• python