WHERE, AND/OR/NOT, IN, BETWEEN — Keeping Only the Rows You Need

The situation this lesson prepares you for

Last lesson ended on a promise: every condition you write in WHERE is a comparison, so everything you learned about types is about to matter. Here is where it pays.

Your boss asks: "which of our customers are in Germany?" You know how to read the customers table — SELECT "CompanyName", "City" FROM "Customers" — but that hands you all 25 customers, and now you are reading down the Country column with your finger like it is a phone book. 25 rows you would survive. 25,000 and you are there all week.

The database should do that scanning, not you. That is what WHERE is: a test, applied to every row. Rows that pass come back; rows that fail are thrown away. Every operator in this lesson — =, <>, AND, OR, NOT, IN, BETWEEN, LIKE — is just a different kind of test.

Every query below runs on the Northwind database on PostgreSQL. Type each one into CloudBeaver and compare the result with the table under it. If that is not set up yet, the Learn Data guide gets it running.

Keep only the rows that pass a test

WHERE goes right after FROM, and the test reads almost like English:

SELECT "CompanyName", "City"
FROM "Customers"
WHERE "Country" = 'Germany';

11 rows out of 25. The other 14 customers still exist — the filter did not touch the table, it only decided what came back to you. That distinction matters: WHERE shapes the answer, never the data.

Notice the quotes doing two different jobs: "Country" in double quotes is the name of a column, 'Germany' in single quotes is a text value. You met that rule in the SELECT lesson; WHERE is where you will use it constantly, because every text value you compare against goes in single quotes.

Text comparisons are exact

Try the same query with 'germany':

SELECT "CompanyName", "City"
FROM "Customers"
WHERE "Country" = 'germany';

Zero rows. No error, no warning — just an empty result, because no row's Country is exactly germany with a small g. SQL compares text character by character, capitals included.

So when a filter comes back suspiciously empty, check the value's spelling and case first. That is the usual cause.

There is one other, and it is worth knowing the name of now even though it gets a lesson of its own later in this series: a column with nothing recorded in it never matches =. Not even = NULL. You test for that with IS NULL. Park the 2 words for now — if the spelling and the capitals are right and the result is still empty, that is where to look next.

Not equal: <>

The opposite test — everyone who is not in Germany:

SELECT "CompanyName", "Country"
FROM "Customers"
WHERE "Country" <> 'Germany';

14 rows — and 11 Germans plus 14 others is the whole table again. That add-up check takes 2 seconds in your head and it catches real mistakes. You will also see != in other people's queries: it is the same operator, and every database in this course accepts both. <> is the standard ANSI spelling.

Numbers stand alone — no quotes

Text needs quotes; numbers do not. Products cheaper than 10:

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE "UnitPrice" < 10;

2 of the 20 products. Writing '10' in quotes would ask the database to compare a price against a piece of text — which is exactly the trap the last lesson ended on, where '9' > '10' comes back true. If the column holds numbers, compare it with a number.

The comparison family is what you would expect: <, >, <=, >=, and they work on numbers, text and dates alike.

AND narrows, OR widens

Chain 2 tests with AND and a row must pass both. Cheap beverages — category 1 is Beverages, straight from the Categories table you printed two lessons ago:

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE "CategoryID" = 1
  AND "UnitPrice" < 10;

1 row. 3 products are beverages, 2 products are under 10 — but only Guarana Fantastica is both. Each AND you add makes the answer smaller. OR does the opposite: a row passes if either side passes, so each OR makes it bigger.

Hold on to both of those, because the next section is about what happens when they meet.

The trap: AND grabs before OR

This is the one thing in this lesson that silently produces wrong answers in real reports. Say the requirement is: "beverages or condiments, under 15". The obvious translation looks right and is wrong:

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE "CategoryID" = 1 OR "CategoryID" = 2 AND "UnitPrice" < 15;

4 rows — and look at the prices: Chai at 18 and Chang at 19 are over the cap you asked for. Nothing failed, no error was raised, the query is perfectly legal. It simply answers a different question from the one you asked it.

SQL evaluates AND before OR, the same way arithmetic does × before +, so the engine read your test as:

"CategoryID" = 1   OR   ("CategoryID" = 2 AND "UnitPrice" < 15)

Your OR ended up owning only the condiments half, and every beverage walked in free at any price. Parentheses say what you actually meant:

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE ("CategoryID" = 1 OR "CategoryID" = 2)
  AND "UnitPrice" < 15;

2 rows, every price under 15. The rule to carry away is not the precedence table — it is: the moment one WHERE mixes AND with OR, parenthesize. Even when the default happens to be right, because the next person to read it cannot tell that you checked.

NOT flips a test

NOT turns a test inside out, and it pairs naturally with parentheses — flip the whole group:

SELECT count(*)
FROM "Products"
WHERE NOT ("CategoryID" = 1 OR "CategoryID" = 2);

The answer is 14: everything except the beverages and condiments, 14 of the 20 products. (count(*) just counts the surviving rows instead of printing them — aggregates get their own lesson soon.)

Your turn, before we go on

Run the fixed query in CloudBeaver, and then take the parentheses out and run it again:

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE ("CategoryID" = 1 OR "CategoryID" = 2)
  AND "UnitPrice" < 15;

2 rows become 4, and Chai walks straight back in at 18. Breaking a query on purpose, and watching the bug arrive under your own hands, is the fastest way to remember where it lives.

IN: one test against a list

"Customers in Mexico, Venezuela or Argentina" could be 3 = tests glued with OR. IN says it in one breath:

SELECT "CompanyName", "Country"
FROM "Customers"
WHERE "Country" IN ('Mexico', 'Venezuela', 'Argentina');

5 customers. And the flip side, NOT IN, keeps everyone the list does not name:

SELECT count(*)
FROM "Customers"
WHERE "Country" NOT IN ('Mexico', 'Venezuela', 'Argentina');

20 — 25 minus the 5 above. The moment you catch yourself writing the same column with = … OR … = … OR … =, that is IN asking to be used.

BETWEEN keeps a range — and includes both ends

Products priced from 10 to 20. Before you run it: Aniseed Syrup costs exactly 10. Is it in or out?

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE "UnitPrice" BETWEEN 10 AND 20;

9 rows, and Aniseed Syrup is in. BETWEEN 10 AND 20 means >= 10 AND <= 20; both ends are included, always, on every database you will meet. If you want the ends out, say so with strict comparisons:

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE "UnitPrice" > 10
  AND "UnitPrice" < 20;

8 rows now — Aniseed Syrup, sitting exactly on the boundary, dropped out. Neither query is more correct; they answer different questions. The skill is noticing that a row can sit on an end, and deciding on purpose whether it belongs. Nobody reading the finished report would ever spot the difference.

The date habit that never miscounts a month

Back in episode 00 you ran a query on faith. Now you can read every character of it:

SELECT count(*)
FROM "Orders"
WHERE "OrderDate" >= DATE '2024-06-01'
  AND "OrderDate" <  DATE '2024-07-01';

4 — the same 4 June orders as your very first query. The shape is called a half-open range: greater-or-equal the first day, strictly before the first day of the next month.

Why not BETWEEN DATE '2024-06-01' AND DATE '2024-06-30'? On this data it happens to return 4 as well — every Northwind order lands exactly at midnight. But OrderDate is a timestamp, and an order stamped 2024-06-30 14:00 is after midnight on the thirtieth, so BETWEEN would silently drop it. The half-open shape has no such edge: it works for dates and timestamps alike, for 28-, 30- and 31-day months, with no leap-year thinking. Make >= first, < next your reflex for every date range you ever write.

LIKE: a taste of patterns

One more test belongs in the family. LIKE compares text against a pattern, where % stands for "anything here":

SELECT "ProductName"
FROM "Products"
WHERE "ProductName" LIKE 'Ch%';

'Ch%' reads: starts with Ch, then anything. Put % on both sides — '%an%' — and it means contains. Like every text comparison it is case-exact: 'ch%' finds nothing here. Patterns go deeper than this (there is also _ for exactly one character), but % at the start, end or both covers most of what you will reach for.

What you can do now

This morning you could print a table. Now you can question one:

1. One test: WHERE "Country" = 'Germany' — text in 'single quotes', numbers bare.
2. Combined tests: AND narrows, OR widens — and parentheses the moment they mix.
3. A list: IN ('A', 'B', 'C'), and NOT IN for the complement.
4. A range: BETWEEN (ends in) or > … AND < … (ends out) — decide on purpose.
5. Dates: >= first day, < first day of next — every time.
6. A pattern: LIKE 'Ch%', and '%an%' for contains.

Next lesson: putting the survivors in order and keeping the top of the list, with ORDER BY and LIMIT — who is your #1 customer?

The koans: now you write the queries

So far the queries were handed to you. The koans are where you write them. Each koan is a tiny fill-in-the-blank exercise: a failing test with a ___ where the answer belongs. Fill it in, run it, watch red turn green — and the next one unlocks.

cd c:/DataPallas/db/datazeus
.\zeus.bat koans learnsql series1 _10

On macOS or Linux, run ./zeus.sh koans learnsql series1 _10 from the same folder. The 3 arguments are course, series and episode.

Says the lesson isn't there? Your copy of DataZeus predates this lesson — run .\zeus.bat update (or ./zeus.sh update) once to fetch the latest lessons, then run the command again. Koans you have already solved are kept.

The file you edit is:

c:\DataPallas\db\datazeus\tests\src\koans\groovy\datazeus\learnsql\series1\_10\WhereFilteringKoans.groovy

Open it in any editor — Notepad++, VS Code, whatever you have. Each koan has a ___ where the answer belongs. Replace one, save, re-run the command above, and watch that koan go green.

13 koans, 1 for each idea above and in the same order — but asking different questions, so you write the SQL instead of copying it back. They run on DuckDB: a single file, no server to start.