A very popular question I often see in the interviews of developers. It does not matter if you are a SQL developer, .NET developer, JAVA developer or Python Developer, this question is always popular in any interviews. The question is about join in the SQL and it is about SELF JOIN.
Question: What is a Self Join?
Answer: A self-join is simply a normal SQL join that joins one table to itself. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.
Question: Is Self Join Inner Join or Outer Join?
Answer: A self-join can be an inner join or an outer join or even a cross join. A table is joined to itself based upon a column that have duplicate data in different rows.
Question: What is a practical use of the Self Join in the real world?
Answer: The best example of self join in the real world is when we have a table with Employee data and each row contains information about employee and his/her manager. You can use self join in this scenario and retrieve relevant information. Let us see an example, over here.
Let us first create the same table for an employee.
One of the columns in the same table contains the ID of the manger, who is also an employee for the same company. Now all the employees and their managers are present in the same table. Let us see how Self Join works in the real world scenario now.
USE TempDb GO -- Create a Table CREATE TABLE Employee( EmployeeID INT PRIMARY KEY, Name NVARCHAR(50), ManagerID INT ) GO -- Insert Sample Data INSERT INTO Employee SELECT 1, 'Mike', 3 UNION ALL SELECT 2, 'David', 3 UNION ALL SELECT 3, 'Roger', NULL UNION ALL SELECT 4, 'Marry',2 UNION ALL SELECT 5, 'Joseph',2 UNION ALL SELECT 7, 'Ben',2 GO -- Check the data SELECT * FROM Employee GO
Let us now connect the Employee table with itself with the help of INNER JOIN.
-- Inner Join SELECT e1.Name EmployeeName, e2.name AS ManagerName FROM Employee e1 INNER JOIN Employee e2 ON e1.ManagerID = e2.EmployeeID GO
In the result set, we can see that all the employees who have a manager are visible. Though the above solution has one limitation. The limitation is that we are not able to find out the top manager of the company in our result set. Inner join does not display any result which does not have a manager id in our scenario.
Next let us convert Inner Join to Outer Join and then see the result set.
-- Outer Join SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName FROM Employee e1 LEFT JOIN Employee e2 ON e1.ManagerID = e2.EmployeeID GO
Now we have converted Inner Join to Outer Join for the same table and we can see Top Manager in resultset.
I hope it is clear from the example that SELF JOIN can be INNER JOIN or OUTER JOIN.
Reference: Pinal Dave (https://blog.sqlauthority.com)
We use self join in many cases. Its a very powerful statement. Thanks
how can we get manager’s manager name
The above query i.e SELECT e1.Name EmployeeName, ISNULL(e2.name, ‘Top Manager’) AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID will give you the required output.
THE QUERY IS GOOD to understand for beginners
Hi, with out using join can we achieve the above result..
It’s really helpful, thanks !!
I think there should be IFNULL instead of ISNULL.