Skip to main content

Comparison Operations

The following operations can take operands with multiple data types but always return a Boolean value (sometimes nullable):

Comparison Operations
OperationDefinitionObservation
=equality test
<>inequality test
!=inequality test, same as above
>greater than
<less than
>=greater or equal
<=less or equal
IS NULLtrue if operand is NULL
IS NOT NULLtrue if operand is not NULL
<=>equality check that treats NULL values as equalresult is not nullable
IS DISTINCT FROMcheck if two values are not equal, treating NULL as equalresult is not nullable
IS NOT DISTINCT FROMcheck if two values are the same, treating NULL values as equalresult is not nullable
BETWEEN ... AND ...x BETWEEN a AND b is the same as a <= x AND x <= binclusive at both endpoints
NOT BETWEEN ... AND ...The NOT of the previous operatornot inclusive at either endpoint
... IN ...checks whether value appears in a list or set
<OP> ANY set_or_subquerycheck if any of the values in a set compares properlyExample: 10 <= ANY (VALUES 10, 20, 30) is true
<OP> SOME set_or_subqueryA synonym for ANYExample: 10 <= SOME (VALUES 10, 20, 30) is true
<OP> ALL set_or_subquerycheck if all the values in a set compare properlyExample: 10 <= ALL (VALUES 10, 20, 30) is true
EXISTS querycheck whether query results have at least one row
UNIQUE querycheck whether the result of a query contains no duplicatesignores NULL values

Note that the SQL standard mandates IS NULL to return true for a ROW object where all fields are NULL (similarly, IS NOT NULL is required to return false). Our compiler diverges from the standard, returning false for ROW(null) is null.

Comparison operations (=, <>, !=, <, >, <=, >=, <=>, IS NULL, IS NOT NULL) are defined on all data types, even recursive data types (including ARRAY, MAP, ROW, VARIANT, user-defined types). For complex types, comparisons are performed lexicographically on the type structure. In such comparisons fields with NULL values are compared smaller than any other value.

Other conditional operators

CASE value WHEN value1 [, value11 ]* THEN result1 [ WHEN valueN [, valueN1 ]* THEN resultN ]* [ ELSE resultZ ] ENDSimple case
CASE WHEN condition1 THEN result1 [ WHEN conditionN THEN resultN ]* [ ELSE resultZ ] ENDSearched case
NULLIF(value, value)Returns NULL if the values are the same. For example, NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5.
COALESCE(value, value [, value ]*)Provides a value if the first value is NULL. For example, COALESCE(NULL, 5) returns 5.
GREATEST( expr [, expr ]* )The largest of a number of expressions.
LEAST( expr [, expr ]* )The smallest of a number of expressions.