SQL Server Self Join

SQL Server Self Join

In this article, I am going to discuss the SQL Server Self Join with one example. 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

  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 SQL Server Self Join with an example.

We are going to use the following Employee table to understand the SQL Server Self Join concept.

SQL Server Self Join with an example

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
Example:

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. 

SELF JOIN in SQL Server

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.

SELF JOIN in SQL Server

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 will discuss SQL Server Indexes with examples.

SUMMARY

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.

Leave a Reply

Your email address will not be published. Required fields are marked *