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 Operators | Description |
|---|---|
| A = B | Returns TRUE when A is equal to B, FLASE when they are not equal. Operand Types: All primitive types |
| A == B | Similar to = operator. Operand Types: All primitive types |
| A <=> B | Same 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 <> B | Returns TRUE if A is not equal to B, otherwise FALSE. Returns NULL if A or B is NULL Operand Types: All primitive types |
| A != B | Similar to <> operator. Operand Types: All primitive types |
| A < B | Returns 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 <= B | Returns 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 > B | Returns 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 >= B | Returns 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 C | Returns 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 NULL | Returns TRUE if A is NULL, otherwise FALSE. Operand Types: All types |
| A IS NOT NULL | Returns 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 B | TRUE 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 B | Same as RLIKE. Operand Types: strings |
Hive Logical Operators
Below are Hive Logical Operators.
| Logical Operators | Description |
|---|---|
| A AND B | Evaluates 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 B | Evaluates to TRUE if either A or B or both are TRUE, FALSE OR NULL is NULL, otherwise FALSE. Operand Types: boolean |
| NOT A | Evaluates to TRUE if A is FALSE or NULL if A is NULL. Evaluates to FALSE if A is TRUE Operand Types: boolean |
| ! A | Similar 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 Operators | Description |
|---|---|
| A + B | Returns result after adding A and B. When you add integer with float, it returns the Float data type. Operand Types: All number types |
| A – B | Returns 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 * B | Returns result after multiplying A and B. Operand Types: All number types |
| A / B | Returns result after dividing A by B. In most cases, the result would be in Double type. Operand Types: All number types |
| A DIV B | Returns the integer part resulting from dividing A by B. Operand Types: All number types |
| A % B | Returns the remainder resulting from dividing A by B. Operand Types: All number types |
| A & B | Gives the result of bitwise AND of A and B. Operand Types: All number types |
| A | B | Gives the result of bitwise OR of A and B. Operand Types: All number types |
| A ^ B | Gives the result of bitwise XOR of A and B. Operand Types: All number types |
| ~A | Gives 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 |
