• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • AI
  • Blog

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 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.

SAP HANA Operator: Union & Union All

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;

SAP HANA Operator: Union & Union All

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;

SAP HANA Operator: Union & Union All

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;

SAP HANA Operator: Union & Union All

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;

SAP HANA Operator: Union & Union All

Takes output from first query and removes row selected by the second query

 

YOU MIGHT LIKE: