How can you join a table to itself?

Answer»

Another type of join in SQL is a SELF JOIN, which connects a table to itself. In order to perform a self-join, it is necessary to have at least one column (say X) that serves as the primary key as well as one column (say Y) that contains values that can be matched with those in X. The value of Column Y may be null in some rows, and Column X need not have the exact same value as Column Y for every row.

Example: Consider the table Employees.

Emp_ID	Emp_Name	Emp_Profile	Emp_Country	ManagerId
101	Ashish Kaktan	Content Writer	Germany	104
104	Raj Choudhary	Data Analyst	India	108
105	Vivek Oberoi	Software Engineer	India	101
108	Shantanu Khandelwal	Development Executive	Europe	101
109	Khanak Desai	Marketing Manager	Mexico

For instance, we might wish to display results that only include employees with their managers. By using table aliases and a self-join, this can be accomplished easily.

SELECT e.Emp_ID, e.Emp_Name, m.FullName as ManagerName
FROM Employees e JOIN Employees m ON e.ManagerId = m.Emp_ID

Output:

Emp_ID	Emp_Name	ManagerName
101	Ashish Kaktan	Raj Choudhary
104	Raj Choudhary	Shantanu Khandelwal
105	Vivek Oberoi	Ashish Kaktan
108	Shantanu Khandelwal	Ashish Kaktan
109	Khanak Desai	Null

How can you join a table to itself?

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment