IN Operator

Check a value against a list — cleaner than chained ORs

Beginner 9 minINNOT INfiltering

Matching Against a List

IN checks whether a value matches any item in a specified list. It's a cleaner and more readable replacement for chaining multiple OR conditions together in your WHERE clause. Here's what to know:

Readable multi-value filter — WHERE country IN ('India', 'Japan', 'Australia') replaces three separate OR conditions with a single, clean expression that's easy to read and easy to extend with more values.

NOT IN has a NULL trap — NOT IN (1, 2, NULL) always returns zero rows because comparing anything to NULL yields unknown, and NOT unknown is still unknown. For safe exclusion, always use NOT EXISTS instead of NOT IN.

Subquery support — IN accepts a SELECT statement instead of hardcoded literals: WHERE customer_id IN (SELECT id FROM vip_customers). This lets you filter dynamically based on another query's results.

Knowledge Canvas

How IN Works

Check membership in a list

IN checks if a value matches ANY item in a list
Equivalent to multiple OR conditions — but cleaner
Works with literal lists or subquery results
NOT IN inverts the check — matches none in the list

The NOT IN + NULL Trap

The single most important IN gotcha

NOT IN (1, 2, NULL) → ALWAYS returns zero rows
NULL in the list makes every comparison unknown
Use NOT EXISTS instead of NOT IN for safety
Filter NULLs: NOT IN (SELECT col WHERE col IS NOT NULL)

IN Patterns

Common uses

Static list: WHERE status IN ('active', 'pending')
Subquery: WHERE id IN (SELECT cust_id FROM orders)
NOT IN: WHERE id NOT IN (SELECT reviewed_by FROM reviews)
Multi-value filter for dashboards and reports

Performance Notes

IN vs EXISTS at scale

Small static lists: IN is perfectly fast
Large subqueries: EXISTS often outperforms IN
NOT IN: prefer NOT EXISTS (NULL-safe + often faster)
Database optimizers often rewrite IN → semi-join

Syntax

Syntax Template

1WHERE column IN ('value1', 'value2', 'value3')

3-- Exclude values

4-- WHERE column NOT IN ('value1', 'value2')

6-- Using a subquery

7-- WHERE column IN (SELECT ...)

IN (...)True if column matches any value in the list

NOT IN (...)True if column matches none of the values

SubqueryIN can accept a SELECT statement instead of literals

Sample Data

customers

12 rows

id	name	country
C1001	Aarav Sharma	India
C1002	Sara Chen	Singapore
C1003	James Wilson	UK
C1004	Maria Garcia	Spain
C1005	Yuki Tanaka	Japan
C1006	Priya Patel	India
C1007	Alex Johnson	USA
C1008	Chen Wei	China
C1009	Emma Brown	Australia
C1010	Omar Hassan	UAE
C1011	Lena Muller	Germany
C1012	Ravi Kumar	India

orders

12 rows

id	status	total
5001	delivered	59.98
5002	delivered	449
5003	shipped	12.5
5004	delivered	89.99
5005	pending	245
5006	delivered	159
5007	shipped	45.5
5008	cancelled	79
5009	delivered	425
5010	pending	18.99
5011	delivered	134
5012	cancelled	65.5

Worked Example

Find all customers from India, Japan, or Australia.

SQL

1SELECT

2 name,

3 country

4FROM customers

5WHERE country IN ('India', 'Japan', 'Australia')

6ORDER BY

7 country;

IN checks each customer's country against the three values. It is equivalent to three OR conditions but reads as a single, clean filter.

Output

5 rows

name	country
Emma Brown	Australia
Aarav Sharma	India
Priya Patel	India
Ravi Kumar	India
Yuki Tanaka	Japan

Key Concepts

1IN replaces chained ORs: cleaner, more readable, same result

2NOT IN fails silently if the list contains NULL — use NOT EXISTS

3IN accepts subqueries: WHERE id IN (SELECT ...)

4Values in the list must match the column type

Pro Tip

IN collapses verbose OR chains into a single, readable expression.

When to Use

Filtering by status list, customers from selected countries, products in specific categories.

Challenge

Solve the problem below

Find all orders with status 'pending' or 'cancelled' from the orders table. Show id, status, and total.

Your Query

DISTINCT

BETWEEN