Operator | Description |
---|---|
< | Less than |
> | Greater than |
<= | Less than or equal to |
>= | Greater than or equal to |
= | Equal |
<> | Not equal |
!= | Not equal |
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
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 \
.
IN
operator can be used in a WHERE
clause to compare column values with
a list of values. The list of values can be supplied by a subquery or directly
as static values in an array:
NOT
keyword to negate the condition.
The following example shows a simple usage with a static array:
OR
. The preceding query is equivalent to the following query:
NOT
, and get all other regions
except the values in list: