The following operations can take operands with multiple data types
but always return a Boolean value (sometimes nullable):
Comparison OperationsOperation | Definition | Observation |
---|
= | equality test | |
<> | inequality test | |
> | 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 | check if any of the values in a set compares properly | Example: 10 <= ANY (VALUES 10, 20, 30) |
<OP> ALL SET | check if all the values in a set compare properly | Example: 10 <= ALL (VALUES 10, 20, 30) |
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 |
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. |