Filtering with WHERE

"Show me all the films" is rarely the question Lora actually has. Her real questions come with conditions attached: the PG films, the films under 100 minutes, the customers who joined this month. WHERE is the clause — the part of a query — that narrows the answer from everything down to exactly the rows that matter.

WHERE also gives you your first taste of thinking the way the database thinks. It doesn't scan the table looking for a vibe; it takes one true-or-false test and runs it against every row, one row at a time. True, the row stays in the answer. False, the row is dropped. Adopt that row-by-row picture now and everything later in the book becomes legible.

One Test per Row

Here is the smallest possible filter. In English: show the title and rating of each film, but only where the rating equals PG. In SQL:

SELECT title, rating
FROM films
WHERE rating = 'PG';

The database walked all three film rows, asked each one "is your rating PG?", kept the two that said true, and dropped Night Bus, whose PG-13 said false. Think of a door check at a venue: each guest — each row — is checked against the same rule, and the rule never changes mid-line. Map the picture, keep the mechanics: one condition, tested per row, keep or discard. That's WHERE, and we'll use the real terms from here.

The Comparison Toolkit

Equality is one test among several. The comparison operators are mostly the ones you know from school math: = for equals, <> for not-equals, and <, >, <=, >= for the orderings. Films shorter than 100 minutes is WHERE runtime_min < 100; everything except PG films is WHERE rating <> 'PG'. The only surprise in the set is that not-equals is written with angle brackets rather than an exclamation mark, though many engines accept != too.

Notice the quoting: 'PG' wears quotes, 100 doesn't. Text values always go in single quotes, so the database can tell the word from a column name; numbers stand bare, exactly as Chapter 2's types promised they could. One honest engine note while we're here: whether 'PG' also matches a lowercase 'pg' depends on the engine — some compare text case-sensitively, some don't — so professionals never rely on it either way. You'll meet the standard trick for taming case in Topic 13.

Combining Tests: AND, OR, NOT

Real questions stack conditions, and SQL stacks them with the words you'd use in English. PG films under 100 minutes becomes two tests joined by AND — a row must pass both to stay:

SELECT title, runtime_min, rating
FROM films
WHERE rating = 'PG' AND runtime_min < 100;

The Long Rain passed the rating test but failed the runtime test at 112 minutes, so only Paper Lanterns survives. OR works the same way but keeps a row if either test passes, and NOT flips a test. So far, so English. The trap arrives when AND and OR meet in one clause.

Say Lora wants PG or PG-13 films that run under 100 minutes. Written the way you'd say it, the query looks right and answers wrong:

SELECT title, runtime_min, rating
FROM films
WHERE rating = 'PG' OR rating = 'PG-13' AND runtime_min < 100;

All three films came back — including a 112-minute one in what was supposed to be an under-100 list. The reason: AND binds tighter than OR, the way multiplication binds tighter than addition. The database read the clause as "rating is PG, or (rating is PG-13 and runtime under 100)" — and every PG film passes that first half no matter how long it runs. Parentheses make the sentence you actually meant:

SELECT title, runtime_min, rating
FROM films
WHERE (rating = 'PG' OR rating = 'PG-13') AND runtime_min < 100;

Now the rating test is settled first, and the runtime test applies to everyone. The professional habit is simple: the moment one WHERE clause contains both AND and OR, add parentheses — even when they turn out to be unnecessary, they make the query say what you meant out loud.

When NULL Strikes

Chapter 2 made a promise about NULL — the marker for "no value here" — and this is where it comes due. A few of the Marquee's customers have no email on file. The obvious query for finding them looks like this, and it returns nothing at all: WHERE email = NULL. Zero rows. Not an error, just silence.

The reason is NULL's whole personality: NULL means unknown, and an unknown value never equals anything — not even another unknown. The test "does this unknown equal NULL?" can't come back true, so no row ever passes it. SQL provides special forms for exactly this question, and they read like honest English:

SELECT name
FROM customers
WHERE email IS NULL;

Three of the Marquee's 212 customers have no email on file, and IS NULL finds them where = NULL found nothing. The mirror form IS NOT NULL keeps only rows where a value is present. File both away as a pair of special verbs reserved for missing data — every SQL beginner runs the = NULL query exactly once, gets the eerie empty answer, and never forgets it.