04 — WHERE (Part 1: Syntax)

One line: WHERE filters rows — it keeps only the rows that meet your condition(s). SELECT + WHERE are the backbone of nearly every query you'll write.

The idea in plain English

SELECT chooses columns. WHERE chooses rows. Together: "give me these columns, but only for the rows that match this condition."

"Show me the name and choice, but only where the name is David."

WHERE goes immediately after FROM.

SELECT col_1, col_2
FROM   table_name
WHERE  condition;

The operators

Comparison operators (compare a column to a value)

Two ways to write "not equal": != (common) and <> (the SQL standard). Both work in Postgres.

Logical operators (combine conditions)

• AND — both must be true
• OR — at least one must be true
• NOT — flips a condition (true ↔ false)

These operators are standard across every major SQL engine, not Postgres-only.

Examples

-- One string condition. Strings use SINGLE quotes.
SELECT name, choice
FROM   table
WHERE  name = 'David';

-- Filter on one column, still return others (usually what you want)
SELECT name, choice
FROM   table
WHERE  name = 'David';     -- returns name + choice for every David

-- Multiple conditions with AND (both must hold)
SELECT name, choice
FROM   table
WHERE  name = 'David' AND choice = 'Red';

You'll almost always SELECT more columns than you filter on — filtering on name but only returning name isn't very useful; you want name, choice.

Gotchas that matter (read these) ⚠️

1. Single quotes for values, double quotes mean something else. In Postgres, 'David' is the string David. "David" is treated as an identifier (a column/table name) — so WHERE name = "David" errors with column "David" does not exist. Always single-quote text values.

2. Equality is one =, not ==. Coming from Python/JavaScript, you'll reach for ==. In SQL it's a single =. == is a syntax error.

3. String comparison is case-sensitive. WHERE name = 'david' will not match David. (Later you'll use ILIKE or LOWER(name) = 'david' for case-insensitive matching.)

4. AND binds tighter than OR. This silently changes results. A OR B AND C is read as A OR (B AND C), not (A OR B) AND C. When you mix them, use parentheses to say exactly what you mean — see Challenge 3.

5. You can't compare to NULL with =. WHERE return_date = NULL returns nothing — NULL is never "equal" to anything, even itself. Use IS NULL / IS NOT NULL (a later lesson). Just tuck this away for now.

Challenges

All use the DVD Rental database, film table (title, rating, rental_rate, length, replacement_cost…).

Challenge 1 — Single condition

Return the title and rental_rate of every film rated R.

SELECT title, rental_rate
FROM   film
WHERE  rating = 'R';

Challenge 2 — Two conditions (AND)

Return the title of films that are rated R and cost more than $2 to rent.

SELECT title
FROM   film
WHERE  rating = 'R' AND rental_rate > 2;

Challenge 3 — Spot the bug 🔎 (precedence)

A teammate wants: films that are rated R or PG-13, and cost more than $3. They write:

SELECT title, rating, rental_rate
FROM   film
WHERE  rating = 'R' OR rating = 'PG-13' AND rental_rate > 3;

They get cheap R-rated films in the results, which they didn't expect. Why — and how do you fix it?

WHERE rating = 'R' OR (rating = 'PG-13' AND rental_rate > 3);

SELECT title, rating, rental_rate
FROM   film
WHERE  (rating = 'R' OR rating = 'PG-13') AND rental_rate > 3;

30-second recap