Database Reference
In-Depth Information
The
BETWEEN
operator works with any kind of data type; however, it is not
practical to use it with
BOOLEAN
Sometimes it is possible that the values provided are in the lower or upper
bound, so you can apply the
Cast()
operator to those values to convert
them into compatible types
It is also important to note that the
BETWEEN
operator is typically used with
numeric data types, so other functions can also be used to extract numeric
values, if possible.
While using
BETWEEN
with a string, it is advisable to use the
upper()
,
lower()
,
substr()
, or
trim()
function to operate on the string instead
of having them operated with the
BETWEEN
operator, because variation in
the string length could change the outcome of the comparison operator in
BETWEEN
.
•
DISTINCT
: The SQL
DISTINCT
operator is used with the
SELECT
statement
to retrieve only unique data entries, depending on the column list selected in
the table. Here are the key features of the
DISTINCT
operator when used
with Impala:
• The
DISTINCT
operator returns the unique values from the column
•
NULL
is also included as a value in the column and as part of the res-
ult
• The
DISTINCT
operator can also return unique combinations of val-
ues from multiple columns within the same
SELECT
statement
• The
DISTINCT
operator can be combined with other functions, such
as
COUNT
, to aggregate total unique values found in the reference
column
• Impala does not support using
DISTINCT
in more than one aggreg-
ate function in the same query
Here is the syntax and an example of using the
DISTINCT
operator in Im-
pala: