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
Operator Operation Description
Unary A Unary Unary plus operator(+) Unary
operator negation operator(-) Logical
applies to one negation(NOT)
operand
Binary A Binary Multiplicative operators ( *, /
Operator ) Additive operators ( +,- )
applies on two Comparison operators (
operand =,!=,<,>,<=,>=) 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 SQLaccordingly.
Set Operator Examples are as below -
Operator SQL Query Output Uses
UNION SELECT * Combine
Result of
FROM two or
more
( query
with no
SELECT ELEMENT duplicate.
FROMDHK_SCHEMA.TABLE1
UNION
SELECT ELEMENT
FROMDHK_SCHEMA.TABLE2
) ORDER BY ELEMENT;
UNION SELECT * Combine
ALL Result of
FROM two or
more
( query
with all
SELECT ELEMENT duplicate.
FROMDHK_SCHEMA.TABLE1
UNION ALL
SELECT ELEMENT
FROMDHK_SCHEMA.TABLE2
ORDER BY ELEMENT;
INTERSECT SELECT * Combine
Result of
FROM two or
more
( query
with all
SELECT ELEMENT common
rows.
FROMDHK_SCHEMA.TABLE1
INTERSECT
SELECT ELEMENT
FROMDHK_SCHEMA.TABLE2
ORDER BY ELEMENT;
EXCEPT SELECT * Takes
output
FROM from first
query
( and
removes
SELECT ELEMENT row
selected
FROMDHK_SCHEMA.TABLE1 by the
second
EXCEPT query
SELECT ELEMENT
FROMDHK_SCHEMA.TABLE2
ORDER BY ELEMENT;
