4. SQL for Data Analysis: Master Advanced Filtering with IN, BETWEEN, and LIKE

In our previous lesson, we learned how to use the basic WHERE clause to narrow down our results. However, real-world business questions are often more complex than a simple “equals to” or “greater than” check. As you advance in your journey of SQL for data analysis, you will need tools to handle ranges of dates, lists of categories, and partial text matches.

Today, we are mastering three powerful operators: IN, BETWEEN, and LIKE. These are the “Swiss Army Knives” of SQL for data analysis, allowing you to slice through large datasets with surgical precision.

The IN Operator: Filtering by a List

Imagine the marketing team at Mastery Retail wants a list of all customers living in ‘London’, ‘Manchester’, or ‘Liverpool’. Using what we learned in Lesson 3, you might write:

WHERE city = 'London' OR city = 'Manchester' OR city = 'Liverpool'

This works, but it’s tedious and hard to read. In SQL for data analysis, we use the IN operator to simplify this into a clean list.

The Syntax

SELECT first_name, last_name, city
FROM customers
WHERE city IN ('London', 'Manchester', 'Liverpool');

The IN operator is highly efficient because it allows you to check a column against multiple values at once. It is also much easier to maintain—if the team adds ‘Birmingham’ to the list, you just add one word to the parentheses.

The BETWEEN Operator: Filtering Ranges

Data analysts frequently deal with timeframes and price brackets. Instead of writing two separate conditions (e.g., WHERE price >= 10 AND price <= 50), you can use BETWEEN.

The Syntax

SELECT product_name, retail_price
FROM products
WHERE retail_price BETWEEN 10 AND 50;

Important Note for Analysts: The BETWEEN operator is inclusive. This means it includes both 10 and 50 in the results. This operator is particularly powerful for SQL for data analysis when filtering sales data between two specific dates.

The LIKE Operator: Pattern Matching

Sometimes, you don’t know the exact value you are looking for. Perhaps you are searching for all customers with an ‘@gmail.com’ address, or you need to find all products with the word “Pro” in the name.

The LIKE operator, combined with the percent sign (%) wildcard, allows for flexible text searching in SQL for data analysis.

Common Wildcard Patterns:

'A%': Finds any value that starts with “A”.
'%son': Finds any value that ends with “son” (e.g., Johnson, Wilson).
'%Data%': Finds any value that has “Data” anywhere in the string.

The Syntax

SELECT first_name, email
FROM customers
WHERE email LIKE '%@gmail.com';

Data Cleaning with Advanced Filtering

These advanced operators are not just for retrieval; they are essential for the data cleaning process we’ve been discussing.

Finding Outliers: You can use NOT BETWEEN to find data points that fall outside an expected range (e.g., temperatures above 100 or below -50).
Validating Formats: You can use LIKE to find phone numbers that are missing the correct area code or IDs that don’t follow the required naming convention.
Standardizing Lists: Use IN to identify rows that use “old” category names so you can update them to the “new” ones.

Using these techniques in SQL for data analysis ensures that your final reports are based on clean, trustworthy data.

Practice Exercise: Test Your Knowledge

The Mastery Retail products table has the columns: product_name, category, and retail_price.

Your Task: Write a query to find all products in the ‘Electronics’ or ‘Home’ categories that have a retail_price between 100 and 500.

Click to see the solution

SELECT product_name, category, retail_price
FROM products
WHERE category IN ('Electronics', 'Home') 
  AND retail_price BETWEEN 100 AND 500;

💡 Pro Tip: The “Case” for LIKE

While MySQL is often case-insensitive, some databases (like PostgreSQL) differentiate between LIKE and ILIKE. To keep your SQL for data analysis skills portable, always try to use the exact casing of the data when possible. Additionally, remember that LIKE can be slower on very large tables than exact matches, so use it wisely!

Further Learning and Resources

Internal Link: Mastered basic filtering? Revisit Lesson 3: Filtering Data with the WHERE Clause to reinforce the basics.
External Link: Deep dive into the MySQL Pattern Matching Documentation for more wildcard tricks.

Next Lesson: We will learn how to Sort and Organize your data using ORDER BY and how to find unique values with DISTINCT!