The WHERE clause restricts the set of rows returned by evaluating a conditional predicate against each individual record.
In relational algebra, filtering rows corresponds to the selection operator, producing a horizontal slice of the table.
Predicates evaluate to a boolean truth value for every row: true, false, or unknown under three-valued logic.
Only rows where the predicate evaluates strictly to true are included in the query result set.
Standard comparison operators include equality, inequality, greater than, less than, and inclusive ranges with BETWEEN.
The IN operator tests whether a column value matches any item in a specified list or nested query result.
Pattern matching on text columns is accomplished using the LIKE operator paired with percent and underscore wildcards.
Percent matches any sequence of zero or more characters, while an underscore matches exactly one single character.
Multiple predicates are combined using boolean logic: AND requires all conditions, while OR requires at least one.
Parentheses are essential when mixing AND and OR operators to enforce intended precedence during evaluation.
The database query engine uses indexes on filtered columns to jump directly to matching rows without scanning the whole table.
Writing non-sargable predicates, such as wrapping columns in functions, prevents the database from utilising indexes.
For example, filtering on the year of a date using a function often forces a slow sequential table scan.
WHERE filters rows before any grouping or aggregation takes place, reducing the volume of data processed downstream.
A common mistake is placing aggregate conditions like sums or averages inside WHERE, which triggers an immediate syntax error.