IN, BETWEEN, LIKE
Learning Focus
Use this lesson to filter rows using IN, BETWEEN, LIKE, and ILIKE in DuckDB.
IN Operator
-- Match any value in a list
SELECT * FROM employees
WHERE department IN ('Engineering', 'Finance', 'Marketing');
-- NOT IN
SELECT * FROM employees
WHERE department NOT IN ('HR', 'Legal');
-- IN with subquery
SELECT * FROM employees
WHERE id IN (SELECT employee_id FROM project_assignments WHERE project = 'Alpha');
BETWEEN Operator
-- Inclusive range (both ends included)
SELECT * FROM employees WHERE salary BETWEEN 70000 AND 90000;
-- Date range
SELECT * FROM employees
WHERE hire_date BETWEEN DATE '2020-01-01' AND DATE '2022-12-31';
-- NOT BETWEEN
SELECT * FROM employees WHERE salary NOT BETWEEN 60000 AND 80000;
LIKE and ILIKE
-- LIKE: % = any sequence, _ = single character
SELECT * FROM employees WHERE name LIKE 'A%'; -- starts with A
SELECT * FROM employees WHERE name LIKE '%son'; -- ends with son
SELECT * FROM employees WHERE name LIKE '%li%'; -- contains li
SELECT * FROM employees WHERE name LIKE 'A__ce'; -- A + 2 chars + ce
-- ILIKE: case-insensitive version
SELECT * FROM employees WHERE name ILIKE 'alice%';
-- NOT LIKE
SELECT * FROM employees WHERE name NOT LIKE 'D%';
Regex with regexp_matches
-- DuckDB supports regex natively
SELECT * FROM employees WHERE regexp_matches(name, '^[A-C]');
SELECT * FROM employees WHERE regexp_matches(department, 'Eng|Fin');
Common Pitfalls
| Pitfall | Consequence | Prevention |
|---|---|---|
| Large IN lists | Slow execution | Use a temp table or subquery instead |
| LIKE without wildcard | Exact match (just use =) | Add % wildcards for partial matching |
| Case-sensitive LIKE | Missing results | Use ILIKE for case-insensitive patterns |
Quick Reference
WHERE col IN ('a','b','c')
WHERE col NOT IN ('x','y')
WHERE col BETWEEN 10 AND 100
WHERE col NOT BETWEEN 10 AND 100
WHERE col LIKE 'prefix%'
WHERE col ILIKE '%pattern%'
WHERE regexp_matches(col, 'regex')
What's Next
- Next: AND, OR, NOT - Combine multiple conditions.
- Module Overview - Return to this module index.