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.

OperatorDescription
<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.