Skip to Content
Technical Articles
Author's profile photo Subhendu Ghanty

Non Equi join in SAP HANA 2.0 Web IDE

Purpose:

Purpose of this blog is to demonstrate the use of “Non Equi join” in SAP HANA Modelling. SAP HANA 1.0 modelling does not support the Non Equi join between two data set, however in hana 2.0 SAP Web IDE modelling new feature added to support Non Equi join.

What is Non Equi Join?

As per SAP Documentation Non-equi joins are joins whose join conditions use conditional operators other than equals.

A non equi join uses comparison operators like !=, >, <, >= and <= to query data from two data sources. Like a normal join, it allows you to specify the cardinality and a join type (Inner, Left Outer, Right Outer) to query data from two data sources. If multiple pairs of left and right columns are participating in join definition, each pair can have different operator.

Use case:

Non equi join can be used to detect duplicate data between tables. In This blog I’ve used non equi join to detect duplicate names in our employee dataset.

Table structure

Table Data:

In my example I will detect duplicate entries of First Name and Last Name of employees table.Here Combination of Business entity ID , First Name and last name makes the record unique, which is composite key in my Employee Table.

So the solution I used to detect the duplicate entry is that I self joined the Employee table with itself on the basis of First Name and Last Name where they are matching and Business Entity ID where they are mismatching.This is the place where Non – equi join comes into the picture.

Solution-1 ( Using SQL Query):

SELECT 
	t1."BUSINESS_ENTITY_ID",
	t1."FIRST_NAME",
	t1."LAST_NAME"
FROM   "DB_45"."Employee.EMPLOYEE" t1 join 
        "DB_45"."Employee.EMPLOYEE" t2
on     t1."FIRST_NAME" = t2."FIRST_NAME"
and    t2."LAST_NAME" = t2."LAST_NAME"
and    t1."BUSINESS_ENTITY_ID" <> t2."BUSINESS_ENTITY_ID";

Query output you can see as below which has detected duplicate

Solution-2 ( Using Graphical modelling):

Model Output:

SQL used in this blog can be find below:

context Employee {

    @Comment : 'Employee Details'
    entity EMPLOYEE {
            KEY BUSINESS_ENTITY_ID : Integer; 
            KEY FIRST_NAME :  String(50);
            KEY LAST_NAME :  String(50);
    }
    technical configuration {
        column store;
    };

};

delete from "DB_45"."Employee.EMPLOYEE";
insert into "DB_45"."Employee.EMPLOYEE" values ( 1,'Subhas','Bose');
insert into "DB_45"."Employee.EMPLOYEE" values ( 1,'Rabindranath','Tagore');
insert into "DB_45"."Employee.EMPLOYEE" values ( 1,'Arabindo','Ghosh');
insert into "DB_45"."Employee.EMPLOYEE" values ( 1,'John','Doe');
insert into "DB_45"."Employee.EMPLOYEE" values ( 1,'Subhendu','Ghanty');
insert into "DB_45"."Employee.EMPLOYEE" values ( 2,'Subhas','Bose');
insert into "DB_45"."Employee.EMPLOYEE" values ( 2,'Rabindranath','Tagore');
insert into "DB_45"."Employee.EMPLOYEE" values ( 2,'Subhendu','Ghanty');
select * from "DB_45"."Employee.EMPLOYEE";

Conclusion:

Using HANA 2.0 WEB IDE we are getting lots of new advanced database feature like “Non Equi Join” which makes a Developers life easy 🙂

Which can be used to remove duplicate as I’ve demonstrated above.

 

Please feel free to comment/ add any suggestion on this.

Assigned tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Anupam Dash
      Anupam Dash

      Nice Blog

      Author's profile photo Subhendu Ghanty
      Subhendu Ghanty
      Blog Post Author

      Thanks Anupam Dash

      Author's profile photo Former Member
      Former Member

      You were great and everyone received so much from your experience and knowledge
      Absolutely amazing, thank you for sharing your knowledge with me.

      Author's profile photo Naim Khan
      Naim Khan

      Informative

      Author's profile photo Harshith Tenneti
      Harshith Tenneti

      Very informative. Can you please share the summarized new technical features in HANA 2.0.

      Author's profile photo Subhendu Ghanty
      Subhendu Ghanty
      Blog Post Author

      Thanks Harshith Tenneti . I will try to come up with SAP blog consolidating new features of HANA 2.0