###### Summary -

In this topic, we described about the below sections -

• Built-In Operators

There are four types of built-in operators available in Hive.

1. Relational
2. Arithmetic
3. Logical
4. Complex Operators

Let us discuss about each type in detail.

## Relational Operators –

Relational Operators are used to compare the operands. Relational Operators generate a TRUE or FALSE value depending on condition.

Relational OperatorOperand typesDescription
A = B all primitive types TRUE if expression A is equivalent to expression B otherwise FALSE
A != B all primitive types TRUE if expression A is not equivalent to expression B otherwise FALSE
A < B all primitive types TRUE if expression A is less than expression B otherwise FALSE
A <= B all primitive types TRUE if expression A is less than or equal to expression B otherwise FALSE
A > B all primitive types TRUE if expression A is greater than expression B otherwise FALSE
A >= B all primitive types TRUE if expression A is greater than or equal to expression B otherwise FALSE
A IS NULL all types TRUE if expression A evaluates to NULL otherwise FALSE
A IS NOT NULL all types FALSE if expression A evaluates to NULL otherwise TRUE
A LIKE B strings TRUE if string A matches the SQL simple regular expression B, otherwise FALSE.
A RLIKE B strings NULL if A or B is NULL, TRUE if any (possibly empty) substring of A matches the Java regular expression B, otherwise FALSE.
A REGEXP B strings NULL if A or B is NULL, TRUE if any (possibly empty) substring of A matches the Java regular expression B, otherwise FALSE.

#### Example -

Assume we have a requirement to create student information with the columns student number, name, class, grade for a school.

1Pawan10A
2Srinivas10A
3Sridhar10A
4Kumar10B

The below are the examples which shows usage of relational operators on table columns.

Relational OperatorExample RequirementExample Query
A = B Get the student details studying 10th class ` SELECT * FROM std_db.std_details WHERE std_class = 10 `
A != B Get the student details not studying 10th class ` SELECT * FROM std_db.std_details WHERE std_class != 10 `
A < B Get the student details from 1st to 7th class ` SELECT * FROM std_db.std_details WHERE std_class < 8 `
A <= B Get the student details from 1st to 7th class ` SELECT * FROM std_db.std_details WHERE std_class <= 7 `
A > B Get the student details from 8th to 10th class ` SELECT * FROM std_db.std_details WHERE std_class > 7 `
A >= B Get the student details from 8th to 10th class ` SELECT * FROM std_db.std_details WHERE std_class >= 8 `
A IS NULL Get the student details without having any class details in database. ` SELECT * FROM std_db.std_details WHERE std_class IS NULL `
A IS NOT NULL Get the student details having any class details in database. ` SELECT * FROM std_db.std_details WHERE std_class IS NOT NULL `
A LIKE B Get the student details whose names starts with S letter. ` SELECT * FROM std_db.std_details WHERE std_name LIKE ‘S%’ `
A RLIKE B Get the student details whose names starts with S letter. ` SELECT * FROM std_db.std_details WHERE std_name RLIKE ‘S%’ `
A REGEXP B Get the student details whose names starts with S letter. ` SELECT * FROM std_db.std_details WHERE std_name REGEXP ‘S%’ `

## Arithmetic Operators –

Arithmetic operators are to support common arithmetic operations. The input and output by using arithmetic operators are numbers or numeric.

Arithmetic OperatorsOperand typesDescription
A + B all number types Gives the result of adding A and B.
A - B all number types Gives the result of subtracting B from A.
A * B all number types Gives the result of multiplying A and B.
A / B all number types Gives the result of dividing B from A.
A % B all number types Gives the reminder resulting from dividing A by B.
A & B all number types Gives the result of bitwise AND of A and B.
A | B all number types Gives the result of bitwise OR of A and B.
A ^ B all number types Gives the result of bitwise XOR of A and B.
~A all number types Gives the result of bitwise NOT of A.

#### Example -

Assume we have a requirement to create student information with the columns student number, name, class, grade for a school.

1Pawan10A
2Srinivas10A
3Sridhar10A
4Kumar10B

The below are the examples which shows usage of arithmetic operators on table columns.

Arithmetic OperatorsExample RequirementExample Query
A + B Adding 20 and 30 ` SELECT 20+30 AS RESULT FROM temp `
A - B Subtracting 20 from 50 ` SELECT 50-20AS RESULT FROM temp `
A * B Multiplying 3 and 4 ` SELECT 3*4 AS RESULT FROM temp `
A / B Dividing 100 by 10 ` SELECT 100/10 AS RESULT FROM temp `
A % B 10% ` SELECT 100%10 AS RESULT FROM temp `

## Logical Operators –

Logical operators are used to create logical expressions. All of them return boolean TRUE or FALSE based on the values in operands.

Logical OperatorsOperands typesDescription
A AND B boolean TRUE if both A and B are TRUE, otherwise FALSE
A && B boolean Same as A AND B
A OR B boolean TRUE if either A or B or both are TRUE, otherwise FALSE
A || B boolean Same as A OR B
NOT A boolean TRUE if A is FALSE, otherwise FALSE
!A boolean Same as NOT A

## Complex Operators –

Complex operators provide tools to access elements in Complex Types.

OperatorOperand typesDescription
A[n] A is an Array and n is an int "Returns the nth element in the array A. The first element has index 0 "
M[key] M is a Map and key has type K returns the value corresponding to the key in the map
S.x S is a struct returns the x field of S