Python SQL IN & NOT IN: Membership Operators & Subqueries
The `IN` operator specifies multiple discrete values in a `WHERE` clause, serving as a clean, compact shorthand for multiple `OR` conditions. `IN` can check a static list of literal values or the dynamic output of a nested subquery.
"`IN` is like a VIP guest list checked at the door: "If your name is on this list of 5 approved guests, come on in.""
Deep Dive: How It Works
Shorthand for OR: `WHERE country IN ('USA', 'UK', 'Canada')` is mathematically equivalent to `WHERE country = 'USA' OR country = 'UK' OR country = 'Canada'`.
Dynamic Subquery: `WHERE customer_id IN (SELECT id FROM vip_customers)` matches rows whose IDs appear in the subquery result set.
The NOT IN NULL Trap: If a subquery returns ANY NULL value, `NOT IN` evaluates to `UNKNOWN` for all rows, returning ZERO results!
Syntax Blueprint
SELECT column1
FROM table_name
WHERE column1 IN ('value1', 'value2', 'value3');Use IN with literal value lists or subqueries to test set membership.
Core Rules to Remember
Common Beginner Traps & How to Fix Them
Using `NOT IN (SELECT nullable_col FROM ...)` when the subquery contains a single NULL.Why it happens: In 3VL logic, `x NOT IN (1, 2, NULL)` becomes `x != 1 AND x != 2 AND x != NULL`, which always evaluates to UNKNOWN.
How to fix: Filter out NULLs in subquery (`WHERE nullable_col IS NOT NULL`) or use `NOT EXISTS`.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Filter with IN Operator
- Select `name` and `status` from `tasks` where `status IN ('todo', 'in_progress')`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.