Online College Courses for Credit

+
IN to Filter Data

IN to Filter Data

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a SELECT statement that uses the IN operator to limit the result set based on a specific set of values in a column.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

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. 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 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:

SELECT *
FROM customer
WHERE country = 'Brazil'
OR country = 'Belgium'
OR country = 'Norway'
OR country = 'Austria';

File:11231-1180-1.png


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:

SELECT *
FROM customer
WHERE country IN ('Brazil', 'Belgium', 'Norway', 'Austria');

File:11232-1180-2.png

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:

SELECT *
FROM customer
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:

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. Running A Query

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:

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):


File:11233-1180-3.png

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:

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

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:

SELECT *
FROM track
WHERE genre_id NOT IN (21,22,23,24,25);

Both in this case would return the same result set of 3307 rows.


File:11234-1180-4.png


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.