Self Join with an example



There are 3 different types of joins available in sql server, and they are
1. Cross Join
2. Inner Join or Join
3. Outer Join

Outer Join is again divided into 3 types as shown below.
1. Left Outer Join or Left Join
2. Right Outer Join or Right Join
3. Full Outer Join or Full Join

I strongly recomend to learn about the basics and types of joins, before reading this article. Read the articles below, before proceeding with self join.
1. Basics of Joins
2. Inner Join
3. Left Outer Join
4. Right Outer Join
5. Full Outer Join


Self join is not a different type of join. Self join means joining a table with itself. We can have an inner self join or outer self join. Let us try to understand with an example.

To set up the data for the example, use the script below to create Employee Table and populate it with some sample data. We will be using Employee Table to understand Self Join.

CREATE TABLE EMPLOYEE
(
[EMPLOYEEID] INT PRIMARY KEY,
[NAME] NVARCHAR(50),
[MANAGERID] INT
)
GO

INSERT INTO EMPLOYEE VALUES(101,'Mary',102)
INSERT INTO EMPLOYEE VALUES(102,'Ravi',NULL)
INSERT INTO EMPLOYEE VALUES(103,'Raj',102)
INSERT INTO EMPLOYEE VALUES(104,'Pete',103)
INSERT INTO EMPLOYEE VALUES(105,'Prasad',103)
INSERT INTO EMPLOYEE VALUES(106,'Ben',103)
GO

We use Self Join, if we have a table that references itself. For example, In the Employee Table below MANAGERID column references EMPLOYEEID column. So the table is said to referencing itself. This is the right scenario where we can use Self Join. Now I want to write a query that will give me the list of all Employee Names and their respective Manager Names. In order to achieve this I can use Self Join. In the Table below,Raj is the manager for Pete,Prasad and Ben. Ravi is the manager for Raj and Mary. Ravi does not have a manager as he is the president of the Company.


The query below is an example of Self Join. Both E1 and E2 refer to the same Employee Table. In this query we are joining the Employee Table with itself.


SELECT E1.[NAME],E2.[NAME] AS [MANAGER NAME]
FROM EMPLOYEE E1
INNER JOIN EMPLOYEE E2
ON E2.EMPLOYEEID =E1.MANAGERID

If we run the above query we only get 5 rows out of the 6 rows as shown below.

Inner Self Join

This is because Ravi does not have a Manager. MANAGERID column for Ravi is NULL. If we want to get all the rows then we can use LEFT OUTER JOIN as shown below.


SELECT E1.[NAME],E2.[NAME] AS [MANAGER NAME]
FROM EMPLOYEE E1
LEFT OUTER JOIN EMPLOYEE E2
ON E2.EMPLOYEEID =E1.MANAGERID

If we execute the above query we get all the rows, including the row that has a null value in the MANAGERID column. The results are shown below. The MANAGERNAME for 2nd record is NULL as Ravi does not have a Manager.
Left Outer Self Join

Let us now slightly modify the above query using COALESCE as shown below. Read COALESCE function in SQL Server to understand COALESCE in a greater detail.


SELECT E1.[NAME],COALESCE(E2.[NAME],'No Manager'AS [MANAGER NAME]
FROM EMPLOYEE E1
LEFT JOIN EMPLOYEE E2
ON E2.EMPLOYEEID =E1.MANAGERID

If we execute the above query the output will be as shown in the image below. This is how COALESCE can be used.

Left Outer Self Join with COALESCE

7 comments:

  1. what is the scope of triggers in database..?

    ReplyDelete
  2. This is for question table

    EMPID EMPNAME MANAGER
    1001 JP -
    1002 SANDEEP 1001
    1003 SUMIT 1002
    1004 XYZ 1002
    1005 QST 1005
    1006 LMN 1004



    This is for result table
    OUTPUT
    WRITE A QUERY


    EMPID EMPNAME MANAGER
    1001 JP -
    1002 SANDEEP JP
    1003 SUMIT SANDEEP
    1004 XYZ SANDEEP
    1005 QST QST
    1006 LMN XYZ







    ReplyDelete
    Replies
    1. HI Jayprakash,

      Below is query for that output:

      select e2.EmpID,e2.EmpName,e1.EmpName as Manager
      from tblEmployee e1 right join tblEmployee e2
      on e1.EmpID=e2.Manager

      Delete
    2. select EMPID,EMPNAME,(select empname from empmanager a where a.EMPID=b.manager)mname
      from empmanager b

      Delete
  3. Hi Jayaprakash,

    try this u'll get above output

    select a.Employyeid,a.empname,COALESCE(aa.[empname],'NoMAnager')as ManagerName
    FRom
    (
    select a1.Employyeid,a1.empname,MAX(a2.Employyeid)as nextID
    from dbo.Employye a1
    left join dbo.Employye a2 on a1.Employyeid>a2.Employyeid
    group by a1.Employyeid,a1.empname)a
    left join dbo.Employye aa on a.nextID=aa.Employyeid
    order by a.Employyeid;

    ReplyDelete
  4. Hi Everyone,

    I got a questions in the interview please help me out in this regard would be great thanks for u all
    Ques1 Find top 5 emp name in each department with lowest attendance in the month of December from two different table one is emp and outer is attendence in emp table empid, name, deptName
    Attendance table have empid, Jan, Feb, March to Dec like
    2. Same question but lowest attendance in the month of all months of the year
    Please reply

    ReplyDelete
    Replies
    1. SELECT TOP5(name),deptName FROM emp GROUP BY deptName
      HAVING empid=(SELECT empid,COUNT(Dec) FROM Attendance GROUP BY empid ORDER BY desc);

      Delete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us