Hive Relational | Arithmetic | Logical Operators

Like any other Database Hive also supports Relation, Arithmetic and Logical operators. Keeps an eye on return values as they changes if one of the operand is NULL.

Hive Relational Operators

Below are Hive relational operators.

Relational OperatorsDescription
A = BReturns TRUE when A is equal to B, FLASE when they are not equal.
Operand Types: All primitive types
A == BSimilar to = operator.
Operand Types: All primitive types
A <=> BSame as = and == operator for non-null values.
For NULLS, returns TRUE if both are NULL, FALSE if one of them is NULL
Operand Types: All primitive types
A <> BReturns TRUE if A is not equal to B, otherwise FALSE.
Returns NULL if A or B is NULL
Operand Types: All primitive types
A != BSimilar to <> operator.
Operand Types: All primitive types
A < BReturns TRUE if A is less than B, and returns FALSE when A is greater than equal to B.
Returns NULL if A or B is NULL,
Operand Types: All primitive types
A <= BReturns TRUE if A is less than or equal to B, and returns FALSE when A is greater than B.
Returns NULL if A or B is NULL
Operand Types: All primitive types
A > BReturns TRUE if A is greater than B, and returns FALSE when A is less than equal to B.
Returns NULL if A or B is NULL
Operand Types: All primitive types
A >= BReturns TRUE if A is greater than or equal to B, and returns FALSE when A is less than B.
Returns NULL if A or B is NULL
Operand Types: All primitive types
A [NOT] BETWEEN B AND CReturns TRUE if A is greater than or equal to B AND A less than or equal to C, otherwise FALSE.
NOT is optional, when used it inverts.
Returns NULL if A, B, or C is NULL
Operand Types: All primitive types
A IS NULLReturns TRUE if A is NULL, otherwise FALSE.
Operand Types: All types
A IS NOT NULLReturns FALSE if A is NULL, otherwise TRUE.
Operand Types: All types
A IS [NOT] (TRUE|FALSE)Returns TRUE only if A meets the condition.
Note: NULL is UNKNOWN, and because of that (UNKNOWN IS TRUE) and (UNKNOWN IS FALSE) both evaluate to FALSE.
Operand Types: Boolean types
A [NOT] LIKE BTRUE if string A matches the SQL simple regular expression B, otherwise FALSE.
Returns NULL if A or B is NULL
Operand Types: strings
A RLIKE B, TRUE if any (possibly empty) substring of A matches the Java regular expression B, otherwise FALSE.
Returns NULL if A or B is NULL
Operand Types: strings
A REGEXP BSame as RLIKE.
Operand Types: strings

Hive Logical Operators

Below are Hive Logical Operators.

Logical OperatorsDescription
A AND BEvaluates to TRUE if both A and B are TRUE,
Evaluates to FALSE if either A or B is FALSE.
NULL if A or B is NULL.
Operand Types: boolean
A OR BEvaluates to TRUE if either A or B or both are TRUE, FALSE OR NULL is NULL, otherwise FALSE.
Operand Types: boolean
NOT AEvaluates to TRUE if A is FALSE or NULL if A is NULL.
Evaluates to FALSE if A is TRUE
Operand Types: boolean
! ASimilar to NOT A.
Operand Types: boolean
A IN (val1, val2, …)Evaluates to TRUE if A is present in any of the values.
Operand Types: boolean
A NOT IN (val1, val2, …)Evaluates to TRUE if A is NOT present in any of the values.
Operand Types: boolean
[NOT] EXISTS (subquery)TRUE if the subquery returns at least one row.

Hive Arithmetic Operators

Below are Hive Arithmetic Operators.

Arithmetic OperatorsDescription
A + BReturns result after adding A and B.
When you add integer with float, it returns the Float data type.
Operand Types: All number types
A – BReturns result after subtracting B from A.
When you subtract an integer from the float, it returns the Float data type.
Operand Types: All number types
A * BReturns result after multiplying A and B.
Operand Types: All number types
A / BReturns result after dividing A by B. In most cases, the result would be in Double type.
Operand Types: All number types
A DIV BReturns the integer part resulting from dividing A by B.
Operand Types: All number types
A % BReturns the remainder resulting from dividing A by B.
Operand Types: All number types
A & BGives the result of bitwise AND of A and B.
Operand Types: All number types
A | BGives the result of bitwise OR of A and B.
Operand Types: All number types
A ^ BGives the result of bitwise XOR of A and B.
Operand Types: All number types
~AGives the result of bitwise NOT of A. The type of the result is the same as the type of A.
Operand Types: All number types

Leave a Reply