In this Blog, I will explain/show all Set Operators using SQL Code and also in HANA Studio/WEB IDE using Calculation Views. In latest versions of HANA we can see all Set Operators INTERSECT and MINUS set operations in WEB IDE. i.e. below HANA 2.0 SP01 versions, we don't have INTERSECT and MINUS set operations Calculation Views. To achieve INTERSECT and MINUS set operations nodes (like Union, Join, Aggregation, Projection etc.. ) this blog is useful.
I started all SAP HANA articles with very simple examples, these simple examples are useful/helpful to understand my future articles, slowly we will move towards more SQL Code and complex scenarios.
In all below examples I used SQL Code and HANA Studio Only.
Set operators allow multiple queries to be combined to return a single result set. Set Operators are used to combine data sets whose METADATA is compatible and also NO relationship is required between data sets.
We have four different types of Set Operators.
UNION: Combines the results of two or more select statements or query expressions. UNION operations like "APPEND" operations WITHOUT duplicate records. i.e. UNION will add records from first data set to second data set at the end (of second data set).
Note: WITHOUT duplicate records
UNION ALL: Combines the results of two or more select statements or query expressions, including all duplicate rows. UNION ALL will combine data sets including duplicate records. In HANA Modeling (HANA Studio GUI/WEB IDE), in Calculation View "UNION" node implements UNION ALL Operator.
Note: WITH duplicate records.
INTERSECT: Combines the results of two or more select statements or query expressions, and returns all common rows. i.e. To combine data sets and to select Only Duplicate records.
EXCEPT/MINUS: Takes the output from the first query and then removes the rows selected by the second query. MINUS is an accepted synonym for EXCEPT. i.e. To select the records from First Data set only which are NOT repeated in Second Data set.
To explain all above Set Operators, I took below two simple tables with sample data.
See the Meta Data of the both tables, basically they are same but contains different Countries Product Data.
See the data in below image, observe the data in both tables.
In above both tables, Product Id 101 is common.
UNION: Using SQL Code
If we apply UNION between the above two tables, see the results in below image. Though Product ID 101 is Common in both tables, but it shows one time only i.e. UNION will not consider duplicate records.
The results of above query is like below.
UNION: Using HANA Modeling/HANA Studio
Note: No Changes at Aggregation Node, Projection Node and Semantics. Here Aggregation Node will aggregate data from both tables. If we don't use this node then it acts like UNION ALL.
UNION ALL: Using SQL Code
The results of above query is like below.
UNION ALL: Using HANA Modeling/HANA Studio
Display Data in RAW Date format.
INTERSECT: Using SQL Code
If we apply INTERSECT between the above two tables, see the results in below image. Product ID 101 is Common in both tables, and it will display only that record.
The Results of above query is...
INTERSECT: Using HANA Modeling/HANA Studio
This is little bit tricky, we have to apply some logic to achieve INTERSECT in HANA Studio.
Create a CV same as like above two CV's and Mapping also same at UNION Level. Apart from mapping for existing fields, we have to create a DUMMY field/column in TARGET with Data Type INTEGER.
Right Click and Manage Mappings for DUMMY column. For PRODUCTS_US, assign 1 as Constant Value and for PRODUCTS_CA, assign -1 as Constant Value.
Add an AGGREGATE Node and select DUMMY Field/Column in the Property window, make sure that "Engine Aggregation" is "SUM".
In Filters, Open Expression and give "DUMMY"=0, because we need only Common Records. In above UNION Node we assigned 1 and -1 for tables, this will be used at AGGREGATION Node. If all field values are same, then it will become 1-1 = 0 (ZERO) so it will display/fetch/filter only Records which are exists in both tables.
In final Projection, HIDE Column DUMMY.
See the images of all above steps.
HIDE/Remove DUMMY Column in top Projection Node.
Display the Data in Raw Data/ Analysis
EXCEPT/MINUS: Using SQL Code
If we apply EXCEPT/MINUS between the above two tables, see the results in below image. Product ID 101 is Common in both tables, so it will not display from LEFT Table and EXCEPT that record it will display all records from LEFT Table.
The results of above queries looks like below, both are same we can use EXCEPT or MINUS.
In above results, we ca'r see Product Id 101, because it is common in both tables, so it is eliminated from LEFT Table PRODUCTS_US.
EXCEPT/MINUS: Using HANA Modeling/HANA Studio
This logic also exactly same as INTERSECT, but one change we have to do in AGGREGATION Node, in Filters, Open Expression and give "DUMMY"=1. In this case the data will display from Left Table, if you want to display data from PRODUCTS_CA then you first/left table should be PRODUCTS_CA and also Filter Expression will be -1.
The Results of above model is...
WEB IDE Screen:
I started all SAP HANA articles with very simple examples, these simple examples are useful/helpful to understand my future articles, slowly we will move towards more SQL Code and complex scenarios.
In all below examples I used SQL Code and HANA Studio Only.
Set Operators:
Set operators allow multiple queries to be combined to return a single result set. Set Operators are used to combine data sets whose METADATA is compatible and also NO relationship is required between data sets.
We have four different types of Set Operators.
UNION: Combines the results of two or more select statements or query expressions. UNION operations like "APPEND" operations WITHOUT duplicate records. i.e. UNION will add records from first data set to second data set at the end (of second data set).
Note: WITHOUT duplicate records
UNION ALL: Combines the results of two or more select statements or query expressions, including all duplicate rows. UNION ALL will combine data sets including duplicate records. In HANA Modeling (HANA Studio GUI/WEB IDE), in Calculation View "UNION" node implements UNION ALL Operator.
Note: WITH duplicate records.
INTERSECT: Combines the results of two or more select statements or query expressions, and returns all common rows. i.e. To combine data sets and to select Only Duplicate records.
EXCEPT/MINUS: Takes the output from the first query and then removes the rows selected by the second query. MINUS is an accepted synonym for EXCEPT. i.e. To select the records from First Data set only which are NOT repeated in Second Data set.
To explain all above Set Operators, I took below two simple tables with sample data.
See the Meta Data of the both tables, basically they are same but contains different Countries Product Data.
See the data in below image, observe the data in both tables.
In above both tables, Product Id 101 is common.
Scenario : 1
UNION: Using SQL Code
If we apply UNION between the above two tables, see the results in below image. Though Product ID 101 is Common in both tables, but it shows one time only i.e. UNION will not consider duplicate records.
The results of above query is like below.
UNION: Using HANA Modeling/HANA Studio
Note: No Changes at Aggregation Node, Projection Node and Semantics. Here Aggregation Node will aggregate data from both tables. If we don't use this node then it acts like UNION ALL.
Scenario : 2
UNION ALL: Using SQL Code
If we apply UNION ALL between the above two tables, see the results in below image. Product ID 101 is Common in both tables, and it shows two times i.e. UNION ALL will consider duplicate records.
The results of above query is like below.
UNION ALL: Using HANA Modeling/HANA Studio
Display Data in RAW Date format.
Scenario : 3
INTERSECT: Using SQL Code
If we apply INTERSECT between the above two tables, see the results in below image. Product ID 101 is Common in both tables, and it will display only that record.
The Results of above query is...
INTERSECT: Using HANA Modeling/HANA Studio
This is little bit tricky, we have to apply some logic to achieve INTERSECT in HANA Studio.
Create a CV same as like above two CV's and Mapping also same at UNION Level. Apart from mapping for existing fields, we have to create a DUMMY field/column in TARGET with Data Type INTEGER.
Right Click and Manage Mappings for DUMMY column. For PRODUCTS_US, assign 1 as Constant Value and for PRODUCTS_CA, assign -1 as Constant Value.
Add an AGGREGATE Node and select DUMMY Field/Column in the Property window, make sure that "Engine Aggregation" is "SUM".
In Filters, Open Expression and give "DUMMY"=0, because we need only Common Records. In above UNION Node we assigned 1 and -1 for tables, this will be used at AGGREGATION Node. If all field values are same, then it will become 1-1 = 0 (ZERO) so it will display/fetch/filter only Records which are exists in both tables.
In final Projection, HIDE Column DUMMY.
See the images of all above steps.
HIDE/Remove DUMMY Column in top Projection Node.
Display the Data in Raw Data/ Analysis
Scenario : 4
EXCEPT/MINUS: Using SQL Code
If we apply EXCEPT/MINUS between the above two tables, see the results in below image. Product ID 101 is Common in both tables, so it will not display from LEFT Table and EXCEPT that record it will display all records from LEFT Table.
The results of above queries looks like below, both are same we can use EXCEPT or MINUS.
In above results, we ca'r see Product Id 101, because it is common in both tables, so it is eliminated from LEFT Table PRODUCTS_US.
EXCEPT/MINUS: Using HANA Modeling/HANA Studio
This logic also exactly same as INTERSECT, but one change we have to do in AGGREGATION Node, in Filters, Open Expression and give "DUMMY"=1. In this case the data will display from Left Table, if you want to display data from PRODUCTS_CA then you first/left table should be PRODUCTS_CA and also Filter Expression will be -1.
The Results of above model is...
WEB IDE Screen:
If we use WEB IDE (for latest versions of HANA DB) we can see the new options i.e. INTERSECTION and EXCEPT/MINUS. I shown all examples in HANA Studio, we can replicate the same in WEB IDE also, but it will increase number of screenshots. I opened one CV in WEB IDE and shown above.
Basically I conclude that, by looking at above all Scenarios and Examples, we can achieve all Set Operators logic using SAP HANA GUI with simple steps at UNION and AGGREGATION Nodes.
- SAP Managed Tags:
1 Comment
