The following operations can take operands with multiple data types
but always return a Boolean value (sometimes nullable):
Comparison Operations
Operation
Definition
Observation
=equality test
<>inequality test
>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 equal
result is not nullable
IS DISTINCT FROMcheck if two values are not equal, treating NULL as equal
result is not nullable
IS NOT DISTINCT FROMcheck 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 <= binclusive 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 SETcheck if any of the values in a set compares properly
Example: 10 <= ANY (VALUES 10, 20, 30)
<OP> ALL SETcheck if all the values in a set compare properly
Example: 10 <= ALL (VALUES 10, 20, 30)
EXISTS querycheck whether query results have at least one row
UNIQUE querycheck whether the result of a query contains no duplicates
ignores NULL values
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.