The presence of NULL in a
SELECT 3 NOT BETWEEN 2 AND 6;0 or
SELECT 3 NOT BETWEEN 2 AND 6;2 statement will result in the statement evaluating to NULL:
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null
The
SELECT 3 NOT BETWEEN 2 AND 6;0 and
SELECT 3 NOT BETWEEN 2 AND 6;2 operators can also be used to evaluate string arguments:
SELECT 'Paul' BETWEEN 'John' AND 'Ringo'; -- true
Note that the value, min, and max parameters to
SELECT 3 NOT BETWEEN 2 AND 6;0 and
SELECT 3 NOT BETWEEN 2 AND 6;2 must be the same type. For example, Presto will produce an error if you ask it if John is between 2.3 and 35.2.
IS NULL and IS NOT NULL
The
SELECT 3 NOT BETWEEN 2 AND 6;9 and
SELECT 3 6;0 operators test whether a value is null [undefined]. Both operators work for all data types.
Using
SELECT 3 6;1 with
SELECT 3 NOT BETWEEN 2 AND 6;9 evaluates to true:
select NULL IS NULL; -- true
But any other constant does not:
SELECT 3.0 IS NULL; -- false
IS DISTINCT FROM and IS NOT DISTINCT FROM
In SQL a
SELECT 3 6;1 value signifies an unknown value, so any comparison involving a
SELECT 3 6;1 will produce
SELECT 3 6;1. The
SELECT 3 6;6 and
SELECT 3 6;7 operators treat
SELECT 3 6;1 as a known value and both operators guarantee either a true or false outcome even in the presence of
SELECT 3 6;1 input:
SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true
In the example shown above, a
SELECT 3 6;1 value is not considered distinct from
SELECT 3 6;1. When you are comparing values which may include
SELECT 3 6;1 use these operators to guarantee either a
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3 or
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4 result.
The following truth table demonstrate the handling of
SELECT 3 6;1 in
SELECT 3 6;6 and
SELECT 3 6;7:
a
b
a = b
a b
a DISTINCT b
a NOT DISTINCT b
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null8
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null8
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null8
SELECT 'Paul' BETWEEN 'John' AND 'Ringo'; -- true5
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null8
SELECT 3 6;1
SELECT 3 6;1
SELECT 3 6;1
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4
SELECT 3 6;1
SELECT 3 6;1
SELECT 3 6;1
SELECT 3 6;1
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null4
SELECT NULL BETWEEN 2 AND 4; -- null SELECT 2 BETWEEN NULL AND 6; -- null3
GREATEST and LEAST
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 is null. Note that in some other databases, such as PostgreSQL, they only return null if all arguments are null.
The following types are supported:
SELECT 3.0 IS NULL; -- false2,
SELECT 3.0 IS NULL; -- false3,
SELECT 3.0 IS NULL; -- false4,
SELECT 3.0 IS NULL; -- false5,
SELECT 3.0 IS NULL; -- false6,
SELECT 3.0 IS NULL; -- false7greatest[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
SELECT 3.0 IS NULL; -- false8,
SELECT 3.0 IS NULL; -- false9 and
SELECT NULL IS DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true0 quantifiers can be used together with comparison operators in the following way:
expression operator quantifier [ subquery ]
For example:
SELECT 3 >= 2 AND 3