Comparison Operations
The following operations can take operands with multiple data types but always return a Boolean value (sometimes nullable):
Operation | Definition | Observation |
---|---|---|
= | equality test | |
<> | inequality test | |
!= | inequality test, same as above | |
> | greater than | |
< | less than | |
>= | greater or equal | |
<= | less or equal | |
IS NULL | true if operand is NULL | |
IS NOT NULL | true if operand is not NULL | |
<=> | equality check that treats NULL values as equal | result is not nullable |
IS DISTINCT FROM | check if two values are not equal, treating NULL as equal | result is not nullable |
IS NOT DISTINCT FROM | check if two values are the same, treating NULL values as equal | result is not nullable |
BETWEEN ... AND ... | x BETWEEN a AND b is the same as a <= x AND x <= b | inclusive at both endpoints |
NOT BETWEEN ... AND ... | The NOT of the previous operator | not inclusive at either endpoint |
... IN ... | checks whether value appears in a list or set | |
<OP> ANY set_or_subquery | check if any of the values in a set compares properly | Example: 10 <= ANY (VALUES 10, 20, 30) is true |
<OP> SOME set_or_subquery | A synonym for ANY | Example: 10 <= SOME (VALUES 10, 20, 30) is true |
<OP> ALL set_or_subquery | check if all the values in a set compare properly | Example: 10 <= ALL (VALUES 10, 20, 30) is true |
EXISTS query | check whether query results have at least one row | |
UNIQUE query | check whether the result of a query contains no duplicates | ignores 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 ] END | Simple case |
CASE WHEN condition1 THEN result1 [ WHEN conditionN THEN resultN ]* [ ELSE resultZ ] END | Searched 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. |