In SQL, the IN operator allows you to filter your query results based a list of values.

You can also use it to match any value returned by a subquery (a subquery is a query that’s nested inside another query).

Source Tables

The following tables are used for the examples on this page.

SELECT * FROM PetTypes;
SELECT * FROM Pets;

Result:

+-------------+-----------+
| PetTypeId   | PetType   |
|-------------+-----------|
| 1           | Bird      |
| 2           | Cat       |
| 3           | Dog       |
| 4           | Rabbit    |
+-------------+-----------+
(4 rows affected)
+---------+-------------+-----------+-----------+------------+
| PetId   | PetTypeId   | OwnerId   | PetName   | DOB        |
|---------+-------------+-----------+-----------+------------|
| 1       | 2           | 3         | Fluffy    | 2020-11-20 |
| 2       | 3           | 3         | Fetch     | 2019-08-16 |
| 3       | 2           | 2         | Scratch   | 2018-10-01 |
| 4       | 3           | 3         | Wag       | 2020-03-15 |
| 5       | 1           | 1         | Tweet     | 2020-11-28 |
| 6       | 3           | 4         | Fluffy    | 2020-09-17 |
| 7       | 3           | 2         | Bark      | NULL       |
| 8       | 2           | 4         | Meow      | NULL       |
+---------+-------------+-----------+-----------+------------+
(8 rows affected)

Example

Here’s an example to demonstrate the IN operator.

SELECT 
    PetId, 
    PetName, 
    DOB 
FROM Pets
WHERE PetName IN ('Fluffy', 'Bark', 'Wag');

Result:

+---------+-----------+------------+
| PetId   | PetName   | DOB        |
|---------+-----------+------------|
| 1       | Fluffy    | 2020-11-20 |
| 4       | Wag       | 2020-03-15 |
| 6       | Fluffy    | 2020-09-17 |
| 7       | Bark      | NULL       |
+---------+-----------+------------+

We could achieve the same result by using two OR operators:

SELECT 
    PetId, 
    PetName, 
    DOB 
FROM Pets
WHERE PetName = 'Fluffy' 
    OR PetName = 'Bark' 
    OR PetName = 'Wag';

However, the IN operator is a more concise way of doing it. The IN operator is especially beneficial when you have a long list of values for which to compare against.

The IN operator almost always executes quicker than multiple OR operators, especially on larger data sets.

Numeric Values

The values aren’t limited to just strings. For example, you can use IN on a list of numeric values.

SELECT 
    PetId, 
    PetName, 
    DOB 
FROM Pets
WHERE PetId IN (1, 3, 5);

Result:

+---------+-----------+------------+
| PetId   | PetName   | DOB        |
|---------+-----------+------------|
| 1       | Fluffy    | 2020-11-20 |
| 3       | Scratch   | 2018-10-01 |
| 5       | Tweet     | 2020-11-28 |
+---------+-----------+------------+

Dates

Here’s an example that uses a list of dates.

SELECT 
    PetId, 
    PetName, 
    DOB 
FROM Pets
WHERE DOB IN (
    '2020-11-20', 
    '2018-10-01', 
    '2015-10-01'
    );

Result:

+---------+-----------+------------+
| PetId   | PetName   | DOB        |
|---------+-----------+------------|
| 1       | Fluffy    | 2020-11-20 |
| 3       | Scratch   | 2018-10-01 |
+---------+-----------+------------+

Using IN with Subqueries

Another benefit of the IN operator, is that it can contain another SELECT list. This is known as a subquery. A subquery is a query that is nested inside another query (or even another subquery).

Here’s an example.

SELECT 
    PetTypeId,
    PetType
FROM PetTypes
WHERE PetTypeId IN ( SELECT PetTypeId FROM Pets );

Result:

+-------------+-----------+
| PetTypeId   | PetType   |
|-------------+-----------|
| 1           | Bird      |
| 2           | Cat       |
| 3           | Dog       |
+-------------+-----------+

This example shows us how many pet types are in our pet hotel.

Using NOT IN

We could add the NOT operator to flip this around and see how many pet types are not in our pet hotel.

SELECT 
    PetTypeId,
    PetType
FROM PetTypes
WHERE PetTypeId NOT IN ( SELECT PetTypeId FROM Pets );

Result:

+-------------+-----------+
| PetTypeId   | PetType   |
|-------------+-----------|
| 4           | Rabbit    |
+-------------+-----------+

In this case, our database contains a pet type of Rabbit, but we don’t currently have any rabbits as pets.

Expressions

The values provided are a list of expressions. Therefore, you can do stuff like this:

SELECT 
    PetId, 
    PetName, 
    DOB 
FROM Pets
WHERE PetId IN (1, 3, 2 + 3);

Result:

+---------+-----------+------------+
| PetId   | PetName   | DOB        |
|---------+-----------+------------|
| 1       | Fluffy    | 2020-11-20 |
| 3       | Scratch   | 2018-10-01 |
| 5       | Tweet     | 2020-11-28 |
+---------+-----------+------------+