What is a self join? Explain it with an example

Posted by

To view Verified answers click on the button below.

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.

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