Beginner-Friendly Topic
Take your time - it's perfectly normal to re-read this topic 2-3 times. Try the interactive code editor below to run code yourself. Use the Q&A section to check your understanding before moving on. You've got this! 🚀
IN & NOT IN
IN checks if a value matches any value in a list. It's cleaner than writing multiple OR conditions. NOT IN excludes values in the list. IN can also take a subquery - making it extremely powerful for dynamic filtering.
IN Rules
- IN (val1, val2, val3) - value must match one from the list
- Equivalent to: col = val1 OR col = val2 OR col = val3
- NOT IN - value must not match any in the list
- IN with subquery: WHERE id IN (SELECT user_id FROM orders)
- Beware: NOT IN with NULL in the list always returns empty result!
- IN is more readable than many ORs
IN & NOT IN Examples
-- Filter by multiple statuses (IN replaces multiple ORs)
SELECT id, status FROM orders
WHERE status IN ('pending', 'shipped', 'processing');
-- Equivalent (harder to read):
-- WHERE status = 'pending' OR status = 'shipped' OR status = 'processing'
-- NOT IN: everything except these
SELECT name FROM products
WHERE name NOT IN ('Laptop', 'Phone');
-- Result: Headphones
-- IN with numeric list
SELECT * FROM users WHERE id IN (1, 3);
-- IN with subquery (users who placed orders)
SELECT name, email FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);
-- Result: John Doe, Alice Smith
-- NOT IN with subquery (users who NEVER ordered)
SELECT name FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);
-- Result: Bob Lee (no orders)
-- WARNING: NOT IN with NULL
-- If subquery returns NULL, NOT IN always returns empty!
-- Fix: use NOT EXISTS instead (safer)Quick Quiz
Tip
Tip
Practice IN NOT IN in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Relationships define how tables connect through foreign keys
Common Mistake
Warning
A common mistake with IN NOT IN is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of IN NOT IN from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- IN checks if a value matches any value in a list.
- IN (val1, val2, val3) - value must match one from the list
- Equivalent to: col = val1 OR col = val2 OR col = val3
- NOT IN - value must not match any in the list