PostgreSQL – IN operator

The PostgreSQL IN operator is used with the WHERE clause to check against a list of values.

The syntax for using 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:

Syntax: value IN (value1, value2, …)

The syntax for using IN operator to return the matching values in contrast with the SELECT statement is as below:

Syntax: value IN (SELECT value FROM tbl_name);

For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link.

Now, let’s look into a few examples.
Example 1:
Here we will make a query for the rental information of customer id 10 and 12, using the WHERE clause and IN operator.

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

Output:

Example 2:
Here we will make a query for a list of customer id of customers that has rental’s return date on 2005-05-27.

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: