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.
Schema
| id | INT |
| customer | TEXT |
| total | NUMERIC |
| status | TEXT |
Example data
AND: every condition must hold
Chain conditions with AND and a row only qualifies if all of them are true.
Before you run it — of the 5 orders, how many are both 'delivered' and over RM 100?
OR: any condition will do
OR is looser: a row qualifies if at least one condition is true.
Before you run it — how many of the 5 orders are either 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".
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.
Before you run it — reading it as "(pending or cancelled) and over RM 100", how many rows should come back?
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.
Before you run it — only Bala is cancelled. How many of the 5 customers come back?
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? AEON'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.