Comparison functions and operators

A comparison operation compares the values of two parameters, which can be used for any comparable types, such as int, bigint, double, and text.

Comparison operators

A comparison operator is used to compare two parameter values.  During the comparison, if the logic is true, TRUE is returned. Otherwise, FALSE is returned.

Operator Meaning
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
= Equal to
<> Not equal to
!= Not equal to

Range operator BETWEEN

BETWEEN is used to determine whether a parameter value is between the values of two other parameters. The range is a closed interval.

  • If the logic is true, TRUE is returned. Otherwise, FALSE is returned.

    Example: SELECT 3 BETWEEN 2 AND 6;. The logic is true, and TRUE is returned.

    The preceding example is equivalent to SELECT 3 >= 2 AND 3 <= 6;.

  • BETWEEN can follow NOT to determine the opposite logic.

    Example: SELECT 3 NOT BETWEEN 2 AND 6;. The logic is false, and FALSE is returned.

    The preceding example is equivalent to SELECT 3 < 2 OR 3 > 6;.

  • If the value of any parameter is NULL, NULL is returned.

IS NULL and IS NOT NULL

These operators are used to determine whether a parameter value is NULL.

IS DISTINCT FROM and IS NOT DISTINCT FROM

Similar to determining whether two values are equal or not, but these operators can determine whether a NULL value exists.

Example:

SELECT NULL IS DISTINCT FROM NULL; -- false
SELECT NULL IS NOT DISTINCT FROM NULL; -- true

As described in the following table, the DISTINCT operator can be used to compare parameter values in most cases.

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 among multiple columns.

Example:

select greatest(1,2,3) ; -- 3 is returned.

Comparison conditions: ALL, ANY, and SOME

Comparison conditions are used to determine whether a parameter meets the specified conditions.

  • ALL is used to determine whether a parameter meets all the conditions.  If the logic is true, TRUE is returned. Otherwise, FALSE is returned.
  • ANY is used to determine whether a parameter meets any of the conditions.  If the logic 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 the comparison operators.

comparison and determination in many cases.

Expression Meaning
A = ALL (…)  TRUE is returned when A is equal to all values.
A <> ALL (…) TRUE is returned when A is not equal to all values.
A < ALL (…)  TRUE is returned when A is less than all values.
A = ANY (…)  TRUE is returned when A is equal to any value, which is equivalent to A IN (…).
A <> ANY (…) TRUE is returned when A is not equal to any value.
A < ANY (…) TRUE is returned when A is less than the maximum value.
Example: 
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