Filtering with IN and NOT IN

1. The IN Operator: A Shortcut for Multiple ORs

The IN operator allows you to specify multiple values in a WHERE clause. It acts as a shorthand for multiple OR conditions, making your queries cleaner, more readable, and easier to maintain.

Instead of writing a long chain of OR statements, you can simply provide a list of values to match against.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Comparison:

Consider the task of finding all users from your key Latin American markets: Colombia, Mexico, and Argentina.

Using OR:

SELECT user_id, email, country
FROM users
WHERE country = 'Colombia' OR country = 'Mexico' OR country = 'Argentina';

Using IN:

SELECT user_id, email, country
FROM users
WHERE country IN ('Colombia', 'Mexico', 'Argentina');

Both queries produce the same result, but the version with IN is much more concise, especially if your list of countries grows. This is directly applicable to your work in product design, where you might need to analyze a cohort of users from a specific set of target markets for a new feature launch.

2. The NOT IN Operator: Excluding a Set of Values

Conversely, the NOT IN operator allows you to exclude all rows that match a list of values. It's the equivalent of chaining multiple AND ... != conditions.

This is extremely useful when you want to analyze a segment of users by excluding certain groups. For example, you might want to look at the behavior of all paying customers by excluding those on 'free' or 'trial' plans.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);

Example:
To get a list of all paying users, you could write:

SELECT user_id, email, subscription_plan
FROM users
WHERE subscription_plan NOT IN ('free', 'trial');

This query retrieves every user whose plan is not 'free' and not 'trial', giving you a clean list of your active, paying customer base.

3. Video Demonstration: IN and NOT IN in Action

To see these operators in a practical context, let's watch a short tutorial. This video does an excellent job of demonstrating the syntax and comparing IN to the OR operator you already know.

4. Reference and Further Reading

For a quick text-based reference, the W3Schools page on the IN operator is a great resource. It concisely summarizes the syntax and provides clear examples.

You may notice the article also mentions using IN with a SELECT statement (this is called a subquery). That's a more advanced technique that we will cover in detail in Module 6. For now, just be aware that it's possible, but focus on using IN with a static list of values.

5. Practice: Defining User Cohorts

Now, let's apply what you've learned to some scenarios you might encounter in your work. Imagine you have a users table with columns like user_id, email, country, subscription_plan, and job_role.

Problem 1: Your team is testing a new feature aimed at users in creative roles. Write a query to get the user_id and email for all users whose job_role is 'UX Designer', 'Product Designer', or 'Illustrator'.

SELECT user_id, email
FROM users
WHERE job_role IN ('UX Designer', 'Product Designer', 'Illustrator');

Problem 2: You want to run a survey for users in your non-English speaking markets. Pull a list of all users who are not from the 'USA', 'UK', 'Canada', or 'Australia'.

SELECT user_id, email, country
FROM users
WHERE country NOT IN ('USA', 'UK', 'Canada', 'Australia');

Problem 3 (Challenge): You need to identify high-value, active customers from your primary European markets for a special workshop. Find all users with a subscription_plan of 'premium' or 'enterprise' who are also from 'Germany', 'France', or 'Spain'.

SELECT user_id, email, country, subscription_plan
FROM users
WHERE 
  subscription_plan IN ('premium', 'enterprise') 
  AND country IN ('Germany', 'France', 'Spain');

Conclusion

Great job! You've added two very practical and powerful operators to your SQL toolkit. Using IN and NOT IN will make your queries for filtering on multiple items much cleaner and more intuitive.

Key Takeaways:

• IN is a clean and efficient shorthand for multiple OR conditions, perfect for checking against a list of values.
• NOT IN is used to exclude rows that match any value in a specified list.
• Both operators are essential for defining cohorts—specific groups of users or items—which is a cornerstone of data analysis in product development and design.

In our next lesson, we will address a common data quality issue: how to handle records where data is missing. We'll learn to use IS NULL and IS NOT NULL to specifically filter for or exclude rows with empty values.