Skip to main content

Row and Array Comparisons

Extreme DPS allows you to use IN or NOT IN to make multiple comparisons between groups of values.

IN​

The IN function checks whether a given expression is in the list of expressions.

Syntax​

<expression> IN (<value> [, ...])

The right-hand side is a parenthesized list of scalar expressions. When <expression> matches any member in the right-hand list, true is returned.

Arguments​

  • <expression>

    The expression to check whether it is a member of the list of values.

  • <value>

    The value in the list with which <expression> matches.

Examples​

'john' IN ('john', 'tome', 'gary') → true
'john' IN ('x', 'y', 'z') → false
NULL IN ('x', 'y', 'z') → NULL
'john' IN (NULL, NULL) → NULL

Usage notes​

If the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand expression yields null, the result will be null.

NOT IN​

The NOT IN function checks whether a given expression is not in the list of expressions.

Syntax​

<expression> NOT IN (<value> [, ...])

The right-hand side is a parenthesized list of scalar expressions. If the result of the left-hand expression is equal to none of the right-hand expressions, true will be returned.

Arguments​

For details, see Arguments in the "IN" section.

Examples​

'john' NOT IN ('john', 'tome', 'gary') → false
'john' NOT IN ('x', 'y', 'z') → true
NULL NOT IN ('x', 'y', 'z') → NULL
'john' NOT IN (NULL, NULL) → NULL

Usage notes​

If the left-hand expression yields null, or if no equal right-hand values exist and at least one right-hand expression yields null, the result of NOT IN will be null.