SQL Self Join with example

The self join is a popular special case of the SQL JOIN. While most JOINs link two or more tables with each other to present their data together, a self join links a table to itself. This is usually done by joining a table to itself just once within a SQL query, but it is possible to do so multiple times within the same query.

When referring to the same table more than once in an SQL query, we need a way to distinguish each reference from the others. For this reason, it is important to use aliases to uniquely identify each reference of the same table in an SQL query. As a good practice, the aliases should indicate the role of the table for each specific reference in a query.

Let's see below few examples where we will see the usage of self join.

Example: Find the hierarchies

This type of table structure is very common in hierarchies. Now, to show the name of the manager for each employee in the same row, we can run the following query:

Table: Employee

Id	FullName	Salary	ManagerId
1	John Smith	10000	3
2	Jane Anderson	12000	3
3	Tom Lanon	15000	4
4	Anne Connor	20000
5	Jeremy York	9000	1

SQL Code:

SELECT

    employee.Id as EmpId,

        employee.FullName as EmpFullName,

        employee.ManagerId,

        manager.FullName as ManagerName

FROM Employees employee

JOIN Employees manager

ON employee.ManagerId = manager.Id

Output:

EmpId	EmpFullName	ManagerId	ManagerName
1	John Smith	3	Tom Lanon
2	Jane Anderson	3	Tom Lanon
3	Tom Lanon	4	Anne Connor
5	Jeremy York	1	John Smith

Example: Find the pair among the colleagues

Suppose we need to generate all possible pairs among the colleagues so that everyone has a chance to talk with everyone else at the company introductory evening.

Table: Colleagues 

Id	FullName	Age
1	Bart Thompson	43
2	Catherine Anderson	44
3	John Burkin	35
4	Nicole McGregor	29

SQL Code:

SELECT

    teammate1.FullName as Teammate1FullName,

    teammate1.Age as Teammate1Age,

        teammate2.FullName as Teammate2FullName,

    teammate2.Age as Teammate2Age

FROM Colleagues teammate1

JOIN Colleagues teammate2

ON teammate1.FullName <> teammate2.FullName

Output:

Teammate1FullName	Teammate1Age	Teammate2FullName	Teammate2Age
Catherine Anderson	44	Bart Thompson	43
John Burkin	35	Bart Thompson	43
Nicole McGregor	29	Bart Thompson	43
Bart Thompson	43	Catherine Anderson	44
John Burkin	35	Catherine Anderson	44
Nicole McGregor	29	Catherine Anderson	44
Bart Thompson	43	John Burkin	35
Catherine Anderson	44	John Burkin	35
Nicole McGregor	29	John Burkin	35
Bart Thompson	43	Nicole McGregor	29
Catherine Anderson	44	Nicole McGregor	29
John Burkin	35	Nicole McGregor	29

Please visit related article below:

• INNER JOIN
• LEFT OUTER JOIN: 
• RIGHT OUTER JOIN: 
• FULL OUTER JOIN: 
• Self Join
• CROSS JOIN