Learn about Self Join In SQL Server

Introduction

In this article, I am going to explain self-join and types of self-joins with examples. Here we will be using SQL Server 2017 or you can use SQL Server 2008 or above.

Read my previous article about Joins in SQL Server 2017 using the below links before reading this article,

• Joins In SQL Server 2017

Definition

Joining a table with itself is called a self-join. It is not a different type of join. It can be classified under any type of join like INNER Join, OUTER Join, and CROSS Join.

Use

It is used for querying hierarchical data or comparing rows within the same table.

Types of self Joins

Self-join can be classified as

• Inner self Join
• Outer self Join
• Cross self Join

Again, Outer Self Joins are divided into three types.

• Left Self Join or Left Outer Self Join
• Right Self Join or Right Outer Self Join
• Full Self Join or Full Outer self Join

Now, I am going to explain the different types of self-joins with examples and the differences between them.

Prerequisites

SQL Server 2017 or you can use SQL Server 2008 or above version.

Now, first, we will create a Database and one table to apply the self-joins for understanding.

Creating a Database and One Table

Step 1. Create a Database

Open your SQL Server and use the following script to create the “chittadb” Database.

CREATE DATABASE chittadb

Now, select the script query then press F5 or click on Execute button to execute the above script.

You should see a message, “Command(s) completed successfully.” This means your new database is created.

Step 2. Create a table

Open your SQL Server and use the following script to create a table “tblEmployee”.

CREATE TABLE tblEmployee
(
    EmpID int PRIMARY KEY,
    Name nvarchar(50),
    ManagerId int
)

Execute the above query to create “tblEmployee “.

You should see a message, “Command(s) completed successfully.”

Now, data is inserted into the table.

INSERT INTO tblEmployee VALUES (1, 'Chitta', 3);
INSERT INTO tblEmployee VALUES (2, 'Chandin', 4);
INSERT INTO tblEmployee VALUES (3, 'Nabin', NULL);
INSERT INTO tblEmployee VALUES (4, 'Mitu', 1);
INSERT INTO tblEmployee VALUES (5, 'Jitu', 1);

Execute the above query, you should see a message, “Command(s) completed successfully.”

Now retrieve all data from the “tblEmployee” table.

select * from tblEmployee

output

General Formula for Self Joins

SELECT ColumnList
FROM Table T1
JOIN JOIN_TYPE Table T2 ON JoinCondition;

T1 and T2 are different table alias names for the same table.

Inner Self Join

Inner join returns only the matching rows between both the tables, non-matching rows are eliminated.

Example

Write a query, to retrieve Employee Name and Manager Name from tblEmployee.

Inner Self Join tblEmployee table Query

SELECT E.Name AS EmployeeName, M.Name AS ManagerName
FROM tblEmployee E
INNER JOIN tblEmployee M ON E.ManagerId = M.EmpID;

Output

LEFT SELF JOIN or LEFT OUTER SELF JOIN

Left Join or Left Outer Join returns only the matching rows between both tables, plus non-matching rows from the left table.

Example

Write a query, to retrieve Employee Name and Manager Name from tblEmployee.

LEFT Self JOIN or LEFT OUTER Self JOIN Query

SELECT E.Name AS EmployeeName, M.Name AS ManagerName
FROM tblEmployee E
LEFT JOIN tblEmployee M ON E.ManagerId = M.EmpID;

Output

RIGHT SELF JOIN or RIGHT OUTER SELF JOIN

Right Join or Right Outer Join returns only the matching rows between both tables, plus non-matching rows from the right table.

Example

Write a query, to retrieve Employee Name and Manager Name from tblEmployee.

RIGHT SELF JOIN or RIGHT OUTER SELF JOIN Query

SELECT E.Name AS EmployeeName, M.Name AS ManagerName
FROM tblEmployee E
RIGHT JOIN tblEmployee M ON E.ManagerId = M.EmpID;

Output

FULL SELF JOIN or FULL OUTER SELF JOIN

Full Join or Full Outer Join returns all rows from both tables (left & right tables), including non-matching rows from both tables.

Example

Write a query, to retrieve Employee Name and Manager Name from tblEmployee.

FULL SELF JOIN or FULL OUTER SELF JOIN Query

SELECT E.Name AS EmployeeName, M.Name AS ManagerName
FROM tblEmployee E
FULL JOIN tblEmployee M ON E.ManagerId = M.EmpID;

Output

CROSS SELF JOIN

CROSS JOIN, produces the Cartesian product of the 2 tables.

For example, in the tblEmployee table, we have 5 rows; also in the tblEmployee, we have 5 rows. So, a cross joins between these 2 tables produces 25 rows. Cross Join shouldn't have an ON clause.

Example

Write a query, to retrieve Employee Name and Manager Name from tblEmployee.

Note. Cross Join shouldn't have an ON clause.

CROSS Self JOIN Query

SELECT E.Name AS EmployeeName, M.Name AS ManagerName
FROM tblEmployee E
CROSS JOIN tblEmployee M;

Output

Conclusion

In this article, I explained self joins in SQL Server with examples. I hope this article has helped you to understand this topic. Post your valuable feedback in the comments section.