SQL Server Self Join

Back to: SQL Server Tutorial For Beginners and Professionals

Self Join in SQL Server with Example

In this article, I am going to discuss the SQL Server Self Join with Examples. Please read our previous article where we discussed Full Outer Join in SQL Server with Examples. At the end of this article, you will understand what is Self Join and when and how to use Self Join.

What is Self Join in SQL Server?

The Self Join is nothing a concept where we need to join a table by itself. You need to use 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 of when to and how to use SQL Server Self Join.

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

1. Inner Join
2. Outer (Left, Right, Full) join
3. 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 Self Join with Examples

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

Self Join Examples in SQL Server:

The above Employee table contains the rows for both normal employees as well as managers of that employee. So in order to find out the managers of each employee, we need a SQL Server Self Join.  So here we need to write a query that should give the following result. 

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 Cross Join in SQL Server 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.