Comparison Operators
Applies to: ✅ Data Cloud SQL ✅ Tableau Hyper API
Comparison operators compare a value against another value. Comparison operators are binary operators that return values of type boolean
.
Operator | Description |
---|---|
< | less than |
> | greater than |
<= | less than or equal to |
>= | greater than or equal to |
= | equal |
<> or != | not equal |
The BETWEEN
predicate simplifies range tests.
a BETWEEN x AND y
is equivalent to a >= x AND a <= y
. BETWEEN
treats the endpoint values as included in the range. NOT BETWEEN
does the opposite comparison: a NOT BETWEEN x AND y
is equivalent to a < x OR a > y
.
While the normal comparison operators and BETWEEN
ignore NULLs.
These comparison functions are available in Data Cloud SQL.
Ordinary comparison operators yield null (signifying "unknown") if either input is null. For example, 7 = NULL
returns null. If this behavior doesn't fit your use case, use the IS NOT DISTINCT FROM
predicates.
a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b
.
For non-null inputs, IS DISTINCT FROM
is the same as the <>
operator. However, if both inputs are null it returns false, and if one input is null it returns true. IS NOT DISTINCT FROM
is identical to =
for non-null inputs, but returns true if both inputs are null, and false if one input is null. These predicates act as though null is a normal data value, rather than an unknown.
To check whether a value is or isn't null, use the predicates:
If either input is null, ordinary comparison operators yield null, signifying “unknown”. For example, 7 = NULL
returns null. If this behavior doesn’t fit your use case, use the IS NOT DISTINCT FROM
predicates.
expression IS NOT NULL
expression ISNULL
expression NOTNULL
.
For non-null inputs, IS DISTINCT FROM
is the same as the <>
operator. However, if both inputs are null, it returns false. If one input is null, it returns true. IS NOT DISTINCT FROM
is identical to =
for non-null inputs, but it returns true if both inputs are null and false if one input is null. These predicates act as though null is a normal data value rather than an unknown.
To check whether a value is or isn’t null, use the predicates.
IS NOT TRUE
IS FALSE
IS NOT FALSE
IS UNKNOWN
IS NOT UNKNOWN
.
You can also test boolean values by using the predicates.
IN
and NOT IN
compare an expression against multiple values. They use the syntax
These tests always return true or false, even if the operand is null. A null input is treated as the logical value “unknown”. Notice that IS UNKNOWN
and IS NOT UNKNOWN
are effectively the same as IS NULL
and IS NOT NULL
, respectively, except that the input expression is of Boolean type.
The right-hand side is a parenthesized list of scalar expressions. The value from the left-hand side is compared against each of the expressions from the right-hand side.
IN
and NOT IN
compare an expression against multiple values. They use this syntax.
For NOT IN
the result is true if the left-hand expression's result is unequal to all of the right-hand expressions. This is a shorthand notation for expression <> value1 AND expression <> value2 AND ...
If the left-hand expression returns null, or if there are no equal right-hand values and at least one right-hand expression returns null, the result of the IN
/NOT IN
construct is null.
IN
and NOT IN
are syntactically related to the subquery forms but don't involve subqueries.