Filtering with WHERE

Lesson 1 chose how many rows. Lesson 2 chose which columns. This one chooses which rows, and it is the clause you will write more often than any other. WHERE takes a condition, the engine evaluates it once for every row in the file, and rows where the answer is true survive.

It goes after FROM and before LIMIT. That order is fixed, and it is also the order things happen in: the file is read, rows are filtered, and only then does the limit start counting. Which means WHERE region = 'West' LIMIT 5 gives you five western rows, not five rows of which some happen to be western.

The comparison operators

Six of them, and you know five already: =, <, >, <=, >=. The sixth is not-equal, written <> in standard SQL and != in most dialects including this one. Both work here. Pick one and stay consistent.

Note that a single equals sign means comparison, not assignment. If you have written code in almost any other language, your fingers will type == at least once this week and SQL will reject it.

Quotes are not decoration

Text values go in single quotes. Numbers do not. WHERE region = 'West' is correct; WHERE region = West asks the engine to compare the region column against a column called West, which does not exist, and you get an error naming it. That error message is actually helpful once you know what it is telling you.

The reverse mistake is quieter and worse. WHERE units > '20' compares a number against a piece of text. Some databases refuse, some coerce, and at least one will compare them as strings, where '9' is greater than '20' because nine sorts after two. Keep numbers bare.

Text comparison is case sensitive in DuckDB, as it is in Postgres. region = 'west' returns nothing on this file. When you are matching against data typed by humans, that is a real hazard, and the usual fix is to compare lower(region) = 'west' instead.

Null is not a value

The sales file has no missing values, but almost every file you meet later will, so learn this now: an empty cell in a CSV becomes NULL, and NULL means unknown rather than empty. Comparing anything to an unknown gives an unknown, not true, so WHERE satisfaction = NULL matches nothing at all, even on rows where the value is missing. The operator you want is IS NULL, and its opposite is IS NOT NULL. You will use both in lesson 9, on a file that has real gaps in it.

The same trap hides inside not-equal. WHERE status <> 'Closed' silently drops rows where status is null, because unknown is not provably different from 'Closed' either. When that matters, spell it out: WHERE status <> 'Closed' OR status IS NULL.

Read the row count

Under every result grid on this page there is a row count. Get in the habit of reading it before you read the rows. A filter that leaves all one hundred rows in place has not filtered anything, and a filter that leaves zero rows is far more often a typo in a category name than a genuine absence. That two-second check has saved me from more wrong conclusions than any other habit in this course.

Where a filter is doing something surprising, the fastest diagnosis is to select the column you are filtering on and look at the actual values. Trailing spaces, a capital letter you did not expect, a category spelled two ways: all of it is visible in ten seconds and invisible in a summary.

What to look up next

Search for "SQL three-valued logic". It is a short read and it explains, properly, why NULL behaves the way it does in every comparison you will ever write. Understanding it once is worth more than memorising ten special cases.