What is Self Join in SQL And When Would You Use it?

Summary: In this tutorial, you will learn how to use the SQL Server self join in SQL to join a table to itself, and also you will learn when would you use it with example.

Overview: Self Join in SQL

A SQL Self Join is a join in which a table is joined with itself, which is also known as Unary relationships, Therefore when the table has a FOREIGN KEY which references its own PRIMARY KEY. furthermore, To join a table itself means that each row of the table is combined with itself and with every other row of the table.

Therefore, self join SQL allows you to join a table to itself, it is very useful for querying comparing rows within the same table or hierarchical data. A self joins in SQL uses the left join or inner join clause. Because the query that uses a self join references the same table. Furthermore, the table alias is used to assign different names to the same table within the query.

Note:

The self-join in SQL can be viewed as a join of two copies of the same table. therefore, The table is not actually copied, but SQL performs the command as though it were.

The General syntax of the command for joining a table to itself is almost the same as that for joining the two different tables. furthermore, to distinguish the column names from one another, aliases for the actual table name are used since both the tables have the same name. The table name aliases are defined in the FROM clause of the SELECT SQL statement.

The following syntax is given below :

OR
There are the given following below syntax of joining the table T to itself:
Therefore, the above query references the table T twice and the table aliases t1 and t2 are used to assign the T table different names in the query.

SQL Self Join Example

Furthermore, in this tutorial we have used a table TEACHER, that has one-to-many relationship.
Let’s see the following SQL Code to create the table TEACHER

Sample Table: Teacher

There are the given below demo records in table Teacher

TeacherID	Teacher_Name	City	Join_Date	Teacher_Dept
1001	Aarav Adriano	London	2012-02-05	NULL
1002	Armando Aurelio	France	2012-02-15	1001
1003	Gaston Guillaume	Greece	2012-03-02	1002
1004	Adeline Anais	Strasbourg	2012-03-12	1001
1005	Christine Claire	Buenos Aires	2012-03-28	1002
1006	Germaine Henriette	Guyana	2012-04-05	1001

The Structure Of The Table

Therefore, in the Teacher table displayed above, TeacherID is the primary key. Teacher_Dept is the foreign key as you can see in the table structure or SQL code. Furthermore, If we want a list of teachers and the names of their department, then we’ll have to JOIN the TEACHER table to itself to get this list.
Let’s understand the relationship the following data into the table TEACHER as per given sample table data.

Unary Relationship To Teacher

Self Join Example in SQL

Furthermore, let’s us see the following example, we will use the table TEACHER twice and in order to do this we will use the alias of the table. Therefore, to get the list of Teacher and their Teacher name the following SQL statement has used:

SQL Statement:

Output: