Predicates are components of search conditions (see section "Search conditions").
A predicate consists of operands and operators. Predicates can be grouped together as follows according to the operator involved:
Comparison of two rows
Quantified comparison (comparison with the rows of a table)
BETWEEN predicate (range query)
CASTABLE predicate (convertibility check)
IN predicate (elementary query)
LIKE predicate (simple pattern comparison)
LIKE_REGEX predicate (pattern comparison with regular expressions)
NULL predicate (comparison with the NULL value)
EXISTS predicate (existence query)
The individual groups are described below in the above order.
A predicate returns the truth value true, false or unknown. The value of a predicate is calculated by calculating the values of the operands and applying the appropriate operators to the calculated values. In certain cases an operand is not calculated at all, or is only partially calculated, if this is enough to determine the result.
The diagram below provides a simplified overview of the syntax of all predicates:
praedicate ::=
{ row comparison_op row |
vector_column comparison_op expression |
row comparison_op { ALL | SOME | ANY } subquery |
row [NOT] BETWEEN row AND row |
vector_column [NOT] BETWEEN expression AND expression |
expression IS [NOT] CASTABLE AS data_type |
row [NOT] IN { subquery | (row ,...) } |
vector_column [NOT] IN (expression , expression ,...) |
operand [NOT] LIKE pattern [ESCAPE character...] |
operand [NOT] LIKE_REGEX regular_expression [FLAG flag] |
expression IS [NOT] NULL |
EXISTS subquery
}
row ::= { (expression ,...) | expression | subquery }
vector_column ::= [table.]{ column[min..max] | column(min..max) }
comparison_op ::= { = | < | > | <= | >= | <> }
operand ::= expression
pattern ::= expression
character ::= expression
regular_expression ::= expression
flag ::= expression