Self Join in SQL Server – SQL Server Tutorial

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)

Leave a Reply

Your email address will not be published. Required fields are marked *