# 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

• 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