SQL SERVER – What is a self join and Explain it with an example

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.

Ex:

CREATE TABLE emp ( empid int, mgrid int, empname char(10) )

INSERT emp SELECT 1,2,’Vyas’

INSERT emp SELECT 2,3,’Mohan’

INSERT emp SELECT 3,NULL,’Shobha’

INSERT emp SELECT 4,2,’Shridhar’

INSERT emp SELECT 5,2,’Sourabh’

SELECT t1.empname [Employee], t2.empname [Manager] FROM emp t1, emp t2 WHERE t1.mgrid = t2.empid

Here”s an advanced query’ using a LEFT OUTER JOIN that even returns the employees without managers (super bosses) 

SELECT t1.empname [Employee], COALESCE(t2.empname, ‘No manager’) [Manager] FROM emp t1 LEFT OUTER JOIN emp t2 ON t1.mgrid = t2.empid

Dilip Kumar Jena ( http://sqlexplore.wordpress.com )

About these ads

2 thoughts on “SQL SERVER – What is a self join and Explain it with an example

  1. Rather good posting. I just stumbled upon an individual’s blog together with wanted that will tell you the fact that I have quite enjoyed analyzing an individual’s blog site together with reports. Anyway I’ll get subscribing an individual’s provide for together with That i intend that will read an individual’s blog site for a second time.

  2. I wish more people would write blogs like this that are really fun to read. With all the fluff floating around on the net, it is rare to read a blog like this instead.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s