IN usage and examples

Overview

IN tests whether a value equals any value from a list or subquery and is a concise replacement for several equality OR predicates. Duplicate subquery values do not change the match. With NOT IN, a value that has no direct non-NULL match becomes UNKNOWN when the set contains NULL, which commonly leaves no qualifying rows.

Basic syntax

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers);

Examples

IN example

Read customer ids from a subquery and return orders whose customer_id belongs to that set.

Sample table

Table：orders

orders
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
2	102	202	Bob	IT	900	pending	2026-01-08	50
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Table：customers

customers
id	name	city	status	phone	nickname
101	Alice	New York	active	138-0000-0001	NULL
102	Bob	Seattle	active	NULL	Bobby
103	Cindy	Austin	inactive	138-0000-0003	NULL

Run result

result
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
2	102	202	Bob	IT	900	pending	2026-01-08	50
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100

Common issues

Is IN a good replacement for multiple OR conditions?

Yes. IN is clearer when a column can match several fixed values.

Can IN use a subquery?

Yes. For example, WHERE customer_id IN (SELECT id FROM customers ...).

How do I choose between IN and EXISTS?

IN is straightforward for fixed lists; EXISTS is often clearer for correlated existence checks.

Keep reading

WHERE EXISTS BETWEEN

IN