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,
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;






Join the conversation! Your thoughts help the community grow.