SQL WHERE Clause: All Operators Explained With Real Examples

After reading this, you’ll be able to filter query results using comparison operators, logical operators, pattern matching, range checks, and NULL-safe comparisons — and you’ll know which one to reach for depending on what your data actually looks like. That last part matters more than memorizing syntax, because the WHERE clause has more small traps than most people expect, particularly around NULL values and pattern matching. Let’s go through the questions that come up most often when people are learning to filter data properly.

What does the WHERE clause actually do?

WHERE filters rows out of your result set based on a condition you specify. If a row satisfies the condition, it stays. If it doesn’t, it’s excluded before you ever see it.

It runs after your FROM clause has assembled the data (including any JOINs) but before grouping or sorting happens. That ordering matters: WHERE can’t reference an aggregate result like a SUM or COUNT, since those haven’t been calculated yet at the point WHERE is evaluated. Filtering on aggregated values requires HAVING instead, which is a separate topic, but it’s worth knowing WHERE isn’t the tool for that job.

What are the basic comparison operators, and how do they work?

The core set is small: equals (=), not equal (<> or !=, depending on the database), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=).

These work on numbers exactly the way you’d expect — WHERE price > 100 keeps only rows where price exceeds 100. They also work on dates, comparing chronologically, and on text, comparing alphabetically (or based on whatever collation your database uses).

One detail that trips people up: <> and != mean the same thing in most databases, but not every database supports both spellings. If you’re writing SQL that needs to run across different systems, <> is the safer, more universally supported choice.

How do AND, OR, and NOT combine multiple conditions?

AND requires every condition to be true for a row to survive. OR requires at least one of the conditions to be true. NOT reverses whatever condition follows it, keeping rows that would otherwise have been excluded.

WHERE category = ‘Electronics’ AND price > 500 keeps only electronics priced above 500 — both conditions have to hold simultaneously. WHERE category = ‘Electronics’ OR category = ‘Furniture’ keeps rows from either category, since only one side needs to be true.

Mixing AND and OR in the same condition without parentheses is where most bugs start. WHERE category = ‘Electronics’ AND price > 500 OR status = ‘Clearance’ doesn’t read the way most people expect at a glance — AND typically binds tighter than OR, so this evaluates as (category = ‘Electronics’ AND price > 500) OR status = ‘Clearance’, which pulls in every clearance item regardless of category or price, even if that wasn’t the intent. Wrapping the conditions explicitly in parentheses — WHERE category = ‘Electronics’ AND (price > 500 OR status = ‘Clearance’) — removes the ambiguity and makes the intended logic visible to anyone reading the query later, including you in six months.

How does BETWEEN work, and is it inclusive?

BETWEEN checks whether a value falls within a specified range, and it’s inclusive on both ends. WHERE price BETWEEN 100 AND 200 keeps rows where price is 100, 200, or anything in between — neither boundary gets excluded.

This is a common source of off-by-one mistakes, especially with dates. WHERE order_date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’ looks like it covers all of January, but if order_date includes a time component, a timestamp of ‘2026-01-31 14:00:00’ is technically greater than ‘2026-01-31 00:00:00’ and would be excluded, since BETWEEN treats the upper bound as an exact value, not “any time on that day.” For date ranges involving timestamps, it’s often safer to write the condition as order_date >= ‘2026-01-01’ AND order_date < ‘2026-02-01’, which sidesteps the ambiguity entirely.

How does IN simplify a long list of OR conditions?

IN lets you check a column against a list of values in one clean condition, instead of chaining several OR statements together. WHERE category IN (‘Electronics’, ‘Furniture’, ‘Toys’) is equivalent to WHERE category = ‘Electronics’ OR category = ‘Furniture’ OR category = ‘Toys’, but it’s shorter, and it scales better as the list grows.

NOT IN reverses this, keeping rows where the value doesn’t match anything in the list. It behaves as expected with a clean list of values, but it has one well-known trap: if the list comes from a subquery that happens to return a NULL value, NOT IN can silently return zero rows for the entire query, even when you’d expect matches. This happens because comparing anything to NULL produces an unknown result rather than a clear true or false, and that single unknown poisons the whole NOT IN comparison. If you’re filtering against a subquery, NOT EXISTS is usually the more reliable choice for this exact reason.

How do I match partial text with LIKE?

LIKE compares text against a pattern using two wildcard characters: percent (%), which matches any sequence of characters (including none at all), and underscore (_), which matches exactly one character.

WHERE name LIKE ‘A%’ finds every name starting with A. WHERE name LIKE ‘%son’ finds every name ending in “son.” WHERE name LIKE ‘%an%’ finds “an” anywhere in the name at all, since wildcards on both sides mean the match can start and end anywhere. WHERE code LIKE ‘A_1’ matches a three-character code starting with A, ending in 1, with exactly one character in between.

Two things worth knowing here. First, LIKE is case-sensitive in some databases and case-insensitive in others by default, so test this against your specific system rather than assuming. Second, a leading wildcard like ‘%son’ usually prevents the database from using a standard index efficiently, since it has to scan for a match that could start anywhere in the string. On a large table, that can turn into a meaningfully slower query, and it’s worth knowing before you’re debugging a report that suddenly takes ten times longer than expected.

Why does my WHERE clause miss rows that have NULL values?

This catches nearly everyone at some point. NULL represents an unknown or missing value, and standard comparison operators can’t evaluate it the way they evaluate a normal value. WHERE status = NULL will not return rows where status is NULL — it returns nothing at all, because “does this unknown value equal NULL” is itself an unknown, not a true.

To check for NULL, you need IS NULL or IS NOT NULL specifically. WHERE status IS NULL finds rows where that column has no value; WHERE status IS NOT NULL finds rows where it does. Neither of these is a comparison in the usual sense — they’re a dedicated test for the presence or absence of a value.

This becomes especially important once you’re combining conditions. WHERE status <> ‘Cancelled’ looks like it should return every row that isn’t cancelled, but it will silently exclude any row where status is NULL, since comparing NULL to ‘Cancelled’ with <> produces the same unknown result as any other comparison against NULL. If you want cancelled orders excluded but rows with a missing status kept, you need something like WHERE status <> ‘Cancelled’ OR status IS NULL written explicitly.

What’s the difference between WHERE and HAVING?

WHERE filters individual rows before any grouping happens. HAVING filters groups after GROUP BY has produced its summary rows, and it’s the only place you can filter on an aggregate value like COUNT(*) or SUM(amount).

WHERE amount > 100 restricts which rows even get included in a grouped calculation. HAVING SUM(amount) > 10000 restricts which resulting groups show up in the output, based on a total that only exists after grouping. Trying to use SUM(amount) > 10000 inside a WHERE clause will fail, since that aggregate value doesn’t exist yet at the point WHERE runs.

Can I combine multiple operator types in one WHERE clause?

Yes, and most real-world queries do exactly this. A single WHERE clause might combine a range check, a list check, a pattern match, and a NULL check all at once, connected with AND and OR and organized with parentheses to keep the logic unambiguous.

WHERE category IN (‘Electronics’, ‘Furniture’) AND price BETWEEN 50 AND 500 AND (description LIKE ‘%sale%’ OR discount IS NOT NULL) is a realistic example: restrict to two categories, restrict to a price range, and then require either a sale mention in the description or a non-null discount value. Reading it piece by piece — rather than trying to parse the whole line at once — is usually the fastest way to verify it says what you meant it to say.

Quick Reference: Which Operator Fits Which Situation

Keep this table nearby the next time a WHERE clause returns fewer rows than expected — the cause is usually one of these operators behaving slightly differently than assumed, most often the NULL comparison rule.

Is a WHERE clause returning fewer rows than you expect, or behaving oddly with NULLs? Share the condition you’re using and I can walk through exactly what’s happening and how to fix it.