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.