SQL Self Join

In a SQL self join the table is joined to itself.  You can think of it being like an inner join. The idea is to match rows using a join condition.

Here is the general syntax for a SQL self join:

SELECT a.column1, b.column2, …
FROM table1 a
INNER JOIN table1 b ON a.matchColumn = b.matchColumn

This is very similar to the way you set up your inner joins, but here it is very important to alias your tables.  If you didn’t, you couldn’t distinguish the two tables!

SQL Self Join Example

Let’s walk through an example.  Suppose the HR department is looking to foster greater cooperation between company department.  As a first step the director is hosting a series of intra department get togethers.

She would like you to write a query that shows all the department combinations within the company.

To do this you set up using the Department table.

Here is the join you write:

select d1.Name, d2.Name
from HumanResources.Department d1
     inner join HumanResources.Department d2 on d1.DepartmentID = d2.DepartmentID

It works, but one problem, it show Matching departments!

We want to see departments that don’t match.  An easy fix to a common SQL self join mistake (we’re all so used to matching on joins).  This is what we do:

select d1.Name, d2.Name
from HumanResources.Department d1
    inner join HumanResources.Department d2 on d1.DepartmentID <> d2.DepartmentID
order by d1.Name

Now we have  a good list.

You may have noticed a self join is also similar to a cross join. This is true! You’ll cross join the data once you remove the join condition.

If you’re interested learning about more joins, then check out SQL Joins – The Ultimate Guide.