Back to the list of all SQL Server 2000 / 2005 Interview Questions
|
|
What is Self Join and when do you use Self Join?
|
|
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.
|
Employee Table
 |
|
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 in Results1
below. |
Results1 |
|
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. For a list of all different types of JOINS in SQL Server, please click here to read What are different types of JOINS in SQL Server.
|
|
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. |
 |
|
Let us now slightly modify the above query using COALESCE as shown below.
|
|
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.Click here to learn about COALESCE.
|
 |
Back to the list of all SQL Server 2000 / 2005 Interview Questions
|