SAP HANA Union & Union All: Operator Examples

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

Every operator returns a value, which is why they can be nested inside one another. The order in which they are evaluated is set by precedence, explained after the individual families.

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

Set Operator Rules and Requirements

Set operators look simple but reject queries readily, because they compare whole result sets rather than individual values. Four rules govern them.

1. Column count must match. Both SELECT statements must return the same number of columns. A mismatch fails immediately, and the message names the position rather than the column.
2. Data types must be compatible. Column one of the first query is matched against column one of the second, and so on by position, not by name. Compatible numeric types are converted automatically; a character column against a date is not.
3. Column names come from the first query. Whatever the second statement calls its columns is ignored in the output, which surprises anyone reading the result of a UNION for the first time.
4. ORDER BY applies to the whole result. It belongs at the end, after the last SELECT, and is why the examples above wrap the union in an outer SELECT.

The performance difference between UNION and UNION ALL is worth noting. UNION must sort or hash the combined result to remove duplicates, while UNION ALL simply appends. Where duplicates are impossible, or acceptable, UNION ALL is meaningfully faster on large tables. The column definitions used in these tables are covered in SAP HANA data types.

Operator Precedence in SAP HANA SQL

When several operators appear in one expression, precedence decides which is evaluated first. Getting this wrong produces a query that runs perfectly and returns the wrong rows, which is far more dangerous than a syntax error.

The classic trap sits at rows 7 and 8. Because AND binds tighter than OR, the condition WHERE region = ‘EAST’ OR region = ‘WEST’ AND status = ‘OPEN’ is read as EAST, or else WEST that is open. Every eastern row is returned regardless of status. Adding parentheses around the two region tests fixes it.

Everyday statement syntax is covered in the SAP HANA SQL tutorial, and the wider platform in the SAP HANA tutorial series.

FAQs