🔍 Mastering SQL IN and NOT IN: Simplify Filtering with Practical Examples

When writing SQL queries, one common task is to filter rows based on whether a column value matches a list of values. Instead of writing multiple OR conditions, SQL gives us a neat solution — the IN and NOT IN operators.

In this guide, you’ll learn how IN and NOT IN work, how to use them effectively, and why they can make your queries shorter, faster, and more readable.

🚀 What is IN in SQL?

The IN operator is used to match a value against a set of values. If the value exists in the list, the condition evaluates to TRUE.

🧾 Basic Syntax

SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, value3, ...);

It works like a shorthand for multiple OR conditions.

🧠 Simple Example

Imagine we have a students table with a grade column. You want to find students who are in grades 9, 10, or 11.

SELECT *
FROM students
WHERE grade IN (9, 10, 11);

This is equivalent to:

WHERE grade = 9 OR grade = 10 OR grade = 11

But much cleaner and easier to maintain!

📌 Use Case: IN with Strings

You’re not limited to numbers — IN works with text values too.

SELECT *
FROM customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago');

This will return customers living in any of the three cities.

⚙️ Use IN with Subqueries

You can also use IN with a subquery to filter based on the results of another query.

🧾 Example

SELECT name
FROM employees
WHERE department_id IN (
  SELECT id
  FROM departments
  WHERE region = 'North'
);

This finds employees who belong to departments in the “North” region.

🚫 What is NOT IN in SQL?

The NOT IN operator is the opposite of IN. It filters out rows where the value is NOT present in the given list.

🧾 Syntax

SELECT column1, column2
FROM table_name
WHERE column_name NOT IN (value1, value2, value3, ...);

🧠 Example

Get a list of employees who do not work in the Sales or HR departments:

SELECT *
FROM employees
WHERE department NOT IN ('Sales', 'HR');

📛 Common Pitfall: NULL with NOT IN

When using NOT IN with a list that contains NULL, it can cause unexpected behavior.

⚠️ Example

SELECT *
FROM employees
WHERE department_id NOT IN (1, 2, NULL);

This will return no rows — because SQL doesn’t know how to handle comparisons with NULL.

✅ To avoid this, always make sure your subqueries or lists do not contain NULLs when using NOT IN.

🔄 IN vs. JOIN – Which One to Use?

Sometimes, IN can be replaced with a JOIN. Here’s a quick comparison:

• IN is simpler for small lists or subqueries returning a single column.
• JOIN is better when you need data from both tables.

Example with IN:

SELECT name
FROM products
WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

Same logic with JOIN:

SELECT p.name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.active = 1;

📋 Real-World Examples of IN and NOT IN

🎯 Example 1: Find users from selected countries

SELECT *
FROM users
WHERE country IN ('India', 'UK', 'Australia');

🎯 Example 2: Exclude blacklisted emails

SELECT email
FROM newsletter
WHERE email NOT IN (
  SELECT email
  FROM blacklist
);

💡 Performance Tip: IN vs. EXISTS

• IN can be slower with large lists or subqueries.
• EXISTS is sometimes faster when checking for existence of rows.

So in complex queries, consider:

WHERE EXISTS (SELECT 1 FROM ...)

Instead of:

WHERE id IN (SELECT id FROM ...)

Test both for your dataset to see which performs better.

✅ Summary Table

🛠 Pro Tips

• Use IN for a clean way to filter by a list of values.
• Avoid NOT IN with NULLs — filter them out first.
• Use parentheses and spacing for clear, readable queries.
• Prefer JOIN or EXISTS for large datasets or complex conditions.

🏁 Conclusion

SQL’s IN and NOT IN operators offer a powerful, concise way to filter rows based on a set of values. They’re not just easier to write, but they also make your code cleaner and more maintainable.

Whether you’re selecting rows from a known list or comparing with the results of a subquery, these operators are essential tools for anyone writing SQL.