Reference · SQL (DuckDB)
Check allowed values in SQL (DuckDB)
Quarantine rows whose value is not in an approved list.
When to use it
Status codes, countries and other controlled vocabularies.
How
x IN (...) inside a CASE that produces the reason.
CASE WHEN status NOT IN ('shipped', 'pending', 'cancelled') OR status IS NULL THEN 'bad_status' END- x NOT IN (...) is NULL (not true) when x is NULL; test IS NULL explicitly.
Pitfalls
- Forgetting how missing values behave.
- Checking before normalizing case and spacing.
Lesson: Quarantine with reasons, in rule order
Practise it: Validate quantities and prices against ranges