Comparisons

In addition to the binary operators such as =, <>, RAW includes additional comparison operators.

IS DISTINCT FROM

IS DISTINCT FROM is used to check if two values are distinct, i.e. if they have a different value or one of them is null while the other isn’t. If none of the values is null, it behaves as the <> operator.

This operator treats null as a value, as opposed to an unknown, and checks if both values being compared are different.

In RAW, IS DISTINCT FROM can be used to check if two collections, records, or maps are different with each others, where nulls are compared as values, not unknowns.

Syntax:

<expression1> IS DISTINCT FROM <expression2>

For example:

[null] IS DISTINCT FROM [1] // true
1 IS DISTINCT FROM 2 // true
(a: 1) IS DISTINCT FROM (a: null) // true
null IS DISTINCT FROM null // false

IS / IS NOT DISTINCT FROM

IS / IS NOT DISTINCT FROM checks if two values are not distinct, i.e. if they have the same value or both of them are null.

This operator treats null as a value, as opposed to an unknown, and checks if both values being compared are equal. If none of the values is null, it behaves as the = operator.

In RAW, IS / IS NOT DISTINCT FROM can be used to check if two collections, records, or maps are equal with each others, where nulls are compared as values, not unknowns.

Syntax:

<expression1> IS NOT DISTINCT FROM <expression2>
<expression1> IS <expression2>
[null] IS [1] // false
1 IS 2 // false
(a: 1) IS (a: null) // false
null IS null // true

ALL

The ALL operator can be combined with any of the comparison operators (=, <>, >, >=, …) and evaluates to true if all of the subquery values meet the condition.

Syntax:

<expression> <operator> ALL <expression>

For example:

3 > ALL ([1,2]) // true
2 > ALL ([1,2]) // false

ANY

The ANY operator can be combined with any of the comparison operators (=, <>, >, >=, …) and evaluates to true if any of the subquery values meet the condition.

Syntax:

<expression> <operator> ANY <expression>

For example:

1 = ANY ([1,2]) // true
3 = ANY ([1,2]) // false

BETWEEN

Find if an expression is between two other expressions.

Syntax:

<expression> BETWEEN <start expression> AND <end expression>
<expression> NOT BETWEEN <start expression> AND <end expression>

BETWEEN is a shorthand as follows:

e1 BETWEEN e2 // Equivalent to: e1 >= e2 AND e1 <= e3
e1 NOT BETWEEN e2 // Equivalent to: e1 < e2 OR e1 > e3

Note that if end expression is smaller than start expression, this may not do what you want.

For example, the following returns 0 even though 3 is between 4 and 2:

CASE WHEN 3 BETWEEN 4 AND 2 THEN 1
     ELSE 0
END