SELF JOIN :-
- A self join is a joining table to itself.
- To perform self join the same table must be listed twice in different Alias.
- Self join is performed in table having self referential integrity.
Display employee names and manager names:
Select x.ename, y.ename manager From emp x,emp y Where x.mgr= y.empno
Display names of the employee working under manager blake
Select x.ename employee From emp x, emp y Where x.mgr=y.empno and y.ename=’BLAKE’
Display names of the employees whos earning morethan his manager.
Select x.ename employee From emp x ,emp y x.sal > y.sal
List of employee whose are joined before the manager.
Select x.ename employee From emp x,emp y Where x.mgr=y.empno And x.hiredate<y.hiredate
Write a query to produce the following output
Ename Dname Grade Mgrname
Select x.ename,d.dname,g.grade,y.ename mname
From emp x ,emp y ,dept d,salgrade g
Where x.deptno= d.deptno
And
x.sal between g.lowsal and g.highsal
and
x.mgr=y.empno
{Note : here,we are used to equi, non equi & self}
ANSI STYLE OF SELF JOIN :-
Select x.ename,y.ename mname From emp x inner join emp y Or(x.mgr=y.empno)