AND, OR, NOT, IN, BETWEEN, LIKE
One condition is rarely the question. Real questions sound like "western sales over eight hundred dollars", or "anything in the north or the south", or "every product whose name ends in Table". This lesson is the vocabulary for stringing conditions together, plus the one operator precedence rule that quietly produces wrong answers when people skip it.
AND and OR
AND keeps a row only when
both sides are true. OR keeps it
when either side is. You can chain as many as you like. The starter query above uses one AND, and
adding conditions narrows the result each time, which is the mental model to hold: AND makes the
answer smaller, OR makes it bigger.
The trap is that AND binds more tightly than OR, exactly as multiplication binds more tightly than addition. So this:
WHERE region = 'North' OR region = 'South' AND revenue > 5000
does not mean what almost everyone reads it as. It means every northern row, plus southern rows
over five thousand. The engine reads it as
North OR (South AND big).
If you meant "northern or southern, and big either way", the parentheses are not optional:
WHERE (region = 'North' OR region = 'South') AND revenue > 5000
My rule, and I have never regretted it: the moment an AND and an OR appear in the same clause, bracket the OR. Even when the precedence happens to be on your side, the reader should not have to know the rule to trust the query.
IN, for a list
Four ORs on the same column is a lot of typing and a lot of places to misspell a category.
IN collapses it:
WHERE region IN ('North', 'South')
is exactly the bracketed OR from above, and it stays readable at ten values. Its negation,
NOT IN, does the opposite.
A caution to file away for later: NOT IN behaves badly when the list contains a NULL. In that case the whole condition evaluates to unknown for every row, and you get nothing back. It comes up when the list is the result of a subquery rather than something you typed, which is lesson 14. For a hand-written list you are safe.
BETWEEN, for a range
units BETWEEN 5 AND 10 is
shorthand for units >= 5 AND units <= 10.
Both ends are included, which is the single most misremembered fact about it. If you want five
up to but not including ten, BETWEEN is the wrong tool and you should write the two comparisons out.
BETWEEN works on dates and on text as well as numbers, and the inclusive-both-ends rule is
exactly where dates go wrong. BETWEEN DATE '2025-03-01' AND DATE '2025-03-31'
is fine for a date column. Use the same range against a timestamp column and you lose almost the
whole of the thirty-first, because a timestamp at half past nine in the morning is after
midnight on the thirty-first. On timestamps, prefer
>= start AND < next month.
LIKE, for a pattern
LIKE matches text against a
pattern with two wildcards. A percent sign stands for any run of characters, including none. An
underscore stands for exactly one character.
'%Table'matches anything ending in Table.'Aurora%'matches anything starting with Aurora.'%Desk%'matches anything containing Desk anywhere.'SKU-2_1'matches SKU-201 and SKU-211 but not SKU-2001.
LIKE is case sensitive here. DuckDB also gives you
ILIKE, which is not, and which
is what you usually want against human-entered text. ILIKE is a Postgres extension rather than
standard SQL, so it will not travel to every database.
One more: a leading percent sign means the engine cannot use an index to find matches, so on a
large table LIKE '%thing%' reads
everything. It does not matter on a hundred rows. It matters enormously on a hundred million,
and it is worth knowing before you meet one.
NOT
NOT inverts a condition, and
most operators have a built-in negative form that reads better:
NOT IN,
NOT BETWEEN,
NOT LIKE,
IS NOT NULL. Prefer those to
wrapping the whole thing in NOT (...),
which reads like a riddle at the end of a long clause.
Exercises
1. Two regions
Show every row from the North or the South. Use the operator that keeps it to one condition.
Hint
IN takes a parenthesised, comma-separated list of quoted values.
2. A range and a category
Show rows where units are between five and ten inclusive, and the channel is Online.
Hint
BETWEEN for the range, AND for the second condition. No brackets needed with only ANDs.
3. Two name endings
Show rows whose product name ends in Desk or ends in Table.
Hint
Two LIKE patterns joined by OR. The percent sign goes at the front of each.
4. Everything except two regions
Show every row that is neither West nor East.
Hint
NOT IN with a two-value list is the short way. Two not-equals joined by AND also works.
What to look up next
Look up "regexp_matches" in the DuckDB documentation. LIKE runs out of road quickly on messy text, and a real regular expression in a WHERE clause is one of those things that feels like cheating the first time it works.