SQL IN operator

Here is a clear and practical guide to the SQL IN operator, with examples, performance notes, and best practices.

What the SQL IN operator Does

IN allows you to test whether a value matches any value in a list or any value returned by a subquery.

It simplifies multiple OR conditions.

 Basic Syntax

Equivalent to:

Basic Examples

1. Match any value in a list

2. NOT IN (exclude values)

⚠ Warning: NOT IN behaves differently when the list contains NULL
(see notes below).

 IN with Subquery

Common for relational filtering:

 IN with Numbers / IDs

 Important Behavior Notes

✔ IN is NULL-safe (but NOT IN is not)

  • IN ignores NULL in the list

  • NOT IN returns no rows if the list contains NULL

Example (dangerous):

WHERE id NOT IN (1,2,NULL) -- returns ZERO rows!

Best fix:
Use NOT EXISTS instead of NOT IN.

Performance Tips

✔ For large lists, subqueries or JOINs may perform better
✔ Ensure the column in the IN clause is indexed
✔ Avoid extremely long IN lists
✔ For big datasets, consider replacing with JOIN

✔ Real-World Use Cases

1. Select customers in selected regions

2. Get orders placed by premium users

3. Exclude system user IDs

You may also like...