The WHERE clause is how you tell a database which rows you actually want. Master it and you control every query result you will ever ask for: one row, a filtered range, a search by pattern, or a careful test for missing data. This guide walks through every WHERE tool with real, runnable SQL and the results it returns.
What this guide covers
What WHERE does and where it runs
A table holds many rows. WHERE is the filter that decides which of those rows survive into the result. The database looks at every candidate row, evaluates your condition against it, and keeps the row only when the condition is true. Rows for which the condition is false, or unknown, are dropped. That single idea sits behind almost every query you will write, so it is worth learning the operators well. For the wider family of query keywords, see the data query language commands.
WHERE is not the first thing the database does. SQL is written in one order but processed in another. Understanding that order explains several rules that otherwise look arbitrary, such as why you cannot use a SELECT alias inside WHERE.
| Step | Clause | What happens |
|---|---|---|
| 1 | FROM | Read the source tables and joins |
| 2 | WHERE | Keep only rows that match the condition |
| 3 | GROUP BY | Collapse rows into groups |
| 4 | HAVING | Filter the groups |
| 5 | SELECT | Pick and compute the output columns |
| 6 | ORDER BY | Sort the final rows |
Because WHERE runs at step 2, before SELECT at step 5, it filters raw rows and cannot see any alias or aggregate you define later. That is the key difference from HAVING, which we return to below. A minimal example shows the shape of the clause:
SELECT name, city, age
FROM customers
WHERE city = 'Berlin';name city age
Anna Vogel Berlin 34
Karl Weiss Berlin 29Comparison operators
The engine of the WHERE clause is the comparison. Each one asks a yes or no question about two values and returns a truth value. There are six you will use constantly, and they work on numbers, text, and dates alike.
| Operator | Meaning | Example | True when |
|---|---|---|---|
| = | Equal to | status = 'paid' | status is exactly paid |
| <> | Not equal to | status <> 'paid' | status is anything but paid |
| < | Less than | price < 100 | price is below 100 |
| > | Greater than | price > 100 | price is above 100 |
| <= | Less than or equal | age <= 18 | age is 18 or below |
| >= | Greater than or equal | age >= 21 | age is 21 or above |
The portable "not equal" operator is <>, and most engines also accept != as a synonym. Text comparisons run alphabetically, so name > 'M' keeps names from M onward. For a deeper tour of every operator SQL offers, see the guide to SQL operators.
SELECT name, price, stock
FROM products
WHERE price >= 10
AND price < 50
AND stock <> 0;AND, OR, NOT and precedence
One comparison is rarely enough. Logical operators combine conditions so you can express a compound rule. There are three, and they behave exactly as the words suggest.
- AND is true only when both sides are true. Use it to narrow results.
- OR is true when at least one side is true. Use it to widen results.
- NOT inverts a condition, turning true into false and back again.
-- AND narrows: both must hold
SELECT * FROM users WHERE active = 1 AND country = 'US';
-- OR widens: either may hold
SELECT * FROM users WHERE country = 'US' OR country = 'CA';
-- NOT inverts the condition that follows
SELECT * FROM users WHERE NOT active = 1;When you mix AND and OR in one clause, order matters, and this is where beginners lose hours. The rule is that AND binds tighter than OR, so the database evaluates all the AND groups first. In the query below, the author wanted active accounts on either plan, but the grouping quietly lets inactive team accounts through.
SELECT * FROM accounts
WHERE active = 1 AND plan = 'pro' OR plan = 'team';
-- reads as: (active = 1 AND plan = 'pro') OR plan = 'team'
-- inactive 'team' accounts sneak inSELECT * FROM accounts
WHERE active = 1 AND (plan = 'pro' OR plan = 'team');Tip: parentheses cost nothing and remove all doubt. Whenever a condition mixes AND with OR, wrap the OR group in parentheses so the query says exactly what you mean. Reviewers and your future self will thank you.
IN and BETWEEN
Two keywords act as clean shorthands for patterns you would otherwise spell out with several comparisons. They do nothing new, but they read far better.
IN
IN checks whether a value matches any item in a list, replacing a long chain of OR conditions. Negate it with NOT IN.
-- clear and short
SELECT * FROM users WHERE country IN ('US', 'CA', 'GB');
-- exactly equivalent, but noisier
SELECT * FROM users
WHERE country = 'US' OR country = 'CA' OR country = 'GB';
-- the list can even come from a subquery
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = 1);BETWEEN
BETWEEN tests whether a value falls inside a range, and the range is inclusive of both ends. So price BETWEEN 10 AND 50 is identical to price >= 10 AND price <= 50. That inclusive behaviour is the detail people forget, and it matters most with dates, which we cover shortly.
-- these two WHERE clauses are the same
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE price >= 10 AND price <= 50;LIKE and wildcards
LIKE compares text against a pattern rather than an exact string. It uses two wildcard characters. The percent sign matches any run of characters, including none at all. The underscore matches exactly one character. Combine them to describe almost any shape of text.
| Pattern | Wildcard | Matches | Does not match |
|---|---|---|---|
| 'a%' | % | apple, avon, a | banana |
| '%son' | % | Johnson, Watson | Sonata |
| '%test%' | % | latest, testing | tset |
| '_at' | _ | cat, bat, hat | at, chat |
| 'c_t' | _ | cat, cot, cut | cart, ct |
-- names that start with Ma
SELECT name FROM customers WHERE name LIKE 'Ma%';
-- email addresses at gmail
SELECT email FROM customers WHERE email LIKE '%@gmail.com';
-- five letter codes ending in X
SELECT code FROM products WHERE code LIKE '____X';Two practical notes. Whether LIKE is case sensitive depends on the database and its collation: MySQL is usually case insensitive by default, while PostgreSQL is case sensitive and offers ILIKE for a case insensitive match. And a pattern that begins with a wildcard, such as '%son', cannot use an ordinary index, so leading wildcards on large tables can be slow.
Testing for NULL
NULL is SQL's marker for a missing or unknown value, and it follows its own rules inside WHERE. The single most important one is that you cannot test for NULL with =. Any comparison with NULL returns unknown, never true, so = NULL silently matches zero rows. You must use the dedicated IS NULL and IS NOT NULL operators.
SELECT * FROM users WHERE middle_name = NULL;
-- returns 0 rows: anything = NULL is never trueSELECT * FROM users WHERE middle_name IS NULL;
-- IS NULL / IS NOT NULL test for the markerWatch out: a NULL also quietly changes NOT IN. If the list inside NOT IN contains even one NULL, the whole condition can return no rows. There is a full explanation in the guide to SQL NULL values.
Filtering dates and ranges
Dates are just ordered values, so the comparison operators work on them directly. Earlier is less than later, which makes order_date >= '2026-01-01' keep this year onward. The one trap is time. A column of type datetime or timestamp carries hours and minutes, and that breaks a naive BETWEEN.
-- misses orders placed later on the 31st
SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';
-- '2026-01-31' means midnight, so the day is cut short-- half open range covers the whole month safely
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2026-02-01';The half open pattern, greater than or equal to the start and strictly less than the next boundary, is the reliable way to filter a period. It includes every moment of the last day without you having to guess at a time like 23:59:59. It also keeps the column untouched, which matters for performance in the next section.
WHERE vs HAVING
Both WHERE and HAVING filter, so they are easy to confuse, but they run at different stages and see different things. WHERE filters individual rows before any grouping. HAVING filters groups after GROUP BY has run, which means it can test aggregate functions such as COUNT or SUM that do not exist yet when WHERE runs.
| Aspect | WHERE | HAVING |
|---|---|---|
| Runs | Before GROUP BY | After GROUP BY |
| Filters | Individual rows | Grouped results |
| Can use aggregates | No | Yes |
| Can use a plain column | Yes | Only if grouped |
| Typical job | Cut rows early | Keep or drop whole groups |
The example below uses both in one query. WHERE removes cancelled orders row by row first, then GROUP BY totals each customer, and finally HAVING keeps only the customers whose total passes the threshold.
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status <> 'cancelled' -- filter rows
GROUP BY customer_id
HAVING SUM(amount) > 1000; -- filter groupscustomer_id total
14 2450.00
27 1310.00A good habit is to push as much filtering as possible into WHERE, because cutting rows early means fewer rows to group and less work overall. Reserve HAVING for conditions that genuinely need an aggregate. The companion guide to ORDER BY, GROUP BY and HAVING goes deeper on grouping.
Sargability and performance
A condition is called sargable when the database can use an index to satisfy it. Sargable conditions can be fast even on huge tables. The most common way to accidentally destroy sargability is to wrap the column in a function. Once the column sits inside a function call, the index on the raw column can no longer be used, and the engine falls back to scanning every row.
-- YEAR() hides the column, index cannot help
SELECT * FROM orders
WHERE YEAR(created_at) = 2026;-- plain column on the left, range on the right
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';Both queries return the same rows, but only the second can use an index on created_at. The rule generalises: keep the indexed column bare on one side of the comparison and put the computation on the other side. The same logic explains why a leading wildcard in LIKE is slow. Both engines document how the optimizer decides what it can use in the MySQL WHERE optimization guide and the PostgreSQL manual on table expressions and the WHERE clause.
The chart below gives a rough sense of how much each habit tends to help WHERE performance on a large table. Indexing a filtered column and writing sargable predicates are the two biggest wins.
Recap at a glance
The WHERE clause is a small vocabulary that gives you total control over which rows a query returns. Here is the whole toolkit in one place.
| Tool | Use it to | Example |
|---|---|---|
| = <> < > <= >= | Compare two values | price >= 10 |
| AND OR NOT | Combine or invert conditions | a = 1 AND (b = 2 OR c = 3) |
| IN | Match any value in a list | country IN ('US','CA') |
| BETWEEN | Match an inclusive range | price BETWEEN 10 AND 50 |
| LIKE | Match a text pattern | name LIKE 'Ma%' |
| IS NULL | Test for a missing value | middle_name IS NULL |
Remember the rules that trip up newcomers: parenthesise any mix of AND and OR, use IS NULL rather than = NULL, prefer half open ranges for dates, and keep the filtered column bare so an index can help. Keep the SQL cheat sheet handy while these become second nature, and when you are ready to build real fluency, work through the structured SQL beginner course.