Filtering with the IN Operator

Hello! Welcome to your next lesson in SQL for product designers.

Introduction

In our last session, you learned how to use the WHERE clause with comparison operators like =, !=, and > to filter your data and select specific rows. This is a crucial skill for isolating individual user records or analyzing events that happened before or after a certain date.

But what happens when you need to investigate a group of users? For example, you might have a list of 15 user_ids from a recent usability test and want to see all their in-app actions. You could write a long WHERE clause with 15 OR conditions, but that's inefficient and hard to read.

Today, we'll learn a much more elegant solution.

Lesson Goal: By the end of this 60-minute lesson, you will be able to use the IN operator to filter data for a specific set of values, such as a list of user IDs or event names.

1. From Multiple ORs to IN

Let's start with a scenario. As a product designer, you often work with specific cohorts. Imagine you want to analyze the behavior of users from your three primary markets: the United States, Germany, and the United Kingdom.

Based on our last lesson, you might write a query like this:

SELECT user_id, email, signup_date
FROM users
WHERE country = 'USA'
   OR country = 'UK'
   OR country = 'DE';

This works, but it's verbose. If you add more countries, the query becomes even longer. The IN operator is designed to simplify exactly this situation. It lets you check if a column's value matches any value in a list.

The query above can be rewritten like this:

SELECT user_id, email, signup_date
FROM users
WHERE country IN ('USA', 'UK', 'DE');

This version is cleaner, more readable, and easier to edit. The list of values is enclosed in parentheses () and separated by commas.

2. The IN Operator in Action

Let's watch a short video that demonstrates how the IN operator works and contrasts it with other filtering methods.

As you saw, IN is perfect for selecting items from a list, whether they are numbers (like category_id) or text (like country).

For a solid reference on the syntax and more examples, the W3Schools article on the IN operator is an excellent resource.

3. Excluding a Set of Values with NOT IN

Just as you can select a list of values, you can also exclude a list of values. This is done with the NOT IN operator. This is incredibly useful for cleaning your data before an analysis.

Product Design Scenario:
You're analyzing feature adoption, but you want to exclude your internal team members and a group of beta testers who had early access. You have a list of their user_ids.

SELECT *
FROM events
WHERE event_name = 'new_feature_used'
  AND user_id NOT IN (12, 45, 101, 102, 230); -- Internal and beta user IDs

This query first finds all events named 'new_feature_used' and then filters out any that were performed by the users in your exclusion list.

The W3Schools article you just looked at also covers NOT IN. Take a moment to review that section.

4. Practice: Filtering for Product Insights

Now, let's apply what you've learned. Imagine you are a designer for an online learning platform. You have a courses table with the following structure:

courses table:

Write the queries to answer the following questions.

Question 1: The marketing team is running a campaign focused on course_ids 101, 201, and 302. Write a query to retrieve all information for these specific courses.

Question 2: You are tasked with redesigning the course pages for the 'Design' and 'Data' categories. Write a query to pull all courses that fall into these two categories.

Question 3: For a competitive analysis, you want to look at all courses except those taught by your most prolific instructors, with instructor_ids 55 and 58. Write a query to find the remaining courses.

SELECT *
FROM courses
WHERE course_id IN (101, 201, 302);

SELECT *
FROM courses
WHERE category IN ('Design', 'Data');

SELECT *
FROM courses
WHERE instructor_id NOT IN (55, 58);

Conclusion

Great job! You've added a powerful and practical tool to your SQL toolkit. The IN operator is something you'll use constantly as a product designer to efficiently query for specific segments of users, events, or items.

Key Takeaways:

• The IN operator is a clean and readable shorthand for multiple OR conditions.
• It is used to filter rows where a column's value matches any value in a specified list: WHERE column IN (value1, value2, ...).
• The NOT IN operator does the opposite, excluding all rows that match a value in the list.
• This is extremely useful for pulling data on specific user cohorts or filtering out irrelevant data like test accounts.

Next Lesson Preview:

So far, we've combined conditions that apply to the same column (e.g., country IN ('USA', 'UK')). But what if you need to combine different conditions? For instance, how would you find all 'premium' users and who are from the 'USA'? In our next lesson, we'll dive into the AND and OR operators, which will allow you to build much more layered and specific queries to answer complex product questions.