SQL IN Tutorial With Examples

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

What SQL IN Does

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

Here's the syntax:

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

To see how useful 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 operator to check if the manufacturer name is your list of values:

This is much more concise than writing out:

SQL IN Practice Exercise

Try practicing 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:

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

What's Next: LIKE

Remember how tiresome it was to list multiple times, but then 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 here because... we can't specify the list of matching words.

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

Next SQL Tutorial: LIKE Wildcards