SELECT chose your columns. Now comes the question every stakeholder actually asks: 'which rows?' Orders over $100. Customers from Germany. Signups in March. Filtering is where SQL stops describing tables and starts answering questions.
What you'll learn
What
The WHERE clause keeps only the rows for which a condition — called a predicate — evaluates to true. Predicates combine comparison operators (=, <>, >, <) with logical operators (AND, OR, NOT) and convenience forms like IN, BETWEEN, and LIKE.
Why
Real tables hold millions of rows; almost no question is about all of them. Without WHERE you would drag entire tables into your notebook and filter in pandas — slow, expensive, and impossible once data outgrows memory. Filtering at the database is the single biggest lever for fast, cheap queries.
Where it's used
Every dashboard filter, every cohort definition, every 'active users last 30 days' metric, every data-quality check compiles down to a WHERE clause.
Where this runs in production
Risk analysts filter payments with predicates like amount > 5000 AND country <> card_country AND status = 'pending' to surface transactions that need human review.
A listings search compiles guest filters into SQL-style predicates: price BETWEEN 80 AND 200 AND bedrooms >= 2 AND city IN ('Lisbon', 'Porto').
Pipeline checks run queries like SELECT COUNT(*) FROM viewing_events WHERE duration_seconds IS NULL to catch broken instrumentation before it poisons recommendations.
Think of WHERE as a bouncer at a club door. Every row walks up, the bouncer checks it against the rule list ('amount over 100? country is Germany?'), and only rows that satisfy the rules get in. The bouncer never modifies anyone — rows are filtered, never changed. And crucially, the bouncer checks every guest one at a time: predicates are evaluated per row.
AND requires both sides true; OR requires at least one; NOT flips the result. AND binds tighter than OR: a OR b AND c means a OR (b AND c). When you mix AND with OR, always add parentheses — precedence bugs return the wrong rows silently, with no error message.
SELECT order_id, amount, country FROM orders WHERE (country = 'DE' OR country = 'FR') AND amount > 100;
Without the parentheses this would return every German order regardless of amount (country = 'DE' OR (country = 'FR' AND amount > 100)). The parentheses make the intent explicit: European orders over 100. Reading tip: indent continuation conditions under WHERE so the logic is scannable.
NULL means 'value unknown/missing' — and comparing anything to unknown yields unknown, not true or false. So discount = NULL never matches, and neither does discount <> NULL. Rows where the predicate is unknown are dropped by WHERE. To test for missing values you must use IS NULL or IS NOT NULL. This is SQL's three-valued logic (true / false / unknown), and it is the #1 source of silently wrong filters.
WHERE runs before data leaves the warehouse. A query that filters 10 million rows down to 3,000 ships 3,000 rows to your notebook; the pandas-first equivalent ships all 10 million and then filters. Pushing predicates into SQL is called predicate pushdown, and it is the default habit of every effective analyst.
-- Case-insensitive pattern match (PostgreSQL) SELECT customer_id, email FROM customers WHERE email ILIKE '%@GMAIL.com';
LIKE is case-sensitive in PostgreSQL ('Anna%' won't match 'anna'). PostgreSQL adds ILIKE for case-insensitive matching; other dialects use LOWER(email) LIKE '%@gmail.com' — which works everywhere. Dialect note: MySQL's LIKE is case-insensitive by default for most collations.
Each row flows through the predicate. Click a node to see what happens at each evaluation outcome — note that unknown is dropped, exactly like false.
The orders table has order_id, customer_id, amount, and status. Find all orders worth more than $150.
The predicate amount > 150 is checked against every row. A row with amount 89.99 evaluates to false and is dropped; a row with 210.00 evaluates to true and is kept. Note the filter references the column name, never SELECT aliases — WHERE runs before the SELECT list is applied.
order_id | amount ----------+-------- 1004 | 210.00 1007 | 380.50 (2 rows)
Your task
You're auditing the orders table (order_id, customer_id, amount, status, country). Return order_id, amount, and country for orders that are: from Germany or France, worth between $50 and $500 inclusive, and NOT cancelled. Fill in the blanks.
order_id | amount | country ----------+--------+--------- 1001 | 250.00 | DE 1004 | 480.00 | FR (2 rows)
Write your solution in the editor on the right, then hit Run.
Which clause removes rows that fail a condition?
Is BETWEEN 10 AND 20 inclusive or exclusive of its endpoints?
The bonus column is NULL for 40 of 100 employees. How many rows does WHERE bonus <> 500 return if exactly 10 employees have bonus = 500?
Which pattern matches email addresses ending in '.org'?
Why, and what is the fix?
The customers table (customer_id, name, email, marketing_opt_in) has NULLs in marketing_opt_in for customers who never answered. Write a query counting customers who have NOT opted in — treating 'never answered' (NULL) as not opted in. Return one column named not_opted_in.
Explain SQL's three-valued logic. How does it affect WHERE clauses?
SQL predicates evaluate to true, false, or unknown — not just true/false. Unknown appears whenever a NULL is involved in a comparison, because NULL means 'value missing', and you can't say whether a missing value equals 5. WHERE keeps only rows whose predicate is true, so unknown rows are dropped exactly like false ones. The practical consequence: a filter like status <> 'cancelled' silently excludes rows where status is NULL, which is rarely what the business question intended. The defensive habits are to profile NULL counts before filtering (COUNT(*) vs COUNT(column)), to use IS NULL / IS NOT NULL explicitly, and to reach for COALESCE when a sensible default exists. I'd also mention the NOT IN trap: a single NULL in the list makes the whole predicate return zero rows.
A query mixing AND and OR returns obviously wrong rows. Walk me through how you'd debug it.
First suspicion is operator precedence: AND binds tighter than OR, so a OR b AND c means a OR (b AND c), and unparenthesized mixes almost always express something different from the analyst's intent. I'd rewrite the predicate with explicit parentheses matching the business sentence, then re-run and compare counts. If it's still off, I decompose: run each atomic condition alone with COUNT(*), then add conditions back one at a time — each AND should monotonically shrink the count, and any surprising jump localizes the bug. Finally I'd check for NULLs in the filtered columns, since dropped-unknown rows are the other classic source of wrong counts. Five minutes of counting beats an hour of staring at the predicate.
When would you use LIKE versus = for text filtering, and what are the performance implications?
= does exact matching and can use a plain B-tree index directly, so it's the right choice whenever you know the full value — statuses, country codes, IDs. LIKE is for partial or pattern matching with % and _ wildcards: suffix checks like '%@gmail.com', prefix checks like 'ORD-2026%'. Performance depends on where the wildcard sits: a leading-wildcard pattern ('%gmail.com') can't use a standard index because the beginning of the string is unconstrained, forcing a full scan, whereas 'ORD-2026%' is index-friendly since the prefix anchors the search. For heavy substring search at scale you'd move to trigram indexes (PostgreSQL pg_trgm) or full-text search rather than LIKE. I'd also flag case sensitivity: PostgreSQL LIKE is case-sensitive, MySQL's usually isn't, so portable code often normalizes with LOWER() on both sides.
Common Mistakes to Avoid
1) Writing column = NULL — it never matches; use IS NULL. 2) Mixing AND and OR without parentheses, silently changing the logic through precedence. 3) Using double quotes for text ('shipped' is a string; "shipped" is an identifier in PostgreSQL). 4) Filtering on a SELECT alias (WHERE total > 100 when total is defined in SELECT) — WHERE runs first and can't see aliases; repeat the expression or use a subquery. 5) Assuming BETWEEN excludes its endpoints — it's inclusive, which double-counts boundary timestamps in date ranges; prefer >= start AND < end for time windows.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: why does NULL = NULL not return true in SQL?' • 'Quiz me with five predicates and rows — I'll predict kept or dropped.' • 'Show me a real bug caused by AND/OR precedence and how parentheses fix it.' • 'Explain the NOT IN with NULL trap with a tiny 3-row example.' • 'Interview mode: grill me on three-valued logic and grade my answer.'
Glossary
Predicate — a boolean expression evaluated per row by WHERE. Comparison operators — =, <> (!=), >, >=, <, <= for comparing values. Logical operators — AND, OR, NOT for combining predicates. Precedence — the binding order of operators; AND before OR. IN — membership test against a list of values. BETWEEN — inclusive range test (>= low AND <= high). LIKE — pattern match; % matches any run of characters, _ exactly one. Wildcard — a pattern character standing in for unknown text. NULL — a missing/unknown value; not zero and not empty string. Three-valued logic — predicates evaluate to true, false, or unknown; WHERE keeps only true. IS NULL / IS NOT NULL — the only correct tests for missing values. COALESCE — returns the first non-NULL argument; used to substitute defaults.
Recommended Resources
• Docs: PostgreSQL manual — 'Comparison Functions and Operators' and 'Pattern Matching' chapters. • Read: 'NULLs in SQL' sections of Use The Index, Luke for how NULL interacts with indexes and predicates. • Practice: take any table you have, profile a nullable column with COUNT(*) vs COUNT(col), then write the same business filter twice — once naive, once NULL-safe — and diff the counts. • Next in DSM: your filters now return the right rows — ORDER BY & LIMIT teaches you to rank them and keep just the top N.
Recap
✓ WHERE evaluates a predicate per row and keeps only rows where it is true. ✓ Comparison operators (=, <>, >, >=, <, <=) combine with AND, OR, NOT — and AND binds tighter than OR, so parenthesize mixed logic. ✓ IN tests list membership, BETWEEN tests an inclusive range, LIKE matches patterns with % and _. ✓ NULL comparisons yield unknown, and WHERE drops unknown rows — use IS NULL / IS NOT NULL. ✓ NOT IN with a NULL in the list matches nothing; filter NULLs first or use NOT EXISTS. ✓ Filter in the database, not in pandas — predicate pushdown is your biggest performance lever. Next up: ORDER BY & LIMIT. You can select the right rows — now you'll sort them, paginate them, and answer every 'top 10' question a stakeholder can throw at you.
Run your code to see the output here.