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 | +---------+-----------+------------+