Thursday, May 30, 2013

What is a Self join in SQL? Explain with examples. (SQL)

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,'Sridhar'
INSERT emp SELECT 5,2,'Sourabh'

//Query which will return employees who have managers

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

//Query using a LEFT OUTER JOIN that 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


 

No comments: