SQL IN Tutorial With Examples

In the last tutorial, we learned how to filter on a range of conditions using the keyword BETWEEN. In this tutorial, we'll talk about another way to do this for categorical data using the SQL operator IN.

What SQL IN Does

The IN operator allows us to specify multiple values in a single line's WHERE clause, instead of the more tedious approach of having to use multiple OR conditions to filter for multiple values.

Here's the IN syntax:

SELECT ...
FROM ...
WHERE column IN (....);

This command checks if a particular column value matches anything in the list of values you specify.

To see how useful IN is, imagine you were a Data Analyst examining medicines sold at CVS Pharmacy. Here's a small sample of the data you have access too:

Suppose you wanted to focus your analysis just on the drugs sold by Biogen, Bayer, and Eli Lilly.

You could use the IN operator to check if the manufacturer name is IN your list of values:

SELECT drug, manufacturer, units_sold 
FROM pharmacy_sales
WHERE manufacturer IN ('Biogen', 'Bayer', 'Eli Lilly');

This is much more concise than writing out:

SELECT drug, manufacturer, units_sold 
FROM pharmacy_sales
WHERE manufacturer = 'Biogen'
OR manufacturer = 'Bayer'
OR manufacturer = 'Eli Lilly';

SQL IN Practice Exercise

Try practicing IN on this real-world pharmacy analytics dataset. Write a SQL query to find all medicines which:

• were manufactured by either Roche, Bayer, or AstraZeneca
• and did not sell between 55,000 and 550,000 units

Output the manufacturer name, drug name, and the # of units sold. for all the medicines which match that criteria. The output should look something like this:

Practice Problem
Find all medicines made by Roche, Bayer, or AstraZeneca which DID NOT sell between 55k and 550k units.
Try it out

Hint: Besides the IN operator, don't forget about the BETWEEN operator either!

What's Next: LIKE

Remember how tiresome it was to list OR multiple times, but then IN saved the day, allowing us to easily filter based on if multiple values?

What if you could filter based on if your value matched some arbitrary PATTERN, instead of just a fixed list of values?

For example, what if we wanted to find all the 5-letter words in the dictionary that end in "er"?

It's impractical to use IN here because... we can't specify the list of matching words.

SELECT word
FROM dictionary
WHERE word IN ('enter', 'fader', 'taper', 'foyer', 'gamer', 'giver'.......)

To solve this problem, head over to the next lesson on LIKE which allows us to filter strings based on patterns!

Next SQL Tutorial: LIKE Wildcards