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 .
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:
|Motrin||Johnson & Johnson||104637|
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:
Try practicing on this real-world pharmacy analytics dataset. Write a SQL query to find all medicines which:
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!
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.
SQL LIKE ❤️