A student once spent two hours debugging a query that should have returned several hundred rows but returned zero, and the eventual cause was a single misunderstanding about how comparisons work with empty values in SQL — something that, once explained, took thirty seconds to fix.
WHERE clause operators look deceptively simple, and most of them are exactly as straightforward as they appear. But there are several specific edge cases — particularly around NULL handling and pattern matching — that consistently trip up beginners in ways that produce confusing, silent failures rather than clear error messages. This tutorial covers every common operator, with particular attention to those specific traps.
The Basic Comparison Operators
These are the operators most people learn first, and they generally behave exactly as you would expect from basic mathematics.
Equals, written as a single equals sign, checks whether a column’s value exactly matches a specific value you provide. For text comparisons, this check is typically case-sensitive or case-insensitive depending on your specific database system’s default configuration, which is worth verifying for your particular system if exact text matching behavior matters for your query.
Not equals, written as either an exclamation mark followed by an equals sign, or alternatively as a less-than symbol followed by a greater-than symbol depending on the database system, excludes rows matching a specific value while keeping everything else.
Greater than, less than, greater than or equal to, less than or equal to work as you would expect for numeric and date comparisons, using the standard mathematical symbols. For date comparisons specifically, ensure your date values are actually stored as genuine date types rather than text that merely looks like a date, since text-based date comparisons can produce subtly wrong results (comparing dates as if they were alphabetical strings rather than chronological values).
BETWEEN: A Cleaner Way to Write Range Conditions
Rather than writing two separate conditions joined by AND to check whether a value falls within a range (greater than or equal to some minimum, AND less than or equal to some maximum), the BETWEEN operator expresses this same range check more concisely.
Writing a column name, followed by BETWEEN, followed by your minimum value, followed by AND, followed by your maximum value, checks whether that column falls within the specified range, inclusive of both the minimum and maximum values themselves.
An important detail that surprises people: BETWEEN is inclusive on both ends, meaning a value exactly equal to either boundary you specify counts as matching. If you specifically need an exclusive range (strictly greater than the minimum, strictly less than the maximum, excluding the boundaries themselves), BETWEEN is not the right tool, and you need to write out the separate greater-than and less-than comparisons explicitly instead.
IN: Checking Against a List of Values
When you need to check whether a column matches any one of several specific values, writing out several separate equals conditions joined by OR works but becomes unwieldy with more than a few values. The IN operator provides a cleaner shortcut.
Writing a column name, followed by IN, followed by a parenthesized, comma-separated list of values, checks whether that column matches any value within that list. This is functionally equivalent to writing the column equals the first value, OR the column equals the second value, and so on for every value in your list, but considerably more readable and easier to maintain, especially as your list grows longer.
IN also has a negated counterpart: NOT IN, which excludes rows matching any value in your list rather than including them. This works as expected with most data, but has a specific NULL-related trap covered in the NULL section below.
LIKE: Pattern Matching for Text
Exact equals comparisons only help when you know precisely what text you are looking for. LIKE enables pattern-based matching, useful when you need to find values containing, starting with, or ending with a specific substring rather than matching it exactly.
LIKE uses a percent sign as a wildcard representing any sequence of characters (including zero characters). Placing a percent sign before and after your search text checks whether that text appears anywhere within the column’s value. Placing a percent sign only after your search text checks whether the value starts with that specific text. Placing a percent sign only before your search text checks whether the value ends with that specific text.
For example, searching for customer names using LIKE with a percent sign before and after the text “smith” would match “Smith”, “Smithson”, “John Smith”, and any other value containing that substring anywhere within it.
A specific underscore wildcard exists too, representing exactly one single character rather than any sequence. This is used far less often than the percent sign in typical business queries but is worth knowing exists for situations where you need to match a specific pattern length, like finding product codes that follow a known format with one variable character in a specific position.
Performance consideration worth knowing: A LIKE pattern starting with a leading wildcard (percent sign at the very beginning) typically cannot use a standard database index efficiently, since the database cannot know where in the text to start its search. On very large tables, searches using a leading wildcard can be noticeably slower than searches where the wildcard only appears at the end. This becomes relevant once you are working with genuinely large datasets, though it rarely matters for smaller exploratory queries.
The NULL Trap: Why Equals Does Not Work the Way You Expect
This is the specific issue that caused that student’s two-hour debugging session, and it is genuinely one of the most common beginner traps in SQL.
NULL represents a missing or unknown value in a database — not zero, not an empty string, but the explicit absence of any value at all. The critical, counter-intuitive rule is this: you cannot check for NULL using the standard equals operator. Writing a column equals NULL does not work the way it looks like it should, and will not actually match NULL values in that column, even though it appears like it logically should.
Instead, SQL provides a specific dedicated syntax: writing a column name followed by IS NULL checks for the explicit absence of a value, and writing a column name followed by IS NOT NULL checks for the presence of any actual value.
This is not an arbitrary or unnecessarily complicated rule — it stems from how NULL represents an unknown value conceptually rather than a specific comparable value, and a comparison against an unknown quantity in three-valued logic produces a result of “unknown” rather than a clean true or false, which is why standard equals simply does not behave the way beginners expect when NULL is involved.
The related NOT IN trap: If the list of values you provide to a NOT IN condition happens to include a NULL value anywhere within that list (perhaps from a subquery that returned an unexpected NULL), the entire NOT IN condition can unexpectedly return no rows at all for every single row in your table, due to the same three-valued logic issue. This specific trap has caused real production bugs I have personally debugged, and the safest practice is to be cautious whenever your NOT IN list comes from a dynamic subquery rather than a manually typed, known list of values, since you cannot always be certain a NULL has not snuck into that dynamically generated list.
Combining Operators With AND, OR, and Parentheses
As covered briefly in the first SELECT tutorial, AND requires every combined condition to be true simultaneously, while OR requires at least one of the combined conditions to be true. AND is evaluated before OR by default in the absence of explicit parentheses, similar to how multiplication is evaluated before addition in basic arithmetic.
Whenever you are combining more than two conditions, or mixing AND and OR within the same WHERE clause, using explicit parentheses to group your conditions removes any ambiguity about the order of evaluation and makes your intent immediately clear to anyone else reading your query later, including your future self returning to this query after several months away from it.
NOT: Inverting Any Condition
Placing the word NOT directly before any condition inverts its result — what was previously true becomes false, and vice versa. This works in combination with most other operators covered in this tutorial: NOT LIKE, NOT BETWEEN, NOT IN (with the NULL caveat noted above), and so on.
While NOT is genuinely useful in some situations, it is worth knowing that many conditions written with NOT have an equivalent, often more readable, alternative phrasing without it. For example, rather than writing NOT a column equals a specific value, simply writing the column with the not-equals operator directly achieves the same result with arguably clearer intent. I generally recommend reaching for the most direct, readable phrasing available rather than defaulting to NOT plus a positive condition whenever a clearer direct alternative exists.
A Quick Reference Table
| Operator | What It Checks | Common Trap |
|---|---|---|
| Equals | Exact match | Does not work for NULL — use IS NULL instead |
| BETWEEN | Value within a range | Inclusive on both ends, not exclusive |
| IN | Match against a list of values | NOT IN fails silently if the list contains NULL |
| LIKE | Text pattern matching | Leading wildcard can be slow on large tables |
| IS NULL / IS NOT NULL | Explicit absence or presence of a value | The only correct way to check for NULL |
| AND / OR | Combining multiple conditions | AND evaluates before OR without explicit parentheses |
The Habit That Would Have Saved Two Hours
Whenever a query that should clearly return rows comes back empty, and you cannot immediately spot the issue, checking specifically whether any column involved in your WHERE clause might contain NULL values is one of the fastest diagnostic steps available, precisely because the NULL behavior is so counter-intuitive and so commonly the actual root cause behind an unexpectedly empty result set.
That two-hour debugging session I mentioned at the beginning would have taken thirty seconds with this specific check in mind from the start. I now teach this NULL behavior in the very first week of any beginner course, specifically because of how often it causes confusion that has nothing to do with a genuine logic error in the rest of the query.
Is your query returning unexpected results — too few rows, too many, or none at all? Describe your WHERE clause and the columns involved, and I can help identify which specific operator behavior might be causing the issue.