Union & Union All in SAP Hana

SAP HANA Operator can be used for calculation, value comparison or to assign value.

SAP HANA 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

• 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

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