A join called " Self join"

Recently, when I took quite a many interviews, one of the most commonly question I faced was "what is Self Join?".

I know the word "Self" explains it all, but let us try to understand it more explicitly!

Self join is something when a table is joined to itself. 

Okay! Let us now try to learn things through an example.

Self join is used when you have to find the relationship between rows of the same table/dataset. The most essential thing for a self join is defining "alias"( nickname) . We define two different alias i.e. give two different nicknames to a table, since we are basically using the same table twice.

In data 1 you have people's name and name of their father. The task for you is to find out for each person their corresponding grandfather's name.

Data Parent_data;

input name : $15. Father_name :$15.;

cards;

Amitabh Harivansh

Abhishek Amitabh

Aaradhya Abhishek

;

Run;

Proc SQL;

Create table child_data as select a.name, b.Father_name as gradfather_nm

from  Parent_data as a , Parent_data as b

where b.name = a.Father_name ;

Quit;

and you will get :

So, if you notice here the dataset parent_data has been used twice with alias a and b. The twist here is that you join the table(s) matching one column with another column of the same dataset. e.g. we are matching name with father_name.

Still confused????

Not to worry, let me try to explain this to you with another example.

Suppose you have a dataset "employee" with columns : emp_id, emp_name &  mgr_id.

Data employee;

input emp_id emp_name $ mgr_id ;

cards;

1001 Sam 1010

1002 Rishi 1011

1010 Kashi 1012

1011 Kamal 1012

1003 Rashi 1013

1013 Kamna 1012

1012 Raj   .

;

Run;

Now using self join, you want identify the employee name and the his/her manager's name; here you go :

Proc SQL;
Create table result as 
Select a.emp_name , b.emp_name as manager_name
from employee as a , employee as b
where  a.mgr_id = b.emp_id; 
Quit;

And the result is,

"  There is nothing to confuse, if you imagine that 

there are two datasets instead of just one.  "

I think you are clear about the term self join by now. Use it carefully though !


Enjoy reading our other articles and stay tuned with us.

Kindly do provide your feedback in the 'Comments' Section and share as much as possible.