SQL Server Self Join
In this article, I am going to discuss the SQL Server Self Join with one example. Please read our previous article where we discussed Full Outer Join in SQL Server with some real-time examples. At the end of this article you will understand what are Self Join in SQL Server and when and how use SQL Server Self Join.
What is Self Join in SQL Server?
The SQL Server Self Join is nothing a concept where we need to join a table by itself. You need to use SQL Server Self Join when you have some relations between the columns of the same table. If this is not clear at the moment then don’t worry we will discuss this with an example. I am sure at the end of this article, you definitely have a very good idea when to and how to use SQL Server Self Join.
Note: The point that you need to remember that when you are implementing the self-join mechanism then you need to create the alias for the table. You can also create any number of aliases for a single table in SQL Server.
The Self Join is not a different type of join. It can be classified as any type of join, such as
- Inner Join
- Outer (Left, right, full) join
- Cross Join
The Self Join is like any other join except that the two instances of the same table will be joined in the query.
Let’s understand SQL Server Self Join with an example.
We are going to use the following Employee table to understand the SQL Server Self Join concept.
Please use the following SQL script to create and populate the Employee Table with the required test data.
-- Create the Employee table CREATE TABLE Employee ( EmployeeID INT Primary Key, Name VARCHAR(20), ManagerID INT ) GO -- Insert the following records INSERT INTO Employee VALUES(1, 'Pranaya', 3) INSERT INTO Employee VALUES(2, 'Priyanka', 1) INSERT INTO Employee VALUES(3, 'Preety', NULL) INSERT INTO Employee VALUES(4, 'Anurag', 1) INSERT INTO Employee VALUES(5, 'Sambit', 1) GO
The above Employee table contains the rows for both normal employees as well as managers of that employee. So to find out the managers of all the employees, we need a SQL Server Self Join. So here we need to write a query which should give the following result.
SQL Server Self Join Query:
A MANAGER is also an EMPLOYEE. Both the EMPLOYEE and MANAGER rows are present in the same Employee table. Here we are joining the table Employee with itself using different alias names, E for Employee and M for Manager. Here we are going to use the Left Outer Join to get the records with ManagerId NULL. You can see in the output that Preety’s record is also retrieved, but the MANAGER is NULL. If you replace LEFT JOIN with INNER JOIN, you will not get Preety’s record.
SELECT E.Name as Employee, M.Name as Manager FROM Employee E LEFT JOIN Employee M ON E.ManagerId = M.EmployeeId
Inner Self Join Employee table:
SELECT E.Name as Employee, M.Name as Manager FROM Employee E INNER JOIN Employee M ON E.ManagerId = M.EmployeeId
When we execute the above query it produces the output as shown below.
Cross Self Join Employee table:
SELECT E.Name as Employee, M.Name as Manager FROM Employee E CROSS JOIN Employee M
In the next article, I am going to discuss SQL Server Indexes with examples. Here, in this article, I try to explain the SQL Server Self Join with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.