Operators in Apache Impala

SQL operators are a type of comparison function that is commonly used in SELECT statements’ WHERE clauses.

Arithmetic Operators
BETWEEN Operator
Comparison Operators
EXISTS Operator
ILIKE Operator
IN Operator
IREGEXP Operator
IS DISTINCT FROM Operator
IS NULL Operator
IS TRUE Operator
LIKE Operator
Logical Operators
REGEXP Operator
RLIKE Operator

Arithmetic Operator:
Expressions containing a left-hand argument, the operator, and then (in most circumstances) a right-hand argument are used by the arithmetic operators.

Between Operator:
Compares an expression to both a lower and upper bound in a WHERE clause. If the expression is bigger than or equal to the lower bound and less than or equal to the higher bound, the comparison is successful. There are no values that match when the bound values are flipped so that the lower bound is bigger than the upper bound.

Comparison Operator:
For validating equality and sort order in column data formats, Impala offers the following comparison operators:

=, !=, <>: apply to all scalar types.
<, <=, >, >=: apply to all scalar types; for BOOLEAN, TRUE is considered greater than FALSE.

EXISTS Operator:
The EXISTS operator determines whether or not a subquery yields any results. It’s most commonly used to locate values in one table that correspond to values in another.

NOT EXISTS, on the other hand, aids in locating all values from one database that have no equivalent values in another.

Syntax:
EXISTS (subquery)
NOT EXISTS (subquery)

ILIKE Operator:
For STRING data, a case-insensitive comparison operator with basic wildcard capability using to match a single character and percent to match several characters. The entire string value must be matched by the argument expression. Putting any percent wildcard match at the end of the string is usually more efficient.

This operator, which is accessible in CDH 5.7 / Impala 2.5 and higher, is a case-insensitive version of the LIKE operator.

IN Operator:
The IN operator compares the value of an argument to a set of values, returning TRUE if the argument matches any of the set’s values. The NOT IN operator examines if the given value is not part of a set of values by reversing the comparison.

IREGEXP Operator:
Using case-insensitive string comparisons, determines whether a value matches a regular expression. Uses the POSIX regular expression syntax, with the beginning and end of the string being represented by $ and. ? produces a non-greedy match, * represents a sequence of zero or more items, + represents a sequence of one or more items, and so on.

This operator, which is accessible in CDH 5.7 / Impala 2.5 and higher, is a case-insensitive version of the REGEXP operator.

IS DISTINCT Operator:
The IS DISTINCT FROM operator, as well as its inverse, the IS NOT DISTINCT FROM operator, determine if values are identical. IS NOT DISTINCT FROM and IS DISTINCT FROM are similar to the = and!= operators, with the exception that NULL values are treated the same. When comparing two NULL values, IS NOT DISTINCT FROM returns true rather than NULL, and IS DISTINCT FROM returns false rather than NULL. IS DISTINCT FROM returns true and IS NOT DISTINCT FROM returns false if one of the values being compared is NULL and the other is not, instead of returning NULL in both circumstances.

ISNULL Operator:
The IS NULL and IS NOT NULL operators check whether a given value is NULL. You employ a special-purpose comparison operator to check for this special circumstance since using NULL with any of the other comparison operators, such as = or!=, returns NULL rather than TRUE or FALSE.

You can use the operators IS UNKNOWN and IS NOT UNKNOWN as synonyms for IS NULL and IS NOT NULL, respectively, in CDH 5.14 / Impala 2.11 and higher.

IS TRUE Operator:
This variation of the IS operator tests for truth or falsity, with right-hand arguments [NOT] TRUE, [NOT] FALSE, and [NOT] UNKNOWN.

LIKE Operator:
A wildcard comparison operator for STRING data that uses the underscore (_) to match a single character and the percent sign (percent) to match several characters. The entire string value must be matched by the argument expression. Putting any percent wildcard match at the end of the string is usually more efficient.

LOGICAL Operators:
Based on a binary or unary logical operation between parameters that are also Booleans, logical operators return a BOOLEAN value. Typically, comparison operators are used in argument expressions.

REGEXP Operator:
This function determines if a value matches a regular expression. Uses the POSIX regular expression syntax, with the beginning and end of the string being represented by $ and. ? produces a non-greedy match, * represents a sequence of zero or more items, + represents a sequence of one or more items, and so on. The RLIKE operator is a synonym for REGEXP.

We’ll go over all of the operators with several instances in the next articles. Please subscribe to receive notifications about new posts.