PostgreSQL IN operator

In PostgreSQL, the IN operator is a Boolean operator that checks whether a list of values ​​contains a specified value. The IN operator returns true if the value list contains the specified value, otherwise it returns false.

PostgreSQL IN operator syntax

To check if a value is within a list of values, use the IN operator:

expr IN (value1, value2, ...)

or

expr IN (subquery)

Explanation:

• The expr can be a column name, value, or other expression (such as function calls, operations, etc.).
• The (value1, value2, ...) is a list of values ​​separated by , and surrounded by parentheses ().
• The value1 is a specific value, such as: 1, 2, 'A', 'B' etc.
• The subquery is a subquery that returns only one column.

The IN operator returns true if the list of values ​​or the result set returned subquery includes expr, otherwise it returns false.

The negation of the IN operator is NOT IN.

IN vs OR

The IN expression can be rewritten using the OR operator.

for example:

val IN (1, 2, 3)

is equivalent to the following expression of OR:

val = 1 OR val = 2 OR val = 3

Similarly, NOT IN expressions can be rewritten using the AND operator.

val NOT IN (1, 2, 3)

id equivalent to the following expression of AND:

val <> 1 AND val <> 2 OR val <> 3

PostgreSQL IN operator Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

In the Sakila sample database, the actor table stores information about all actors.

To query all actors whose last name is ALLEN or DAVIS from the actor table, use the following statement with the IN operator:

SELECT *
FROM actor
WHERE last_name IN ('ALLEN', 'DAVIS');

 actor_id | first_name | last_name |     last_update
----------+------------+-----------+---------------------
        4 | JENNIFER   | DAVIS     | 2006-02-15 04:34:33
      101 | SUSAN      | DAVIS     | 2006-02-15 04:34:33
      110 | SUSAN      | DAVIS     | 2006-02-15 04:34:33
      118 | CUBA       | ALLEN     | 2006-02-15 04:34:33
      145 | KIM        | ALLEN     | 2006-02-15 04:34:33
      194 | MERYL      | ALLEN     | 2006-02-15 04:34:33
(6 rows)

You can rewrite the above statement using the OR operator:

SELECT *
FROM actor
WHERE last_name = 'ALLEN'
  OR last_name = 'DAVIS';

Using subqueries in PostgreSQL IN

To retrieve the number of films that have inventory from the film table, you can use a PostgreSQL IN expression with a subquery:

SELECT COUNT(*)
FROM film
WHERE film_id IN (
    SELECT film_id
    FROM inventory
  );

 count
-------
   958

In general, IN expressions in PostgreSQL with subqueries can be rewritten using EXISTS operator, and EXISTS expressions have better efficiency. The above statement is equivalent to the following statement using EXISTS:

SELECT COUNT(*)
FROM film f
WHERE EXISTS (
    SELECT 1
    FROM inventory i
    WHERE i.film_id = f.film_id
  );

 count
-------
   958

Conclusion

The PostgreSQL IN operator is a boolean operator that checks whether a list of values ​​contains a specified value. The IN operator returns true if the value list contains the specified value, otherwise it returns false.