The IN operator allows you to search using a variety of values for a single column. This can simplify some queries to avoid having to write a separate condition using the OR operator. Rather, you would have a list of values enclosed in parentheses that are separated by commas within the IN operator.
If we wanted to find the customers that live in either Brazil, Belgium, Norway, or Austria, using the OR operator that we learned in the prior tutorial, we would do the following:
WHERE country = 'Brazil'
OR country = 'Belgium'
OR country = 'Norway'
OR country = 'Austria';
This can get a bit lengthy and increase the chance of error if we have other conditions as well.
By using the IN operator, the query can be simplified:
WHERE country IN ('Brazil', 'Belgium', 'Norway', 'Austria');
To add other countries, we don’t have to add another comparison. We just add it to the list of values. Notice that we still use single quotes around the strings that we are comparing.
If we wanted the customers that had the support_rep_id being set to 1, 2, 3, or 4, then we would use the following statement:
WHERE support_rep_id IN (1,2,3,4);
It is important to note that the order of the values would not matter in this case. The results would be the same if we ran the following statement:
WHERE support_rep_id IN (4,3,2,1);
Similar to the OR operator, the result set is combined together based on each of the comparisons individually.
The IN operator is especially useful when it is used in conjunction with subqueries which we will discuss in a later tutorial.
You can also use the NOT operator to negate the conditional expression. Since all of the conditional expressions evaluate to true or false, the NOT operator will get the rows that do not match a certain condition. For the IN operator, it would return data that does not fall under that particular criteria. For example, if we wanted to look for customers that are not in Brazil, Belgium, Norway, or Austria, we would need to run the following:
WHERE country NOT IN ('Brazil','Belgium','Norway','Austria');
This would return all of the customers in any country (or not having any country at all):
This can be useful with the IN operator if the list of values to compare to is smaller than the opposite set of values. This typically is used when we have a distinct set of values that can be compared to. For example, if we wanted to get the tracks that had the genre_id with the values of 1-20. Using the IN operator, it would look like this:
WHERE genre_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);
Instead, since we know there are 25 genre_id values from the genre table, we could simplify the query to use the NOT IN option instead:
WHERE genre_id NOT IN (21,22,23,24,25);
Both in this case would return the same result set of 3307 rows.