Filtering Rows with WHERE

Chapter 3 of the 24-chapter SQL for Beginners series read whole tables; chapter 4 narrows them. WHERE is the clause that decides which rows survive a query, and this chapter covers its comparison operators, the logic of AND, OR and parentheses, ranges and lists with BETWEEN and IN, pattern matching with LIKE, and the strange three-valued logic of NULL.

Comparison operators

WHERE takes a condition, and a row either satisfies it or does not. The comparisons are the ones you already know:

• = — equal to. WHERE status = 'paid'
• <> — not equal to; != is accepted by SQLite, MySQL and PostgreSQL, but <> is the standard form.
• <, >, <=, >= — ordering comparisons, which work on numbers, on text, and on dates written as strings.
• IS NULL, IS NOT NULL — the only correct way to test for a missing value; covered near the end of this chapter.

A first filter, cheapest products at or above five hundred:

SELECT name, price FROM products WHERE price >= 500;

Expected output:

Clean Code|899
Python Crash Course|749
Robotics for Kids|649

Three of the six products, in whatever order the table happened to be read — WHERE picks rows, it does not arrange them, so add ORDER BY when the sequence matters (chapter 5). Note that text comparison with = is exact and case-sensitive in SQLite: WHERE name = 'clean code' matches nothing at all, while LIKE would match Clean Code. The negative form does exactly what you would expect — everything except the cancelled order:

SELECT id, status FROM orders WHERE status <> 'cancelled';

Expected output:

1|paid
2|shipped
4|shipped
5|paid
6|paid

Five rows, because the filter removed exactly one. Ordering comparisons follow the same rules on other data types: city > 'C' means alphabetically after “C”, and ISO dates compare correctly because their layout puts the largest unit first, which is a property you will lean on for the rest of the series.

AND, OR and parentheses

Conditions combine with AND (both must hold) and OR (either may hold), negated by NOT. Customers in India or Germany, for instance:

SELECT first_name, city
FROM customers
WHERE country = 'India' OR country = 'Germany';

Expected output:

Aarav|Mumbai
Priya|Delhi
Lena|Berlin

The trap is precedence: AND binds tighter than OR, exactly as multiplication binds tighter than addition. In WHERE a = 1 OR b = 2 AND c = 3 the engine reads a = 1 OR (b = 2 AND c = 3), whether or not that is what you meant. When both operators appear, write the parentheses yourself:

SELECT first_name, city
FROM customers
WHERE (country = 'India' OR country = 'Germany')
  AND city <> 'Delhi';

Expected output:

Aarav|Mumbai
Lena|Berlin

Only Priya was excluded, because the AND condition now applies to both sides of the OR. One pair of parentheses costs nothing and removes the guesswork. NOT follows the same rule — it applies to the condition that comes after it, and NOT (status = 'paid') reads far better than an unbracketed version, especially once the condition grows past one test.

Ranges and lists: BETWEEN and IN

BETWEEN x AND y is shorthand for >= x AND <= y — inclusive at both ends, which surprises people who expect the top bound to be excluded. Dates written in YYYY-MM-DD form compare correctly as text, so a signup window is one condition:

SELECT first_name, signup_date
FROM customers
WHERE signup_date BETWEEN '2025-12-01' AND '2026-01-31';

Expected output:

Lena|2025-12-01
Marc|2025-12-20
Wei|2026-01-12

IN is a short list of exact values, and beats chaining = with OR:

SELECT id, status FROM orders WHERE status IN ('paid', 'shipped');

Expected output:

1|paid
2|shipped
4|shipped
5|paid
6|paid

Five orders — everything except order 3, which was cancelled. IN also accepts subqueries (WHERE customer_id IN (SELECT ...)), which chapter 13 puts to work, and its opposite is NOT IN.

IN is not limited to text; ids are its most common case:

SELECT id, customer_id FROM orders WHERE customer_id IN (1, 3, 5);

Expected output:

1|1
2|1
4|3
5|5

Four orders belonging to three customers, written as one condition instead of three equality tests joined by OR. One warning belongs with NOT IN: if the list ever contains a NULL, the whole test returns unknown for every row and you get nothing back at all — keep NULLs out of the list, or filter them with IS NULL separately.

Pattern matching with LIKE

LIKE compares text against a pattern built from two wildcards: % matches any run of characters (including none) and _ matches exactly one. Every city starting with “T”:

SELECT first_name FROM customers WHERE city LIKE 'T%';

Expected output:

Marc

Marc in Toronto is the only match, and %a% would instead find any city containing an “a”. The underscore counts one character, so LIKE 'Clean_C%' requires a single character after “Clean” — the space — before the rest:

SELECT name FROM products WHERE name LIKE 'Clean_C%';

Expected output:

Clean Code

Case sensitivity is where dialects part company. SQLite’s LIKE ignores ASCII case by default (so the pattern 'clean code' would still find Clean Code), MySQL does the same under its default collation, and PostgreSQL’s LIKE is case-sensitive — use ILIKE there for the behaviour SQLite gives you for free.

Prefix the operator with NOT and the pattern inverts, which is how you ask for the complement of a set:

SELECT name FROM products WHERE name NOT LIKE '%Code%';

Expected output:

The Silent Patient
The Very Hungry Caterpillar
Python Crash Course
Matilda
Robotics for Kids

Five products, with Clean Code removed by the pattern rather than by a second condition. One warning to file away: patterns anchored at the start ('T%') let a database use an index, while a leading wildcard ('%a%') forces it to inspect every row — harmless at six products, painful at six million, and the subject of chapter 19.

NULL is not a value

A NULL column means unknown or absent, and SQL refuses to compare the unknown. Consequences to internalise now:

• = NULL matches nothing — not even the NULL rows. The condition evaluates to unknown, and unknown rows are discarded. Use IS NULL.
• <> NULL also matches nothing — the same reason; use IS NOT NULL instead.
• Every comparison with NULL is unknown — NULL + 1 is NULL, so arithmetic and concatenation quietly propagate it.
• NULLs sort together — in SQLite they come first in ascending order; PostgreSQL puts them last by default and MySQL first, which chapter 5 revisits.

In this series’ sample data every column is populated, so the honest demonstration is an empty result:

SELECT first_name FROM customers WHERE city IS NULL;

Expected output: nothing — zero rows, not an error. The city column was declared without NOT NULL in chapter 2, so a row could arrive with no city; until one does, IS NULL finds nothing.

NULL also refuses to take part in ordinary logic. city IS NULL OR country = 'India' works perfectly, because IS NULL produces a true-or-false answer where = NULL never does. Treat every comparison against NULL as “I do not know”, remember that unknown rows are dropped from results just like false ones, and the behaviour stops feeling arbitrary.

Nullable columns are not automatically a design flaw: an unknown city is better stored as NULL than as an empty string pretending to be a city. Chapter 8 shows where NOT NULL is worth insisting on and where leaving the door open is the honest choice.

Putting filters together

Real conditions layer several of these ideas at once. Non-fiction products with twenty to forty units gathering dust:

SELECT name, stock
FROM products
WHERE category <> 'Fiction'
  AND stock BETWEEN 20 AND 40;

Expected output:

Python Crash Course|22
Matilda|30

Two products qualify: Python Crash Course with 22 units and Matilda with 30 both sit inside the range, while The Very Hungry Caterpillar is above it at 60 and Robotics for Kids below it at 15 — and The Silent Patient never entered the comparison at all, excluded by the first condition. Spreading the conditions over lines with the operators leading is a formatting habit worth stealing — long WHERE clauses read badly when everything is jammed onto one line.

There is a practical reason to push logic into WHERE as early as possible, too. Chapter 3’s evaluation timeline puts that clause first: rows rejected there never reach the select list, the grouping or the sort, so every filter you add is work the rest of the query does not have to do.

Common mistakes

• Writing WHERE city = NULL — it can never be true; the correct test is city IS NULL (and IS NOT NULL for the inverse).
• Forgetting parentheses around OR — AND binds tighter than OR, so a mixed condition quietly filters less than you intended.
• Using = with a pattern — name = '%a%' looks for a row literally named %a%; wildcard matching only happens under LIKE.
• Assuming one case rule everywhere — = is case-sensitive in SQLite while LIKE is not, and PostgreSQL’s LIKE is case-sensitive where you may expect ILIKE.
• Forgetting BETWEEN is inclusive — BETWEEN 1 AND 10 includes 10; exclude the top end with < 10 when you meant to.
• Assuming LIKE respects the case you typed — SQLite ignores ASCII case, so country LIKE 'INDIA' still matches India; write the pattern exactly as you want it on an engine where case matters.

Practice checklist

1. List every product under 700 with price < 700, then write the same set as price BETWEEN 1 AND 699 and compare the row counts.
2. Using NOT IN and NOT (order_date < '2026-02-01'), list the orders that were neither cancelled nor placed before February 2026.
3. List every product whose name contains “Code” with LIKE '%Code%', then search for 'code' in lowercase and explain what SQLite did.
4. Run SELECT first_name FROM customers WHERE city = NULL;, read the empty result, and fix the query with IS NULL.
5. Write one condition with AND and OR together, predict the effect of removing the parentheses, then remove them and check.

What's next?

Rows are filtered but still arrive in whatever order SQLite produced them; next you control sequence and size: Sorting and Limiting Rows.