WHERE Clause (SELECT statement)

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.