Predicates

A predicate specifies a condition that is true, false, or unknown about a given value, row, or group.

The following rules apply to predicates of any type:

  • Predicates are evaluated after the expressions that are operands of the predicate.
  • All values that are specified in the same predicate must be compatible.
  • Except for the EXISTS predicate, a subquery in a predicate must specify a single column unless the operand on the other side of the comparison operator is a fullselect.
  • The value of a host variable can be null (that is, the variable can have a negative indicator variable).
  • The CCSID conversion of operands of predicates that involve two or more operands is done according to Conversion rules for comparisons.
  • Use of an XML value is limited to the NULL or XMLEXISTS predicates.

The types of predicates are:

Read syntax diagramSkip visual syntax diagrambasic predicatequantified predicateARRAY_EXISTS predicateBETWEEN predicateDISTINCT predicateEXISTS predicateIN predicateLIKE predicateNULL predicateXMLEXISTS predicate

Row-value expression: The operand of several predicates (basic, quantified, DISTINCT, and IN) can be a row-value-expression:

Read syntax diagramSkip visual syntax diagram(,expression)

A row-value-expression returns a single row that consists of one or more column values. The values can be specified as a list of expressions. The number of columns that are returned by the row-value-expression is equal to the number of expressions that are specified in the list.

For several examples of predicates that use distinct types , see Assignment and comparison. For examples of predicates that use distinct types, see User-defined type comparisons.