The WHERE clause defines the criteria that rows must meet to be selected for output.
The WHERE clause contains a predicate, which is a set of one or more expressions that can be TRUE, FALSE, or UNKNOWN. Values are compared according to:
| Predicate Type | Description |
|---|---|
| NULLS | Compared to any value, including other NULLs, produce UNKNOWNs |
| Character string types | Collating sequence |
| Numeric types | Numerical order |
| Date-time types | Chronological order |
| Interval types | Magnitude |
These comparisons are expressed using the following operators: =, <, <=, >, =>, and < > (does not equal).
Operators such as "*" (multiplication) or "||" (concatenation) may be applied depending on the data type. In most situations, row value constructors may be used instead of simple value expressions.
In addition to the standard comparison operators, SQL provides the following special predicate operators. Assume that B and C are all value expressions, which can be column names or direct expressions (possibly using column names or aggregate functions) in the appropriate data type:
| Predicate Operator | Description |
|---|---|
| B BETWEEN A AND C | Equal to (A <= B) AND (B <= C)
A and C must be specified in ascending order B BETWEEN C AND A is interpreted as C <= B) AND (B <= A), which is FALSE if the first expression is TRUE, unless all three values are the same If any of the values is NULL, the predicate is UNKNOWN |
| A IN C, D, ., ...) | This is true if A equals any value in the list |
| A LIKE 'string' | This assumes that A is a character string and searches for the specified substring. Fixed and varying-length wild cards can be used |
| A IS NULL | Specifically tests for NULLs. It can be TRUE or FALSE only, not UNKNOWN |
| A comp op SOME | ANY subquery | SOME and ANY have equivalent meanings. The subquery produces a set of values. If, for any value V so produced, A comp op V is TRUE, the ANY predicate is TRUE |
| A comp op ALL subquery | Similar to ANY except that all the values produced by the subquery have to make A comp op V true |
| EXISTS subquery | Evaluates to TRUE if the subquery produces any rows; otherwise, evaluates to FALSE. It is never UNKNOWN. To be meaningful, this phrase must use a correlated subquery |
| UNIQUE subquery | If the subquery produces no identical rows, UNIQUE is TRUE; otherwise, it is false. For the purposes of this predicate, identical rows are devoid of NULLs; otherwise, they are not identical |
| row value constructor MATCH arguments subquery | Tests for the presence of the constructed row among those of the table produced by the subquery. The arguments allow you to specify FULL or PARTIAL matches and whether the matched row must be unique |
| row value constructor OVERLAPS row value constructor | Allows you to determine when two date or time periods overlap. You must use it with DATETIME data types, possibly in conjunction with INTERVAL data types |
These predicates are combined using the conventional Boolean operators AND, OR, and NOT. For TRUE and FALSE values, these have the conventional results. The rows selected by the WHERE clause go on to be processed by subsequent clauses.