🌱BeginnerFiltering & Sorting
IN and NOT IN Operators
Filter rows against a list of values with IN and NOT IN.
Overview
IN checks whether a value matches any item in a list (or subquery result). NOT IN is its negation. Both are shorthand for multiple OR conditions and make queries much more readable when filtering against several discrete values.
Critical warning: NOT IN with NULL in the list always returns no rows. If any value in the list (or subquery result) is NULL, NOT IN produces NULL for every comparison, effectively filtering out all rows. Use NOT EXISTS instead of NOT IN when the list might contain NULLs.
Key Points
- •col IN (v1, v2, v3) same as col=v1 OR col=v2 OR col=v3
- •col NOT IN (v1, v2) excludes matching rows
- •IN with subquery: col IN (SELECT col FROM other_table)
- •NOT IN with NULL values ALWAYS returns no rows use NOT EXISTS instead
- •ANY is an alias for IN with subqueries: col = ANY(array)
Examples
IN with a list and subquery
-- Filter by list of values
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');
-- IN with subquery (orders for VIP customers)
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'vip');NOT IN NULL trap
-- If any value in list is NULL, NOT IN returns 0 rows!
SELECT * FROM users WHERE id NOT IN (1, 2, NULL); -- returns 0 rows!
-- Safe alternative with NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM blocked_ids b WHERE b.id = u.id);Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4