Chapter 05 · basic · 6 min
AND, OR, NOT: combining conditions
One condition in WHERE gets you far, but real questions usually need more than one: "delivered and over RM 100", "cancelled or pending". AND, OR, and NOT are how you combine and invert conditions.
We'll keep using the orders table.
The dataset
The same `orders` table: customer, total, and status.
AND: every condition must hold
Chain conditions with AND and a row only qualifies if all of them are true:
| customer | total | status |
|---|---|---|
| Aisyah | 120.00 | delivered |
| Chong | 310.00 | delivered |
Delivered AND over RM 100
OR: any condition will do
OR is looser: a row qualifies if at least one condition is true:
| customer | status |
|---|---|
| Bala | cancelled |
| Devi | pending |
| Farid | pending |
Cancelled OR pending
Mixing AND and OR needs parentheses
Without parentheses, SQL evaluates AND before OR, the same precedence rule as multiplication before addition. That can silently change what you meant to ask. The parenthesized version below correctly reads as "(pending or cancelled) but only if over RM 100", and returns just one row:
| customer | total | status |
|---|---|---|
| Farid | 210.00 | pending |
Drop the parentheses and SQL reads it as "pending, OR (cancelled and over RM 100)", a completely different, and wrong, question. This exact mistake is one of the most common silent bugs in real reporting queries: it never errors, it just quietly answers a different question than the one you intended.
Parentheses force the OR to group first
NOT: reverse a condition
NOT flips a condition's truth value. On a simple equality check it's just a style choice (NOT (status = 'cancelled') behaves like status <> 'cancelled'), but it earns its keep in front of IN, BETWEEN, and LIKE next, since none of those have a separate <>-style shortcut:
| customer | status |
|---|---|
| Aisyah | delivered |
| Chong | delivered |
| Devi | pending |
| Farid | pending |
Everything except cancelled
If you've used Excel or Google Sheets
The logic is identical to spreadsheet formulas: AND(status="delivered", total>100) and OR(status="cancelled", status="pending") are literally the same functions with the same names. The one thing SQL adds that a spreadsheet formula usually doesn't force you to think about is precedence. Get in the habit of parenthesizing any time you mix AND and OR in the same condition, even when you're fairly sure you know how it'll be read.
Ready to practice? Zalora's order filter question below combines AND, OR, and NOT on a slightly larger dataset.
Once you're comfortable chaining conditions, the next step is a shortcut for the common case of "one column, several acceptable values": IN, plus its range-based cousin, BETWEEN.