SQL IN Operator – Use IN and NOT IN for Multi-Value Filters

Introduction – What is SQL IN?

The IN operator in SQL is used to match a column’s value against a list of values. It makes multiple OR conditions concise, readable, and efficient.

In this guide, you’ll learn:

• Basic syntax of IN and NOT IN
• Use with numbers, strings, and subqueries
• Best practices and common pitfalls

1. Basic SQL IN Syntax

SELECT * FROM employees
WHERE department IN ('HR', 'IT', 'Finance');

Returns rows where department is one of the listed values.

2. Using NOT IN

SELECT * FROM products
WHERE category NOT IN ('Discontinued', 'Obsolete');

Excludes rows matching any value in the list.

3. IN vs Multiple ORs

-- Using OR
WHERE department = 'HR' OR department = 'IT' OR department = 'Finance';

-- Using IN
WHERE department IN ('HR', 'IT', 'Finance');

IN is cleaner and performs better on large sets.

4. IN with Numeric Values

SELECT * FROM orders
WHERE status_code IN (200, 201, 404);

Works with integers and decimals too.

5. IN with Subqueries

SELECT * FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE active = true);

Dynamically filters based on another table’s values.

6. Caution with NULLs in NOT IN

-- This may return 0 rows if the list contains NULL
WHERE status NOT IN ('active', NULL);

Always filter NULLs out in subqueries when using NOT IN.

Best Practices

Summary – Recap & Next Steps

The IN operator allows for cleaner, more maintainable filtering when working with multiple values. It’s ideal for status checks, lookups, and relationship-based queries.

Key Takeaways:

• Use IN for simple multi-value matches
• Use NOT IN to exclude specific values
• Subqueries let you create dynamic IN lists
• Avoid NULLs with NOT IN for safe logic

Real-World Relevance:
Used in filters, dropdown selections, security rules, and conditional logic for audit trails.

Next: Explore BETWEEN for filtering within numeric and date ranges.

FAQ – SQL IN Operator

What does SQL IN do?

Filters rows where a column matches one of the values in a list.

Can I use IN with numbers and strings?

Yes. The values inside the list must match the column’s data type.

Is IN better than multiple OR conditions?

It’s shorter, easier to read, and often performs better.

Can I use IN with a subquery?

Yes, and it’s highly useful for dynamic conditions.

What should I watch out for with NOT IN?

NULLs in the list may cause unexpected behavior. Use WHERE ... IS NOT NULL to clean subquery results.