Thursday, May 20, 2021

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

1John Smith100003
2Jane Anderson120003
3Tom Lanon150004
4Anne Connor20000
5Jeremy York90001

SQL Code:

    employee.Id as EmpId,
        employee.FullName as EmpFullName,
        manager.FullName as ManagerName
FROM Employees employee
JOIN Employees manager
ON employee.ManagerId = manager.Id


1John Smith3Tom Lanon
2Jane Anderson3Tom Lanon
3Tom Lanon4Anne Connor
5Jeremy York1John 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 

1Bart Thompson43
2Catherine Anderson44
3John Burkin35
4Nicole McGregor29

SQL Code:

    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


Catherine Anderson44Bart Thompson43
John Burkin35Bart Thompson43
Nicole McGregor29Bart Thompson43
Bart Thompson43Catherine Anderson44
John Burkin35Catherine Anderson44
Nicole McGregor29Catherine Anderson44
Bart Thompson43John Burkin35
Catherine Anderson44John Burkin35
Nicole McGregor29John Burkin35
Bart Thompson43Nicole McGregor29
Catherine Anderson44Nicole McGregor29
John Burkin35Nicole McGregor29
Please visit related article below:

No comments:

Post a Comment