6.2. Comparison Functions and Operators

    Range Operator: BETWEEN

    The statement shown above is equivalent to the following statement:

    1. SELECT 3 >= 2 AND 3 <= 6;

    To test if a value does not fall within the specified rangeuse NOT BETWEEN:

      The statement shown above is equivalent to the following statement:

      The presence of NULL in a BETWEEN or NOT BETWEEN statementwill result in the statement evaluating to NULL:

      1. SELECT NULL BETWEEN 2 AND 4; -- null
      2.  
      3. SELECT 2 BETWEEN NULL AND 6; -- null

      The BETWEEN and NOT BETWEEN operators can also be used toevaluate string arguments:

      1. SELECT 'Paul' BETWEEN 'John' AND 'Ringo'; -- true

      Not that the value, min, and max parameters to BETWEEN and NOTBETWEEN must be the same type. For example, Presto will produce anerror if you ask it if John is between 2.3 and 35.2.

      Using NULL with IS NULL evaluates to true:

      But any other constant does not:

      1. SELECT 3.0 IS NULL; -- false

      IS DISTINCT FROM and IS NOT DISTINCT FROM

      In SQL a NULL value signifies an unknown value, so any comparisoninvolving a NULL will produce NULL. The IS DISTINCT FROMand IS NOT DISTINCT FROM operators treat NULL as a known valueand both operators guarantee either a true or false outcome even inthe presence of NULL input:

      1. SELECT NULL IS DISTINCT FROM NULL; -- false
      2.  

      In the example shown above, a NULL value is not considereddistinct from NULL. When you are comparing values which mayinclude NULL use these operators to guarantee either a orFALSE result.

      The following truth table demonstrate the handling of NULL inIS DISTINCT FROM and IS NOT DISTINCT FROM:

      These functions are not in the SQL standard, but are a common extension.Like most other functions in Presto, they return null if any argument isnull. Note that in some other databases, such as PostgreSQL, they onlyreturn null if all arguments are null.

      • greatest(value1, value2, …, valueN) → [same as input]

      • Returns the largest of the provided values.

      • least(value1, value2, …, valueN) → [same as input]

      • Returns the smallest of the provided values.

      Quantified Comparison Predicates: ALL, ANY and SOME

      The ALL, ANY and SOME quantifiers can be used together with comparison operators in thefollowing way:

      For example:

      1. SELECT 'hello' = ANY (VALUES 'hello', 'world'); -- true
      2.  
      3. SELECT 21 < ALL (VALUES 19, 20, 21); -- false
      4. SELECT 42 >= SOME (SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43); -- true

      Here are the meanings of some quantifier and comparison operator combinations:

      ANY and have the same meaning and can be used interchangeably.