Topic 17 of 52
IN
Overview
The IN operator checks if a value matches any value in a list or subquery result. It replaces multiple OR conditions with cleaner syntax and enables powerful subquery-based filtering.
Syntax
sql
-- IN with explicit list
WHERE status IN ('pending', 'processing', 'shipped')
-- Equivalent to: WHERE status = 'pending' OR status = 'processing' OR status = 'shipped'
-- NOT IN
WHERE country NOT IN ('USA', 'Canada', 'UK')
-- IN with subquery (powerful!)
WHERE user_id IN (
SELECT user_id FROM premium_subscriptions WHERE is_active = TRUE
)
-- NOT IN with subquery (be careful with NULLs!)
WHERE user_id NOT IN (
SELECT user_id FROM blacklist WHERE user_id IS NOT NULL
)
-- IN with numbers
WHERE id IN (1, 2, 3, 100, 250)
-- Alternative: JOIN (often faster than IN with subquery)
SELECT u.* FROM users u
JOIN premium_subscriptions ps ON u.id = ps.user_id AND ps.is_active = TRUE;Common Pitfalls
- NOT IN returns NO rows if the subquery contains even one NULL — use NOT EXISTS instead: WHERE NOT EXISTS (SELECT 1 FROM ... WHERE ...).
- Large IN lists (thousands of values) can be slow — use a temporary table or JOIN instead.
- Interview tip: IN with a subquery is often slower than a JOIN. The query optimizer may convert it to a JOIN, but explicit JOINs give better control.
Real-World Example
Sending targeted emails to users based on their order status:
example
sql
-- Find users who have orders in specific statuses (for email campaign)
SELECT DISTINCT
u.id,
u.name,
u.email,
ARRAY_AGG(DISTINCT o.status) AS order_statuses
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE
o.status IN ('processing', 'shipped')
AND o.created_at >= NOW() - INTERVAL '7 days'
AND u.id NOT IN (
SELECT user_id FROM email_unsubscribes WHERE list_type = 'order_updates'
)
GROUP BY u.id, u.name, u.email;
-- Get products that have been ordered (exist in order_items)
SELECT id, name, price FROM products
WHERE id IN (SELECT DISTINCT product_id FROM order_items)
ORDER BY name;