Comparison functions and operators
A comparison function compares two parameter values of any comparable data types such as INTEGER, BIGINT, DOUBLE, and Text.
Comparison operators
A comparison operator is used to compare two values. If the statement is true, TRUE is returned. Otherwise, FALSE is returned.
Operator | Description |
---|---|
< | Less than |
> | Greater than |
<= | Less than or equal to |
>= | Greater than or equal to |
= | Equal to |
<> | Not equal to |
! = | Not equal to (not ISO standard) |
Range operator BETWEEN
The BETWEEN operator is used to determine whether a value is within a specified range.
- If the value is within the specified range, TRUE is returned. Otherwise, FALSE is
returned.
Example:
SELECT 3 BETWEEN 2 AND 6;
. If the value is within the specified range, TRUE is returned.The preceding statement is equivalent to
SELECT 3 >= 2 AND 3 <= 6;
. - The BETWEEN operator can follow the NOT operator to test whether a value is not within
a specified range.
Example:
SELECT 3 NOT BETWEEN 2 AND 6;
. If the value is not within the specified range, FALSE is returned.The preceding statement is equivalent to
SELECT 3 < 2 OR 3 > 6;
. - If any of the three values is NULL, NULL is returned.
IS NULL and IS NOT NULL
The IS NULL and IS NOT NULL operators test whether a value is NULL.
IS DISTINCT FROM and IS NOT DISTINCT FROM
These operators are equivalent to comparison operators EQUAL TO and NOT EQUAL TO except that IS DISTINCT FROM and IS NOT DISTINCT FROM can also be used to determine whether a NULL value exists.
Examples:
SELECT NULL IS DISTINCT FROM NULL; -- false
SELECT NULL IS NOT DISTINCT FROM NULL; -- true
The DISTINCT operator can be used to compare parameter values under multiple conditions, as described in the following table.
a | b | a = b | a <> b | a DISTINCT b | a NOT DISTINCT b |
---|---|---|---|---|---|
1 | 1 | TRUE | FALSE | FALSE | TRUE |
1 | 2 | FALSE | TRUE | TRUE | FALSE |
1 | NULL | NULL | NULL | TRUE | FALSE |
NULL | NULL | NULL | NULL | FALSE | TRUE |
GREATEST and LEAST
These operators are used to obtain the maximum or minimum values across multiple columns.
Example:
select greatest(1,2,3) -- Returns 3.
Quantified comparison predicates: ALL, ANY, and SOME
The ALL, ANY, and SOME quantifiers can be used to determine whether a parameter meets the specified conditions.
- ALL is used to determine whether a parameter meets all conditions. If the statement is true, TRUE is returned. Otherwise, FALSE is returned.
- ANY is used to determine whether a parameter meets any of the conditions. If the statement is true, TRUE is returned. Otherwise, FALSE is returned.
- Same as ANY, SOME is used to determine whether a parameter meets any of the conditions.
- ALL, ANY, and SOME must immediately follow comparison operators.
ALL and ANY support comparison under multiple conditions, as described in the following table.
Expression | Description |
---|---|
A = ALL (...) | Evaluates to TRUE when A is equal to all values. |
A <> ALL (...) | Evaluates to TRUE when A does not match any value. |
A < ALL (...) | Evaluates to TRUE when A is smaller than the smallest value. |
A = ANY (...) | Evaluates to TRUE when A is equal to any of the values. This form is equivalent to A IN (...). |
A <> ANY (...) | Evaluates to TRUE when A does not match any values. |
A < ANY (...) | Evaluates to TRUE when A is smaller than the greatest value. |
SELECT 'hello' = ANY (VALUES 'hello', 'world'); -- true
SELECT 21 < ALL (VALUES 19, 20, 21); -- false
SELECT 42 >= SOME (SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43); -- true