IN and NOT IN for Sets
Filter against value lists with IN and NOT IN, learn the NULL gotcha in NOT IN, and convert to EXISTS for safe semantics.
IN and NOT IN for Sets is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
IN: Match Against a List
IN is a shortcut for many ORs:
SELECT * FROM users WHERE country IN ('US', 'CA', 'GB');
-- Equivalent to:
SELECT * FROM users
WHERE country = 'US' OR country = 'CA' OR country = 'GB';IN with a Subquery
You can use a subquery on the right side:
SELECT * FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE country = 'US'
);NOT IN: Exclude a List
Filter out matching rows:
SELECT * FROM users WHERE status NOT IN ('banned', 'deleted');The NOT IN NULL Trap
If the right-hand side contains NULL, NOT IN returns NULL — and rows where the predicate is NULL are dropped. Result: zero rows.
-- Suppose blocked_names contains NULL:
SELECT name FROM users
WHERE name NOT IN (SELECT excluded_name FROM blocked_names);
-- → 0 rows, silently
-- Safer: filter out NULLs explicitly, or use NOT EXISTS:
SELECT name FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM blocked_names b WHERE b.excluded_name = u.name
);Why NOT EXISTS Is Safer
NOT EXISTS uses standard correlated subquery semantics and is NULL-safe. Most planners optimise it identically to a well-formed NOT IN.
IN vs OR — Readability
IN beats long OR chains for clarity and the planner often optimises it identically:
-- Hard to read:
WHERE country = 'US' OR country = 'CA' OR country = 'GB' OR ...
-- Clear:
WHERE country IN ('US', 'CA', 'GB', ...)IN with Tuples (Row Constructors)
Match pairs of columns:
SELECT * FROM order_items
WHERE (order_id, product_id) IN (
(1, 42), (2, 99), (3, 17)
);Index Usage
An IN list on an indexed column can be executed as multiple index lookups (Bitmap Index Scan). It is generally fast.
-- Plan: Bitmap Index Scan + Bitmap Heap Scan
SELECT * FROM products WHERE id IN (1, 5, 99, 121, 4421);Big IN Lists
Lists of thousands of values can become slow. Prefer:
- JOIN against a real or temp table
= ANY(array)to pass an array parameter
SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3,...]);IN with Empty List
An empty IN list is illegal in standard SQL. Some clients pass a sentinel; safer to generate the SQL conditionally.
-- Use empty array trick in PostgreSQL:
SELECT * FROM products WHERE id = ANY($1::bigint[]);
-- Pass {} to get zero matches without rewriting the SQL.IN vs JOIN
Sometimes a JOIN is clearer than a subquery IN:
-- With IN:
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE pro);
-- With JOIN:
SELECT o.*
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.pro;Recap
IN is the friendly form for set membership.
- IN over OR for readability
- NOT IN is dangerous with NULLs — prefer NOT EXISTS
- For big sets, JOIN or pass arrays
Quick Check
What is the safest replacement for NOT IN (subquery) when the subquery may contain NULLs?
Frequently asked questions
Is the “IN and NOT IN for Sets” lesson free?
Yes — the full text of “IN and NOT IN for Sets” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “IN and NOT IN for Sets”?
Filter against value lists with IN and NOT IN, learn the NULL gotcha in NOT IN, and convert to EXISTS for safe semantics. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “IN and NOT IN for Sets” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- LIKE Patterns and Wildcards
- IN and NOT IN for Sets
- BETWEEN for Ranges
- IS NULL, IS NOT NULL and COALESCE