# IN

### Introduction to PostgreSQL IN operator <a href="#introduction-to-postgresql-in-operator" id="introduction-to-postgresql-in-operator"></a>

The `IN` operator allows you to check whether a value matches any value in a list of values.

Here’s the basic syntax of the `IN` operator

```sql
value IN (value1,value2,...)
```

The `IN` operator returns true if the `value` is equal to any value in the list such as `value1` and `value2`. The list of values can be a list of literal values including numbers and strings.

### Using the PostgreSQL IN operator with a list of numbers <a href="#id-1-using-the-postgresql-in-operator-with-a-list-of-numbers" id="id-1-using-the-postgresql-in-operator-with-a-list-of-numbers"></a>

The following example uses the `IN` operator to retrieve information about the film with id 1, 2, and 3

```sql
SELECT film_id, title
FROM film
WHERE film_id in (1, 2, 3);
```

<figure><img src="https://2781136461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FK4KaCc7tzh0tRXwFV2A5%2Fuploads%2FJY10z3dJEVkurAxw3CBt%2Fimage.png?alt=media&#x26;token=d4220d63-605f-40cc-a8a3-a0c384c3f026" alt=""><figcaption></figcaption></figure>

The following statement uses the equal (`=`) and `OR` operators instead of the `IN` operator, which is equivalent to the query above.

```sql
SELECT film_id, title
FROM film
WHERE 
  film_id = 1
  OR film_id = 2
  OR film_id = 3;
```

The query that uses the `IN` operator is shorter and more readable than the query that uses equal (`=`) and `OR` operators. Additionally, PostgreSQL executes the query with the `IN` operator much faster than the same query that uses a list of `OR` operators.

### Using the PostgreSQL IN operator with a list of strings <a href="#id-2-using-the-postgresql-in-operator-with-a-list-of-strings" id="id-2-using-the-postgresql-in-operator-with-a-list-of-strings"></a>

The following example uses the `IN` operator to find the actors who have the last name in the list `'Allen'`, `'Chase'`, and `'Davis'`

```sql
SELECT first_name, last_name
FROM actor
WHERE last_name IN ('Allen', 'Chase', 'Davis')
ORDER BY last_name;
```

<figure><img src="https://2781136461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FK4KaCc7tzh0tRXwFV2A5%2Fuploads%2F5nRZWsQpsGMaqRzBCDts%2Fimage.png?alt=media&#x26;token=68fbf8d7-023f-4d92-a263-9e5894ba2e82" alt=""><figcaption></figcaption></figure>

### Using the PostgreSQL IN operator with a list of dates <a href="#id-3-using-the-postgresql-in-operator-with-a-list-of-dates" id="id-3-using-the-postgresql-in-operator-with-a-list-of-dates"></a>

The following statement uses the IN operator to find payments whose payment dates are in a list of dates: `2007-02-15` and `2007-02-16`

<pre class="language-sql"><code class="lang-sql"><strong>SELECT payment_id, amount, payment_date
</strong>FROM payment
WHERE payment_date::date IN ('2007-02-15', '2007-02-16');
</code></pre>

<figure><img src="https://2781136461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FK4KaCc7tzh0tRXwFV2A5%2Fuploads%2FkVzmNuvknwT2HlS2rjqA%2Fimage.png?alt=media&#x26;token=258dfc6d-a3e7-49dd-81aa-58f7f4a8ea02" alt=""><figcaption></figcaption></figure>

### PostgreSQL NOT IN operator <a href="#postgresql-not-in-operator" id="postgresql-not-in-operator"></a>

To negate the `IN` operator, you use the `NOT IN` operator. Here’s the basic syntax of the `NOT IN` operator

```sql
value NOT IN (value1, value2, ...)
```

The `NOT IN` operator returns `true` if the `value` is not equal to any value in the list such as `value1` and `value2`; otherwise, the `NOT IN` operator returns `false`.

The `NOT IN` operator is equivalent to a combination of multiple boolean expressions with the [AND operators](https://courses.parottasalna.com/database-engineering/filtering-data/and-operator)

```sql
value <> value1 AND value <> value2 AND ...
```

The following example uses the `NOT IN` operator to retrieve films whose id is not 1, 2, or 3,

```sql
SELECT
  film_id,
  title
FROM
  film
WHERE
  film_id NOT IN (1, 2, 3)
ORDER BY
  film_id;
```

Output:&#x20;

```
film_id |            title
---------+-----------------------------
       4 | Affair Prejudice
       5 | African Egg
       6 | Agent Truman
       7 | Airplane Sierra
       8 | Airport Pollock
...
```

The following query retrieves the same set of data but uses the not-equal (`<>`) and `AND` operators

```sql
SELECT
  film_id,
  title
FROM
  film
WHERE
  film_id <> 1
  AND film_id <> 2
  AND film_id <> 3
ORDER BY
  film_id;
```