###### Summary -

In this topic, we described about the below sections -

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

- Relational
- Arithmetic
- Logical
- 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 Operator | Operand types | Description |
---|---|---|

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.

Std No | Std Name | Std Class | Std Grade |
---|---|---|---|

1 | Pawan | 10 | A |

2 | Srinivas | 10 | A |

3 | Sridhar | 10 | A |

4 | Kumar | 10 | B |

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

Relational Operator | Example Requirement | Example 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 Operators | Operand types | Description |
---|---|---|

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.

Std No | Std Name | Std Class | Std Grade |
---|---|---|---|

1 | Pawan | 10 | A |

2 | Srinivas | 10 | A |

3 | Sridhar | 10 | A |

4 | Kumar | 10 | B |

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

Arithmetic Operators | Example Requirement | Example 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 Operators | Operands types | Description |
---|---|---|

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.

Operator | Operand types | Description |
---|---|---|

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 | returns the value corresponding to the key in the map |

S.x | S is a struct | returns the x field of S |