SAP HANA Operator can be used for calculation, value comparison or to assign value. SAP HANA Contain below operators-

Unary and Binary Operator

Arithmetic Operator

String Operators

Comparison Operator

Logical Operator

Set Operator

Unary and Binary Operator

Operator Operation Description
Unary A Unary operator applies to one operand Unary plus operator(+) Unary negation operator(-) Logical negation(NOT)
Binary A Binary Operator applies on two operand Multiplicative operators ( *, / ) Additive operators ( +,- ) Comparison operators ( =,!=,<,>,<=,>=) Logical operators ( AND, OR )

Arithmetic Operator

• Subtraction (-)
• Multiplication ( * )
• Division ( / )

String Operator

A String Operator is a concatenation operator which combines two items such as strings, expressions or constants into one.

Two Vertical Bar "||" is used as the concatenation operator.

Comparison Operator

Comparison operator is used to compare two operand. Below are list of Comparison Operator-

• Equal to ( = )
• Greater Than ( > )
• Less Than ( < )
• Greater than or equal to ( > = )
• Less than or equal to ( < = )
• Not Equal (!= , <> )

Logical Operator

Logical operator is used in search criteria.

E.g. WHERE condition1 AND / OR / NOT condition2

Below is list of logical operator –

• AND - (e.g. WHERE condition1 AND condition2)

If both Condition1 AND Condition2 are true, then Combine condition is true else it will false.

• OR – (e.g. WHERE condition1 OR condition2)

If Condition1 OR Condition2 is true, then combine condition is true or false if both Conditions are false.

• NOT - (e.g. WHERE NOT condition)

NOT condition is true If Condition is false.

Set Operators

• UNION - Combines two or many select statements or query without duplicate.
• UNION ALL - Combines two or many select statements or query, including all duplicate row.
• INTERSECT - Combines two or many select statements or query, and return all common rows.
• EXCEPT - Takes the output from the first query and removes row selected by the second query.

E.g.

I have two table (table1, table2) in which some values are common.

We use Set operator (Union, Union ALL, Intersect, except) for these two table in SQL as below –

Create Table1- SQL Script

```CREATE	 COLUMN TABLE DHK_SCHEMA.TABLE1
(        ELEMENT CHAR(1),
PRIMARY KEY (ELEMENT)
);
INSERT INTO DHK_SCHEMA.TABLE1 VALUES ('P');
INSERT INTO DHK_SCHEMA.TABLE1 VALUES ('Q');
INSERT INTO DHK_SCHEMA.TABLE1 VALUES ('R');
INSERT	INTO DHK_SCHEMA.TABLE1 VALUES ('S');
INSERT	INTO DHK_SCHEMA.TABLE1 VALUES ('T');
```

Create Table2- SQL Script

```CREATE	COLUMN TABLE DHK_SCHEMA.TABLE2
(        ELEMENT CHAR(1),
PRIMARY KEY (ELEMENT)
);
INSERT INTO DHK_SCHEMA.TABLE2 VALUES ('S');
INSERT INTO DHK_SCHEMA.TABLE2 VALUES ('T');
INSERT INTO DHK_SCHEMA.TABLE2 VALUES ('U');
INSERT INTO DHK_SCHEMA.TABLE2 VALUES ('V');
INSERT INTO DHK_SCHEMA.TABLE2 VALUES ('W');
```

Note: Here "DHK_SCHEMA" is a schema name, the user can change schema name in SQL accordingly.

Set Operator Examples are as below -

Operator
SQL Query
Output
Uses
UNION SELECT *

FROM

(

SELECT ELEMENT

FROM DHK_SCHEMA.TABLE1

UNION

SELECT ELEMENT

FROM DHK_SCHEMA.TABLE2

) ORDER BY ELEMENT;

Combine Result of two or more query with no duplicate.
UNION ALL SELECT *

FROM

(

SELECT ELEMENT

FROM DHK_SCHEMA.TABLE1

UNION ALL

SELECT ELEMENT

FROM DHK_SCHEMA.TABLE2

)

ORDER BY ELEMENT;
Combine Result of two or more query with all duplicate.
INTERSECT SELECT *

FROM

(

SELECT ELEMENT

FROM DHK_SCHEMA.TABLE1

INTERSECT

SELECT ELEMENT

FROM DHK_SCHEMA.TABLE2

)

ORDER BY ELEMENT;
Combine Result of two or more query with all common rows.
EXCEPT SELECT *

FROM

(

SELECT ELEMENT

FROM DHK_SCHEMA.TABLE1

EXCEPT

SELECT ELEMENT

FROM DHK_SCHEMA.TABLE2

)

ORDER BY ELEMENT;
Takes output from first query and removes row selected by the second query

YOU MIGHT LIKE: