Basics & filtering · Easy
How do BETWEEN, IN and LIKE behave, and what are their common pitfalls?
Short answer
BETWEEN is inclusive at both ends, IN tests membership in a list, and LIKE matches patterns with % (any characters) and _ (one character). The classic traps are dates stored with a time part making BETWEEN miss the last day, case sensitivity differing between databases for LIKE, and NOT IN returning nothing when the list contains a NULL.
Detailed answer
BETWEEN a AND b means >= a AND <= b. With pure dates that is fine, but if a column stores timestamps, BETWEEN '2026-01-01' AND '2026-01-31' silently drops everything after midnight on the 31st. The safer habit is a half-open range: >= start AND < next_start.
IN (…) is shorthand for several ORs. It reads well for short fixed lists; for long lists, join to a table instead.
LIKE uses % for any run of characters and _ for exactly one. In SQLite, LIKE is case-insensitive for ASCII letters; in PostgreSQL it is case-sensitive (ILIKE is the insensitive version). A leading % also prevents an ordinary index from being used.
-- Delivered or shipped orders placed in Q1 2026, using a half-open date range
SELECT order_id, order_date, status
FROM orders
WHERE status IN ('delivered', 'shipped')
AND order_date >= '2026-01-01'
AND order_date < '2026-04-01'
ORDER BY order_date;
-- Products whose name contains "shoe" or starts with "S"
SELECT product_name, category
FROM products
WHERE product_name LIKE '%shoe%'
OR product_name LIKE 'S%';
Pitfall to mention: NOT IN (subquery) returns no rows at all if the subquery yields even one NULL. See the NULL-handling question for the fix.