SQL WHERE Operators
The operators you use to keep only the rows you care about.
WHERE runs once per row and keeps the rows where the condition istrue. Rows where the condition is false orNULL are dropped.
Comparison operators
| Operator | Meaning | Example |
|---|---|---|
= | Equal to | status = 'shipped' |
<> or != | Not equal to | status <> 'cancelled' |
< <= | Less than / or equal | price <= 10 |
> >= | Greater than / or equal | quantity_on_hand > 0 |
Combining conditions
select *
from products
where category_id = 1
and (price < 10 or quantity_on_hand = 0)AND binds tighter than OR, so use parentheses whenever you mix them. Otherwise the query may match far more rows than you expect.
IN vs OR
Use IN when one column can equal any value in a short list. UseOR when the alternatives are different columns or different comparisons.
where state = 'PA'
or state = 'OR'
or state = 'FL'
where state in ('PA', 'OR', 'FL')Set, range, and pattern operators
| Operator | Use | Example |
|---|---|---|
IN | Match any value in a list | state in ('PA', 'NY', 'OH') |
NOT IN | Match none of a list | status not in ('cancelled', 'refunded') |
BETWEEN | Inclusive range | price between 10 and 25 |
LIKE | Pattern match (% = any, _ = one) | product_name like '%seed%' |
IS NULL | Missing value | shipped_date is null |
NOT IN behaves surprisingly when the list contains aNULL: it can filter out every row. PreferNOT EXISTS or add ... is not null when the column may be null. See Null handling.
LIKE is case-sensitive in DuckDB and Postgres. UseILIKE for case-insensitive matching in both. In MySQL,LIKE is usually case-insensitive by default.