What is a self join? 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.
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