Self Join by Example in SQL SERVER

Self Join is a join where a table is joined to itself. That is, both the tables in the join operations are the same. In a self join each row of the table is joined with itself if they satisfy the join condition

Examples of Self Join

There are many instances, where you need to self join a table. Usually when the table has a parent-child relationship with itself. In a parent-child relationship, the table will have FOREIGN KEY which references its own PRIMARY KEY.

For Example

  1. Customer account in the bank with an introducer. The introducer must be a customer of the bank.
  2. An employee with a supervisor or manager.
  3. Department under another Department

Self Join Syntax

There is no special syntax for Self-join. It is just a normal join where first_table and second_table refers to the same table.

  • There is no special self Join Syntax. There is no SELF JOIN keyword
  • The Alias for the table is a must as both tables are the same. Otherwise, it will result in an error
  • You can use the same table at multiple levels

For Example,

The table is joined with itself thrice

Self Join Example

Consider the following table Employees. It has a ManagerID column, which refers to its own EmployeeID. ManagerID Represents the EmployeeID of the Manager. Note that ManagerID int null as the topmost employee will not have any manager above him

Sample Table for Self Join

To get the Employees List, with the manager’s name we need to join the Employees table with itself as shown below.

Employees table self joined with itself to get the name of the manager

More Examples

Now, let us consider another sample database, where the employee’s table has the DeptID column. The Manager of the department is stored in the third table DeptManager.

Sample database

To get the employee list with dept name with the manager, we need to join

  1. Join the Dept table with the employee table to get the name of the dept. (based on DeptID)
  2. Get the ManagerID from DeptManager table joining it to the above result (based on DeptID)
  3. To get the Manager’s name Join the Employees table again on the ManagerID & EmployeeID
Self join query result

Now, if you notice the Employee Kevin is Manager of Executive Dept. Hence shown as Manager to himself, which is wrong. You can correct by ensuring that employeeID of the manager & employee cannot be the same (employee.employeeID <> Manager.EmployeeID) as shown in the query below.

Self join query result

You can extend the above query, one step further by asking for Managers Department and his manager. That requires us to join the employee & dept table again as shown below

Self join query result

References

  1. Join Syntax (SQL Server)
  2. Sample database

Summary

In this article, you learned what is Self Join is and how to use it SQL Server with several examples

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top