One row at a time
where runs after the rows are collected and before anything else. It sees one row at a time and asks a yes/no question about it. A row that answers no is gone — it never reaches select, never reaches a group, never reaches the sort.
select name, price from doors where price > 1500;
= equal <> not equal (!= also works, <> is the standard)
< > less, greater <= >=
between a and b inclusive at both ends
in (…) one of a list
like 'C%' a text pattern
is null the only way to test for nothingand, or, and the brackets between them
and binds tighter than or, exactly like × before +. The first query quietly returns every pivot door at any price. Nothing errors; the number is just wrong. Put brackets round every or and the question stops being interesting.
-- reads as: (style = 'pivot') or (style = 'glazed' and price < 2000)
where style = 'pivot' or style = 'glazed' and price < 2000
-- what was meant
where (style = 'pivot' or style = 'glazed') and price < 2000between, in, not
where price between 600 and 1200 -- price >= 600 and price <= 1200
where material in ('oak', 'glass') -- material = 'oak' or material = 'glass'
where style not in ('flush')
where not discontinued -- 0 is false, 1 is true
-- dates are text in SQLite, and ISO text sorts like a date
where placed >= '2024-06-01' and placed < '2024-10-01'between '2024-06-01' and '2024-06-30' misses anything stamped later that day if the column holds a time as well. For date ranges, use >= start and < next_start. It is right in every dialect and never loses the last afternoon.
like: patterns in text
like is case-insensitive for ASCII in SQLite and MySQL, and case-sensitive in PostgreSQL, where ilike is the insensitive one. If it matters, say where lower(name) like 'c%' and it behaves the same everywhere — at the cost of the index, which the Indexes lesson comes back to.
where name like 'C%' -- starts with C
where name like '%a' -- ends with a
where name like '%se%' -- contains se
where name like '_____' -- exactly five characters
where name like '100!%' escape '!' -- a literal % signThe condition that is not a filter
where price * qty > 5000
where length(name) > 6
where lead_days % 7 = 0
where substr(placed, 1, 4) = '2024' -- works, but see the noteComparing substr(placed, 1, 4) makes the database compute that for every row. placed >= '2024-01-01' and placed < '2025-01-01' asks the same question in a form an index can answer. It is the difference between reading a book and using its contents page.