SQL: WHERE Clause & Operators
Master row-level filtering. Learn comparison and logical operators, range checks with BETWEEN, wildcard searches with LIKE and ILIKE, three-valued NULL logic, correlated subqueries with EXISTS, and operator precedence.
1. The WHERE Clause: Row-Level Filtering
The WHERE clause filters table rows based on specified search conditions. In the logical query processing order, the WHERE clause evaluates immediately after the FROM and JOIN clauses, determining which rows enter the subsequent grouping, aggregation, and projection stages.
Every condition in a WHERE clause evaluates to a boolean truth value: TRUE, FALSE, or UNKNOWN (due to NULL). Only rows evaluating strictly to TRUE are included in the result set; rows evaluating to FALSE or UNKNOWN are omitted.
Basic WHERE Clause Filtering
SQLFilter table rows by an exact string match condition
SARGable Queries and Index Efficiency
How you write WHERE conditions determines whether database indexes are utilized:
- SARGable Defined:A condition is SARGable (Search Argument Able) if the database query engine can use a B-Tree index to jump directly to matching rows.
- Non-SARGable Anti-Pattern:Writing WHERE UPPER(city) = 'NEW YORK' or WHERE YEAR(enrollment_date) = 2026 wraps the column in a function, blinding the index and triggering a full table scan.
- SARGable Solution:Keep the column bare: WHERE enrollment_date >= '2026-01-01' AND enrollment_date < '2027-01-01' enables high-speed index range scans.
2. Comparison Operators (=, <>, !=, <, >, <=, >=)
Comparison operators evaluate relationships between two scalar expressions. They operate across numbers, strings, dates, and timestamps:
| Operator | Description | Example Expression |
|---|---|---|
| = | Equal to | gpa = 4.0 |
| <> or != | Not equal to (both are valid in modern SQL; <> is ANSI standard) | department <> 'Music' |
| > | Greater than | age > 21 |
| < | Less than | unit_price < 50.00 |
| >= | Greater than or equal to | gpa >= 3.5 |
| <= | Less than or equal to | age <= 25 |
Numeric Range Filtering
SQLFilter records using greater-than-or-equal comparisons
Lexicographical String Comparisons
Comparison operators evaluate strings alphabetically based on character collation rules:
- Alphabetical Sorting:Writing WHERE last_name < 'M' returns all individuals whose surnames start with letters A through L.
- Collation Sensitivity:Whether 'apple' < 'Banana' evaluates to TRUE depends on whether the database collation is case-sensitive (latin1_bin, C) or case-insensitive (utf8mb4_unicode_ci).
3. Logical Operators: AND, OR, NOT
Logical operators combine multiple search conditions to construct compound boolean filters.
| Operator | Condition Outcome | Truth Rule |
|---|---|---|
| AND | Both conditions must be TRUE | Fails if either condition is FALSE or UNKNOWN |
| OR | At least one condition must be TRUE | Passes if condition A is TRUE, condition B is TRUE, or both are TRUE |
| NOT | Inverts the boolean outcome | Turns TRUE into FALSE, and FALSE into TRUE (UNKNOWN remains UNKNOWN) |
Compound Logical Filtering
SQLCombine multiple criteria using AND, OR, and NOT with explicit parentheses
Short-Circuit Evaluation in SQL
How query optimizers evaluate compound logical clauses:
- Undefined Evaluation Order:Unlike languages like JavaScript or Python where && always evaluates left-to-right, SQL query optimizers can evaluate clauses in whatever order costs fewer I/O operations.
- Cost-Based Ordering:If condition A checks a indexed integer key and condition B calls an expensive regular expression, the engine evaluates condition A first to discard non-matching rows early.
4. IN and NOT IN Operators
The IN operator tests whether a value matches any item in a discrete list of literals or subquery results. It replaces repetitive chains of OR conditions with cleaner, more performant syntax.
List Matching with IN
SQLMatch column values against a discrete set of allowed string literals
The NOT IN operator inverts membership testing, returning rows whose values do not appear anywhere in the target set:
Excluding Values with NOT IN
SQLFilter out rows matching any item in the prohibited list
The Fatal NOT IN with NULL Trap
One of the most dangerous edge cases in relational databases:
- The Problem:Writing WHERE department NOT IN ('Arts', 'Music', NULL) returns an empty result set with zero rows, even if departments like 'Engineering' exist!
- The Mathematical Cause:NOT IN translates internally to: department <> 'Arts' AND department <> 'Music' AND department <> NULL. Because department <> NULL evaluates to UNKNOWN, the entire AND chain becomes UNKNOWN.
- The Safe Remedy:When using NOT IN against a subquery, always filter out nulls: WHERE col NOT IN (SELECT id FROM table WHERE id IS NOT NULL), or use NOT EXISTS instead.
5. BETWEEN Operator
The BETWEEN operator filters values within an inclusive range. Writing value BETWEEN min AND max is functionally equivalent to writing value >= min AND value <= max.
Inclusive Range Filtering with BETWEEN
SQLFilter records within lower and upper numeric boundaries
Date and Timestamp Ranges with BETWEEN
Take care when applying BETWEEN to timestamp columns with time components:
- The Midnight Trap:WHERE event_time BETWEEN '2026-10-01' AND '2026-10-10' interprets the end date as '2026-10-10 00:00:00'. Any event occurring at 2:00 PM on October 10 is omitted!
- Half-Open Intervals:For date and time filtering, industry best practice prefers half-open ranges: WHERE event_time >= '2026-10-01' AND event_time < '2026-10-11'.
6. LIKE Operator and SQL Wildcards (% and _)
The LIKE operator performs pattern matching on text strings using two standard wildcard characters:
| Wildcard | Pattern Match Behavior | Example Pattern |
|---|---|---|
| % | Matches zero, one, or multiple characters | 'J%' matches 'John', 'Jane', 'J' |
| _ | Matches exactly one single character | 'J_ne' matches 'Jane', 'June', but not 'Janine' |
Pattern Matching with LIKE
SQLCombine prefix searches and substring matching using the percent wildcard
Index Performance with Leading Wildcards
The placement of the wildcard character determines index usage:
- Prefix Match (LIKE 'Smith%'):B-Tree indexes can efficiently seek the range starting with 'Smith', delivering high-speed search performance.
- Leading Wildcard (LIKE '%Smith'):Because the first characters are unknown, the database cannot navigate the B-Tree and must scan every row in the table.
- Escaping Literal Wildcards:To search for literal '%' or '_' characters, define an escape character: WHERE code LIKE '%!_%' ESCAPE '!'.
7. ILIKE and Case-Insensitive Search
By ANSI standard, the LIKE operator is case-sensitive. However, case-sensitivity behavior varies across database engines:
| Engine | Default LIKE Behavior | Case-Insensitive Operator |
|---|---|---|
| PostgreSQL | Strictly Case-Sensitive | ILIKE (e.g. name ILIKE 'alice%') |
| SQLite | Case-Insensitive for ASCII text | LIKE is case-insensitive by default |
| MySQL / MariaDB | Case-Insensitive by default (table collation dependent) | LIKE with _ci collation (or BINARY for sensitive) |
Portable Case-Insensitive Search
SQLNormalize casing with LOWER() to ensure consistent cross-database behavior
Functional Indexes for Case-Insensitive Searches
When querying large tables using LOWER() or UPPER():
- Expression Indexes:Create a functional index on the lowercased column: CREATE INDEX idx_users_lower_email ON users(LOWER(email)).
- Performance Outcome:The query planner uses the functional index directly, eliminating table scans during case-insensitive user logins.
8. IS NULL and IS NOT NULL (Three-Valued Logic)
In SQL, NULL represents the absence of a value or an unknown state. Because NULL represents an unknown, it cannot be compared using standard equality operators (= or !=).
Testing for Missing Data with IS NULL
SQLObserve how IS NULL successfully identifies rows with missing column values
The Law of Three-Valued Logic (3VL)
How NULL operates inside boolean expressions:
- NULL = NULL is UNKNOWN:Two unknown values cannot be judged equal. If the age of person A is unknown and the age of person B is unknown, we cannot confirm they are the same age.
- WHERE Filters UNKNOWN:A WHERE condition only accepts rows evaluating to TRUE. Expressions evaluating to UNKNOWN are discarded identically to FALSE.
- Always Use IS NULL:Never write WHERE column = NULL or WHERE column != NULL. Always use IS NULL and IS NOT NULL.
9. Correlated Filtering: EXISTS and NOT EXISTS
The EXISTS operator tests whether a correlated subquery returns at least one row. It returns TRUE if the subquery produces one or more rows, and FALSE if the subquery returns an empty result set.
Correlated Subquery Filtering with EXISTS
SQLVerify row existence in child relations using high-performance semi-joins
Why EXISTS Outperforms IN on Large Datasets
How query optimizers handle EXISTS compared to IN:
- Early Termination:EXISTS stops scanning the child table the moment it encounters the first matching row (short-circuit). IN must evaluate the entire subquery result set.
- Immune to NULLs:Unlike NOT IN, the NOT EXISTS operator is completely unaffected by NULL values in the subquery, making it safer for production code.
- SELECT 1 Shorthand:Writing SELECT 1 inside EXISTS is standard convention because the database engine ignores the projected columns and checks only row presence.
10. ANY and ALL Operators
The ANY (or SOME) and ALL operators modify comparison operators when comparing a scalar value against a subquery returning a column of numbers:
| Operator Pattern | Evaluation Rule | Equivalent Concept |
|---|---|---|
| > ANY (subquery) | True if greater than at least one value in the set | > (SELECT MIN(val) FROM subquery) |
| > ALL (subquery) | True if greater than every single value in the set | > (SELECT MAX(val) FROM subquery) |
| = ANY (subquery) | True if value matches any item in the subquery | Identical to IN (subquery) |
Quantified Comparison Against Subqueries
SQLFilter rows that exceed maximum or minimum thresholds of another subset
Universal Portability Notice
While ANY and ALL are ANSI SQL standards, engine support varies:
- Native Support:PostgreSQL, MySQL, Oracle, and Microsoft SQL Server natively support > ANY and > ALL.
- SQLite Equivalent:SQLite does not implement the ANY and ALL keywords directly. In SQLite, write the equivalent scalar aggregation using MIN() or MAX(), which runs identically across all databases.
11. Arithmetic Operators (+, -, *, /, %)
Arithmetic operators evaluate mathematical expressions inside both the WHERE filter and the SELECT projection list:
Arithmetic Operations in Query Conditions
SQLPerform mathematical multiplications and filter based on calculated results
Modulo Operator (%) in Data Sharding
The modulo operator calculates integer division remainders:
- Even/Odd Checks:WHERE id % 2 = 0 filters for even IDs; WHERE id % 2 = 1 filters for odd IDs.
- Data Sampling & Sharding:WHERE id % 10 = 0 extracts an even 10% deterministic sample of rows across millions of table records for analytics.
12. Operator Precedence Hierarchy
When a single SQL statement contains multiple operators without parentheses, the database engine resolves them according to a strict precedence hierarchy. Operators higher in the table evaluate before operators lower in the table:
| Priority Level | Operators | Evaluation Category |
|---|---|---|
| 1 (Highest) | ( ) | Parentheses group expressions and override default precedence |
| 2 | * , / , % | Multiplication, division, and modulo |
| 3 | + , - | Addition and subtraction |
| 4 | = , <> , < , > , <= , >= | Comparison operators |
| 5 | IS NULL , LIKE , IN , BETWEEN | Pattern matching and membership tests |
| 6 | NOT | Logical negation |
| 7 | AND | Logical conjunction (evaluates BEFORE OR!) |
| 8 (Lowest) | OR | Logical disjunction |
Understanding Precedence: AND Binds Before OR
SQLNotice how students in Engineering are included regardless of GPA because AND takes precedence
The Defensive Parentheses Rule
Never rely on default operator precedence in team codebases:
- Defensive Coding:Always wrap mixed AND / OR clauses in explicit parentheses: WHERE (dept = 'Eng' OR dept = 'CS') AND gpa >= 3.8.
- Preventing Production Bugs:Ambiguous AND/OR clauses are a leading cause of security vulnerabilities (such as accidentally returning user records across customer tenants).
Knowledge Check: SQL WHERE Clause & Operators
1. What is the result of evaluating WHERE age = NULL in SQL?
2. Why does value NOT IN (1, 2, NULL) return an empty result set?
3. Is the SQL BETWEEN operator range inclusive or exclusive?
4. In SQL operator precedence, which logical operator evaluates first?
5. What wildcard matches exactly one single character in a LIKE query?
6. Why is EXISTS generally faster than IN for subqueries on large tables?