All 22 modules are open from the start — nothing here is
locked, and nothing costs anything. Sign in so your progress, titles and
credentials stay with you, on every device you use.
Free forever, with your Google account. No password, no payment.
A table with a million rows is not useful. A table with the nineteen rows
that answer your question is. WHERE is the clause that gets you
from one to the other, and getting its logic right is the difference between
a report that is correct and a report that merely looks correct.
Ready?
1
WHERE Is Asked Once per Row
WHERE takes a condition and applies it to every row
independently. If the condition is true for that row, the row survives.
If not, it is dropped. There is no memory between rows and no order to
it — each row is judged alone.
SELECT name
FROM users
WHERE is_active;
is_active is a boolean — it holds
true or false — so it is the
condition, with nothing to compare it against. Postgres has a real
boolean type, and this is the payoff. Databases without one store flags
as 1 and 0, and WHERE is_active is an error here:
operator does not exist: boolean = integer.
For a comparison that does need an operator, note the single
=. Many languages use == because they need
= for assignment; SQL has no assignment here, so one equals
sign is the comparison and there is nothing to confuse it with.
WHERE goes after FROM and before
ORDER BY. It also runs before the SELECT list is
computed, which is why you cannot use a column alias you defined in
SELECT inside your WHERE — the alias does not exist yet.
Fails
WHERE yearly > 100
Where yearly is an alias defined in the SELECT list. WHERE runs first, so the name is unknown.
Works
WHERE price * 12 > 100
Repeat the expression. WHERE can compute anything from the table's own columns.
Quick check
A table has 24 orders. WHERE amount > 100 returns 6. What happened to the other 18?
2
Single Quotes for Text, Nothing for Numbers
Text literals go in single quotes. Numbers go bare.
Double quotes mean something entirely different in SQL — they name a
column or a table — which is why WHERE plan = "free" tends
to fail with a message about a column called free.
WHERE plan = 'free' -- text, single quotes
WHERE amount > 100 -- number, no quotes
WHERE is_active -- boolean, already a condition
WHERE NOT is_active -- and its opposite
The comparison operators are the ones you would expect, with one
surprise: "not equal" is written <> in standard SQL.
!= works in most databases including this one, but
<> is the portable spelling.
Text comparison is exact. 'free' and
'Free' are different strings in most databases. When a
filter you are sure about returns nothing, the first thing to check is
the exact stored spelling — which is what SELECT DISTINCT
in module 1-04 is for.
=
Equal
One equals sign, not two.
<>
Not equal
The portable spelling. != also works nearly everywhere.
>=
At least
Includes the boundary. > does not.
''
Quoting a quote
'O''Brien' — double the apostrophe to include one.
Quick check
WHERE country = "UK" returns an error about a column named UK. Why?
3
AND Binds Tighter Than OR
AND needs both sides true. OR needs either.
Simple enough alone — and the source of a great many quietly wrong
reports the moment they are mixed, because AND is evaluated
first, exactly like multiplication before addition.
WHERE plan = 'pro' OR plan = 'team' AND is_active
-- what SQL actually reads:
WHERE plan = 'pro' OR (plan = 'team' AND is_active)
That query returns every pro user including the churned ones, which is
almost certainly not what was intended. The fix is brackets, and the
habit worth building is bracketing any mixed condition even when
you have worked out that you do not need to.
WHERE (plan = 'pro' OR plan = 'team') AND is_active
Everyday example, the shopping list
"Get bread or milk and eggs." Two people will read that two different
ways and one of them comes home wrong. Brackets are how you stop
having the argument — with SQL, and arguably at the shop.
Quick check
How many users does WHERE plan = 'free' OR plan = 'pro' AND NOT is_active return, given 5 free users (2 inactive), and 4 pro users (all active)?
4
Three Shorthands Worth Knowing Immediately
IN replaces a chain of ORs against the same
column. It is shorter, it reads better, and adding a fourth allowed value
costs one comma rather than another clause.
WHERE category = 'addon' OR category = 'service'
WHERE category IN ('addon', 'service') -- the same thing
BETWEEN is a range, and it includes both
ends. That inclusiveness is fine on numbers and a genuine trap
on dates and timestamps, where BETWEEN '2024-01-01' AND
'2024-01-31' silently excludes everything that happened during the
31st if the column carries a time as well as a date.
LIKE matches text by pattern. % stands for any
run of characters, _ for exactly one.
WHERE name LIKE 'Pro%' -- starts with Pro
WHERE name LIKE '%Seat' -- ends with Seat
WHERE name LIKE '%data%' -- contains data
A leading wildcard is slow
LIKE 'Pro%' can use an index — the database knows where to
start looking. LIKE '%Pro' cannot, because a match could
begin anywhere, so it has to read every row. On twelve rows this is
invisible. On twelve million it is the difference between a query and a
coffee break.
Quick check
Which orders does WHERE amount BETWEEN 9 AND 29 include?
0 of 4 completed
Loading the tables…
01
To do
WHERE is the filter. It sits after FROM, and
it is checked once per row: rows where the condition is true come back,
rows where it is not are dropped.
SELECT name FROM users WHERE amount > 100;
Note the single = for equality. SQL is not a programming
language here — one equals sign is the comparison, and there is no
assignment to confuse it with.
is_active is a boolean: it holds
true or false, so it needs no comparison at
all. WHERE is_active is already a condition. Writing
WHERE is_active = true works and is just noise; writing
WHERE is_active = 1 is an error, because a truth value and a
number are different things and Postgres would rather say so than guess.
Your task: return the name and
plan of every user who is active.
query.sql
PostgreSQLCtrl↵ to run
Hint
Add one line: WHERE is_active — the column is a boolean, so it is the whole condition on its own.
Output
02
To do
Text goes in single quotes. Double quotes mean
something else in SQL (they name a column or table), so a query written
with them will usually fail in a confusing way.
WHERE plan = 'free'
The comparison is exact. 'free' and 'Free' are
different strings to most databases, so when a filter mysteriously
returns nothing, checking the exact stored spelling is the first move.
Your task: return the name and
country of every user on the free plan.
query.sql
PostgreSQLCtrl↵ to run
Hint
WHERE plan = 'free' — single quotes around the text, and lower case, because that is exactly how it is stored.
Output
03
To do
AND requires both sides to be true; OR
requires either. Mixing them without brackets is the classic way to get
a wrong answer that looks plausible, so bracket anything you are not
completely sure about.
WHERE status = 'paid' AND amount > 100
The comparison operators are the ones you would expect:
=, <> (not equal), <,
>, <=, >=.
Your task: return order_id,
amount and status for every order that is paid
and worth more than 100.
query.sql
PostgreSQLCtrl↵ to run
Hint
Two conditions joined by AND: status = 'paid' AND amount > 100. Strictly more than 100, so an order of exactly 100 would not count.
Output
04
To do
IN replaces a chain of ORs. These two say the
same thing, and the second is far easier to read and to change later:
WHERE category = 'addon' OR category = 'service' WHERE category IN ('addon', 'service')
Two more shorthands worth having now. BETWEEN 10 AND 50
covers a range and includes both ends. LIKE
'Pro%' matches text by pattern, where % means "any
run of characters".
Your task: return name,
category and price for every product that is
an addon or a service.
query.sql
PostgreSQLCtrl↵ to run
Hint
WHERE category IN ('addon', 'service') — brackets around the list, single quotes around each value, comma between them.
Output
The exceptions report
To do
Finance wants every order that needs a human to look at it. An order
qualifies if it did not complete — anything whose status
is not paid — or if it did complete but for a trivial amount,
under 15 dollars, because those are usually a mistake.
Your task: from orders, return
order_id, user_id, amount and
status for every order matching either condition.
Seven orders qualify. Read the two conditions carefully before you write
them: one of them is about status, the other is about amount, and they are
joined by or, not and.
query.sql
PostgreSQLCtrl↵ to run
Hint
status <> 'paid' OR amount < 15 — <> is "not equal". An order that is both unpaid and small still appears once, not twice.