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