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: