November 8, 2021

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.

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>