Operator | Description |
---|---|
< | Less than |
> | Greater than |
<= | Less than or equal to |
>= | Greater than or equal to |
= | Equal |
<> | Not equal |
!= | Not equal (non-standard but popular syntax) |
BETWEEN
operator tests if a value is within a specified range. It
uses the syntax value BETWEEN min AND max
:
NOT BETWEEN
:
NULL
in a BETWEEN
or NOT BETWEEN
statement is evaluated using
the standard NULL
evaluation rules applied to the equivalent
expression above:
BETWEEN
and NOT BETWEEN
operators can also be used to evaluate
any orderable type. For example, a VARCHAR
:
BETWEEN
and
NOT BETWEEN
must be the same type. For example, Trino will produce an
error if you ask it if John is between 2.3 and 35.2.
IS NULL
and IS NOT NULL
operators test whether a value is null
(undefined). Both operators work for all data types.
Using NULL
with IS NULL
evaluates to true:
NULL
value signifies an unknown value, so any comparison
involving a NULL
will produce NULL
. The IS DISTINCT FROM
and
IS NOT DISTINCT FROM
operators treat NULL
as a known value and both
operators guarantee either a true or false outcome even in the presence
of NULL
input:
NULL
value is not considered distinct
from NULL
. When you are comparing values which may include NULL
use
these operators to guarantee either a TRUE
or FALSE
result.
The following truth table demonstrate the handling of NULL
in
IS DISTINCT FROM
and IS NOT DISTINCT FROM
:
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 |
DOUBLE
, BIGINT
, VARCHAR
,
TIMESTAMP
, TIMESTAMP WITH TIME ZONE
, DATE
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.
ALL
, ANY
and SOME
quantifiers can be used together with
comparison operators in the following way:
Expression | Meaning |
---|---|
A = ALL (...) | Evaluates to true when A is equal to all values. |
A <> ALL (...) | Evaluates to true when A doesn’t 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 doesn’t match one or more values. |
A < ANY (...) | Evaluates to true when A is smaller than the biggest value. |
ANY
and SOME
have the same meaning and can be used interchangeably.
LIKE
operator can be used to compare values with a pattern:
_
matches any single character%
matches zero or more charactersWHERE
statements. An
example is a query to find all continents starting with E
, which
returns Europe
:
NOT
, and get all other continents,
all not starting with E
:
_
symbol for each character. The following query uses two underscores and
produces only Asia
as result:
_
and %
must be escaped to allow you to
match them as literals. This can be achieved by specifying the ESCAPE
character to use:
true
since the escaped underscore symbol
matches. If you need to match the used escape character as well, you can
escape it.
If you want to match for the chosen escape character, you simply escape
itself. For example, you can use \\
to match for ''''
.