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.
Examples:
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