Part 17: List Filtering with IN and NOT IN

The IN operator checks whether a value matches any value in a specified list or subquery.

1. The IN Operator

SELECT order_id, status 
FROM orders 
WHERE status IN ('shipped', 'delivered', 'completed');

2. ⚠️ The NOT IN and NULL Trap!

If a NOT IN list contains a single NULL value, the query will return 0 rows:

-- Evaluates to UNKNOWN for all rows!
SELECT * FROM users WHERE role NOT IN ('admin', NULL);

Key Takeaways

• Use IN to replace multiple OR clauses cleanly.
• Never use NOT IN if the list could contain NULL; use NOT EXISTS instead.

In Part 18, we will master Pattern Matching with LIKE and Wildcards!