EasySQL#236 min readJul 11, 2026
WHERE
practice
Hook: Think of WHERE as the bouncer at the door: only rows that match the rule get into the query result.
Question: What is WHERE in SQL?
Answer: WHERE is the clause you use to filter rows before the final result is returned. It keeps only the records that make a condition true, such as price > 100 or status = 'PAID'. One important detail is that NULL does not behave like a normal value, so you must use IS NULL instead of = NULL.
Interview-Ready Answer: I use WHERE to filter rows in a table or join result before the query finishes. It works with conditions like =, <, IN, BETWEEN, and LIKE, and it is evaluated before grouping and aggregation. A key detail is that NULL needs IS NULL, not = NULL, because SQL treats unknown comparisons differently.
%22%20stroke%3D%22%234f46e5%22%20stroke-width%3D%221.5%22%2F%3E%3Ctext%20x%3D%22500%22%20y%3D%22308%22%20text-anchor%3D%22middle%22%20font-size%3D%2215%22%20font-weight%3D%22bold%22%20fill%3D%22%23ffffff%22%20font-family%3D%22Segoe%20UI%2C%20Arial%2C%20sans-serif%22%3EWHERE%20clause%3C%2Ftext%3E%3C%2Fsvg%3E)
What WHERE does
Detailed Explanation: WHERE is the row filter in SQL. Imagine a huge table as a stack of cards; WHERE removes the cards that do not match your rule. The important idea is that it works on rows, not on already-computed summaries.
- SQL starts by finding the rows from
FROMand anyJOINs. WHEREchecks each candidate row against the condition.- If the condition is true, the row stays; if false, it is removed.
- Only after that do later steps like
GROUP BY,HAVING,SELECT,ORDER BY, andLIMIThappen.
This is why WHERE cannot use a column alias from SELECT in most databases: the alias does not exist yet when filtering happens. It also means WHERE usually cannot filter on an aggregate like COUNT(*); that belongs in HAVING.
Three-valued logic and NULL
SQL has three truth values: true, false, and unknown. NULL means “missing or unknown,” so status = NULL is not true; it is unknown, which behaves like false in WHERE. That is why the correct test is status IS NULL or status IS NOT NULL.
Common operators
=,<,>,<=,>=for direct comparison.AND,OR,NOTfor combining rules.INfor a short list of allowed values.BETWEENfor inclusive ranges.LIKEfor simple pattern matching.
One very common gotcha is operator precedence. AND binds tighter than OR, so A OR B AND C means A OR (B AND C), not (A OR B) AND C. When in doubt, add parentheses.
WHERE vs HAVING
| Clause | Filters | Uses aggregates? | Typical stage |
|---|---|---|---|
| WHERE | Rows | No | Before grouping |
| HAVING | Groups | Yes | After grouping |
Use WHERE when you want to reduce the number of raw rows early. Use HAVING when you want to filter after a GROUP BY, such as “groups with more than 10 orders.”
Performance: why WHERE matters
WHERE is not just about correctness; it can be a big performance win. A good, index-friendly predicate is often called sargable (search-arg-able), meaning the database can use an index efficiently. A simple equality or range check on an indexed column can often be close to O(log n + k), where k is the number of matching rows. A full table scan is closer to O(n), which hurts as tables grow.
In real systems, a B-tree index often finds rows in just a few page reads even for millions of rows. But patterns like LIKE '%abc' usually cannot use a normal index well because the leading wildcard hides the start of the value. Also, wrapping an indexed column in a function, such as WHERE DATE(created_at) = ..., often makes the predicate less index-friendly unless you have a matching functional index.
When to use it
Use WHERE whenever you want to narrow a result set early: active users only, orders above a threshold, rows from a date range, or records matching a status list. That early filtering keeps queries easier to read and often faster to run.
Real-World Story: Imagine a checkout service for an e-commerce app. A data analyst needs to see only successful orders over $100 that were created this week, so the dashboard query uses WHERE status = 'PAID' AND amount > 100. This keeps refunded, pending, and test orders out of the report.
What goes wrong when someone misunderstands WHERE? A common bug is writing status = NULL or forgetting parentheses around mixed AND/OR logic. The symptom is either an empty report, or far too many orders showing up. In logs you might see the SQL execute successfully, but the dashboard numbers look suspiciously low or high, and support hears, “Why are canceled orders in the sales chart?”
Another production-style mistake is filtering too late. If you first pull millions of rows and only then try to narrow them in application code, the database does extra work, network traffic grows, and the page may slow down from milliseconds to seconds.
SQL
-- Runnable demo: WHERE filters rows, handles NULL correctly, and shows a precedence trap.
-- This script is written in standard, SQLite-friendly SQL.
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer TEXT,
status TEXT,
amount INTEGER NOT NULL,
created_at TEXT NOT NULL
);
INSERT INTO orders (order_id, customer, status, amount, created_at) VALUES
(1, 'Ava', 'PAID', 120, '2024-01-10'),
(2, 'Ben', 'PENDING', 40, '2024-01-11'),
(3, 'Cara', 'PAID', 75, '2024-01-12'),
(4, 'Drew', NULL, 200, '2024-01-12'),
(5, 'Eli', 'REFUNDED', 120, '2024-01-13'),
(6, 'Fay', 'PAID', 25, '2024-02-01');
-- 1) Simple row filtering: keep only paid orders above 100.
SELECT order_id, customer, amount
FROM orders
WHERE status = 'PAID'
AND amount > 100
ORDER BY amount DESC;
-- 2) Failure path: this looks reasonable, but it returns no rows.
-- In SQL, NULL is not equal to anything, not even another NULL.
SELECT order_id, customer, status
FROM orders
WHERE status = NULL;
-- 3) Correct NULL check: use IS NULL.
SELECT order_id, customer, status
FROM orders
WHERE status IS NULL;
-- 4) IN, BETWEEN, LIKE together: a compact, readable filter.
SELECT order_id, customer, status, amount
FROM orders
WHERE status IN ('PAID', 'PENDING')
AND amount BETWEEN 20 AND 120
AND customer LIKE 'A%'
ORDER BY order_id;
-- 5) Precedence trap: AND is evaluated before OR.
-- This means: paid orders, OR pending orders above 100.
SELECT order_id, customer, status, amount
FROM orders
WHERE status = 'PAID'
OR status = 'PENDING' AND amount > 100
ORDER BY order_id;
-- 6) Correct version with parentheses: now BOTH statuses must meet the amount rule.
SELECT order_id, customer, status, amount
FROM orders
WHERE (status = 'PAID' OR status = 'PENDING')
AND amount > 100
ORDER BY order_id;Follow-up & Tricky Questions:
- When does
WHERErun compared withGROUP BY?WHEREruns before grouping. It removes raw rows first, and thenGROUP BYforms groups from the remaining rows. - Why can’t I use a
SELECTalias inWHERE? Because the alias is created later in SQL’s logical order. The filter happens before the projection step that creates the alias. - What is the difference between
WHEREandHAVING?WHEREfilters rows, whileHAVINGfilters grouped results. If your condition usesCOUNT,SUM, or another aggregate, you usually needHAVING. - How does
NULLaffectWHERE? Comparisons withNULLreturn unknown, so the row is not kept. UseIS NULLorIS NOT NULLinstead. - Can
WHEREhurt performance? Yes, if the condition prevents index use or if you filter too late. A sargable predicate on an indexed column is much faster than scanning every row. - Tricky: Why did
A OR B AND Cnot behave like I expected? BecauseANDhas higher precedence thanOR. SQL reads it asA OR (B AND C), so use parentheses when the meaning matters. - Tricky: Why did
WHERE price = NULLreturn nothing? Because equality does not matchNULL. The correct form isWHERE price IS NULL. - Tricky: Can I filter aggregated values in
WHERE? Not directly. Aggregates are produced afterWHERE, so use a subquery orHAVINGinstead.
Common Mistakes:
- Using
= NULLinstead ofIS NULL. Correction:NULLneeds special handling because it means unknown, not a normal value. - Forgetting parentheses with mixed
AND/OR. Correction: make the intended logic explicit so SQL does not surprise you. - Trying to use aggregates in
WHERE. Correction: filter raw rows inWHEREand grouped results inHAVING. - Wrapping indexed columns in functions too early. Correction: write predicates in an index-friendly way when possible, such as range filters on the column itself.
Memory Hook: WHERE is the bouncer at the door: it checks each row before the rest of the query party starts.
Cheat Sheet:
WHEREfilters rows.- It runs before
GROUP BYandHAVING. - Use
IS NULL, not= NULL. ANDbinds tighter thanOR.- Good filters can use indexes and run much faster.
- Use
HAVINGfor aggregate filters.
Practice Tasks:
- Write a query that returns orders between 50 and 150 with status
PAID. - Rewrite a query with mixed
AND/ORusing parentheses so the intent is obvious. - Change the demo table to find customers whose names start with
Cand have a non-null status.