The IN operator allows you to search using a variety of values for a single column. This can simplify some queries by avoiding having to write a separate condition using the OR operator. Instead, you can have a list of values enclosed in parentheses that are separated by commas within the IN operator.
For example, 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:
SELECT *
FROM customer
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 as follows:
SELECT *
FROM customer
WHERE country IN ('Brazil', 'Belgium', 'Norway', 'Austria');
To add other countries, we don’t have to add another comparison; we simply 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 set to 1, 2, 3, or 4, then we would use the following statement:
SELECT *
FROM customer
WHERE support_rep_id IN (1,2,3,4);
It is important to note that the order of the values does not matter in this case. The results would be the same if we ran the following statement:
SELECT *
FROM customer
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 NOT 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:
SELECT *
FROM customer
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), but excluding the four aforementioned countries:
This can be useful with the IN operator if the list of values to compare to is smaller than the opposite set of values. For example, if we wanted to get the tracks that had the genre_id with the values of 1-20, we could use the IN operator like this:
SELECT *
FROM track
WHERE genre_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);
But since we know there are 25 genre_id values from the genre table, we could simplify the query by using the NOT IN option instead:
SELECT *
FROM track
WHERE genre_id NOT IN (21,22,23,24,25);
Both, in this case, would return the same result set of 3,307 rows.
[MUSIC PLAYING] In this video we're going to be covering the utilizing the IN operator. The IN operator allows us to find data that fits within a set of values that we're passing in. So as an example here as part of this option, if we don't utilize the end and we're looking for a series of customers with the id of, 1, 2, 8, 9, we have to enter into this four times.
So if we have like 10 things to look for, we have to enter in 10 times. That does get a lot more complex. What we can do is simplify this by removing all these different options, we enter in, IN and then we enter list all them separated by a comma. Close that. And then if we run that, we should see the same data set is being returned, but it really simplifies it we can make those edits.
The other option as well is that we can also change this to look at strings in the same way. The only thing to make note of again is that we do have to utilize single quotes around the strings. So for example, if we said country and looking for those in 'USA,' 'Canada,' 'Belgium,' 'Denmark.' This will return all those countries that have those values in this case here for those customers.
[MUSIC PLAYING]
Source: Authored by Vincent Tran