A self-join is a join between multiple instances of the same table. With self-joins it is mandatory to alias the instances of the tables differently.

Whenever you have a parent-child relationship within a table you have an opportunity to use a self-join. For example, suppose you had a table of employees. In that table there is a column that contains that employee’s manager id.

Let’s have a look at a few columns of the Employees table used in Itzik Ben-Gan’s book in his book T-SQL Querying published by Microsoft Press in 2015.

Here is our query.

Here are the results.

In a non-production server, you could use the code below to create the original table at the top of this post. I changed some of the names, however.