SAP HANA Operator: Union & Union All

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

Arithmetic Operator

• Addition (+)
• 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 -