SQL IN operator

SQL Tutorial

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):

Best fix:

 

.

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...