SQL Operators in SAP HANA – How to Implement the Operators Quickly

SQL is an important language as it is used for command-line operations in SAP HANA. In this tutorial, we will learn about the different kinds of operators used in the SAP HANA SQL scripting.

We assure you that by the end of this tutorial, you will gain perfection in using SQL operators while working on SAP HANA.

SQL Operators in SAP HANA

SQL operators are used to perform certain operations on the values and expressions used in SQL script in SAP HANA. Using the operators, one can perform calculations, comparisons, string operations, set operations and arithmetic operations on the SQL statements.

There are two types of operators in any scripting language; unary operators and binary operators.

1. Unary Operator

The Unary Operators works only on one Operand. The format in which a unary operator typically appears is, operator operand. Like -1, where – is the unary minus operator and 1 is the operand.

There are several unary operators like:

• Increment and Decrement operators having symbols (++) and (–) respectively.
• Single/Unary Plus and Minus operators having symbols (+) and (-) respectively.
• Not or negation operator with a symbol ‘!’ is also a unary operator.

2. Binary Operator

The Binary Operator operates or works on two operands. The format is, operand1 operator operand2. The binary plus operator used for adding two numbers, 5+2, where + is the binary operator and 5 and 2 are the two operands.

Operator Precedence

If an SQL statement in SAP HANA uses more than one operator, then it gets evaluated as per the order of operator precedence. That is, the first operator in the order of precedence will get evaluated first followed by the operators of lower precedence.

In the case of SAP HANA, anything enclosed in parentheses will be evaluated first. The table given below shows the order of operator precedence from high to low.

Types of Operators Used in SAP HANA

Here are the types of operators used in SQL statements while performing operations on SAP HANA:

1. Arithmetic Operators

The numeric operators are the most common and widely used mathematical symbols like +, -, *, / etc. These operators take up numerical values as it is, perform mathematical operations on them according to the operator and return a numeric value.

• Operator: +

Syntax:

<expression> + <expression>

Description: Arithmetic addition returns the sum of the two operands.

• Operator: –

Syntax:

<expression> - <expression>

Description: Arithmetic subtraction returns the difference between the two operands.

• Operator: *

Syntax:

<expression> * <expression>

Description: Arithmetic multiplication returns the product of the two operands.

• Operator: /

Syntax:

<expression> / <expression>

Description: Arithmetic division returns the ratio between the two operands.

• Operator: – (Unary)

Syntax:

-<expression>

Description: Negation operator which is a unary operator i.e. used with only one operand.

2. String Operator

• Operator: ||

Syntax:

<expression>||<expression>

Description: This is the concatenation operator to combine two items. The two operands on either side of the operator can be strings, expressions, constants, etc.

3. Comparison Operators

The comparison operators are used to compare two values and return the resultant value.

• Operator: =

Description: Equal to operator

SQL Statement Example:

SELECT*FROM Employee WHERE age=50;

• Operator: >

Description: Greater than operator

SQL Statement Example:

SELECT*FROM Employee WHERE age>50;

• Operator: <

Description: Less than operator

SQL Statement Example:

SELECT*FROM Employee WHERE age<50;

• Operator: >=

Description: Greater than or equal to operator

SQL Statement Example:

SELECT*FROM Employee WHERE age>=50;

Operator: <=

Description: Less than or equal to operator

SQL Statement Example:

SELECT*FROM Employee WHERE age<=50;

Operator: <>, !=

Description: Not equal to operator

SQL Statement Example:

SELECT*FROM Employee WHERE age<>50; or SELECT*FROM Employee WHERE age!=50;

4. Logical Operators

• Operator: AND

Syntax:

WHERE condition1 AND conditon2

Description: The result is TRUE only if both the conditions are TRUE together, otherwise FALSE.

• Operator: OR

Syntax:

WHERE condition1 OR condition2

Description: The result is TRUE if either one of the two conditions (condition1 or condition2) is TRUE, otherwise FALSE.

Operator: NOT

Syntax:

WHERE NOT condition

Description: It is used before a condition to negate it. That is, if a condition holds TRUE then this operator will make it FALSE (in the output) or if a condition is FALSE, it will make it TRUE in the output.

5. Set Operators

Set operators combine multiple queries and return a single result set in output.

Operator: UNION

Description: This operator combines the outputs of two or more SQL select statements or query expressions.

Operator: UNION ALL

Description: This operator also combines all the outputs of two or more SQL select statements or query expressions but it also includes all duplicate rows.

Operator: INTERSECT

Description: This operator combines the outputs of two or more SQL select statements or query expressions and returns all the common rows.

Operator: EXCEPT

Description: This operator takes the result from the first query, removes the rows selected by the second query and gives the output. It is also referred to as MINUS.

Summary

This concludes our tutorial on SQL operators in SAP HANA. We hope you found the explanation helpful.

If you have any feedback for us, enter in the comment section. We will be glad to hear from you.

Stay tuned as we bring more tutorials on SAP HANA to you. You can also check the process to create SQL Explain Plans in SAP HANA studio.