Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

IN to Filter Data

Author: Sophia

what's covered
This tutorial explores using the IN clause within a SELECT statement to filter specific values in two parts:
  1. Getting Started
  2. Using NOT IN

1. Getting Started

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';

OR Operator Query Example


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');

IN Operator Query Example

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.


2. Using NOT IN

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:


NOT IN Query Result Example

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.


NOT IN Query Result Example


Video Transcript

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The IN operator is used to check if a value is within a list of values that are specified.

Source: Authored by Vincent Tran