What Is a Self Join in SQL? Explaned With 7 Examples

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.

Typically, each table in a database stores a specific type of information. So, often there are hundreds of tables related to each other in a database. This implies the need for joins. You can join different tables by their common columns using the JOIN keyword. It is also possible to join a table to itself, which is known as a self join. In this article, we will discuss what a self join is, how it works, and when you need it in your SQL queries.

To practice SQL JOIN, including self joins, I recommend our interactive SQL JOINs course. It contains over 90 exercises on differnt types of JOIN, including sections dedicated just to self joins.

What Is a Self Join in SQL?

The self join, as its name implies, joins a table to itself. To use a self join, the table must contain a column (call it X) that acts as the primary key and a different column (call it Y) that stores values that can be matched up with the values in Column X. The values of Columns X and Y do not have to be the same for any given row, and the value in Column Y may even be null.

Let’s take a look at an example. Consider the table Employees:

Each employee has his/her own Id, which is our “Column X.” For a given employee (i.e., row), the column ManagerId contains the Id of his or her manager; this is our “Column Y.” If we trace the employee-manager pairs in this table using these columns:

• The manager of the employee John Smith is the employee with Id 3, i.e., Tom Lanon.
• The manager of the employee Jane Anderson is the employee with Id 3, i.e., Tom Lanon.
• The manager of the employee Tom Lanon is the employee with Id 4, i.e., Anne Connor.
• The employee Anne Connor does not have a manager; her ManagerId is null.
• The manager of the employee Jeremy York is the employee with Id 1, i.e., John Smith.

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:

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

which returns the following result:

The query selects the columns Id, FullName, and ManagerId from the table aliased employee. It also selects the FullName column of the table aliased manager and designates this column as ManagerName. As a result, every employee who has a manager is output along with his/her manager’s ID and name.

In this query, the Employees table is joined with itself and has two different roles:

• Role 1: It stores the employee data (alias employee).
• Role 2: It stores the manager data (alias manager).

By doing so, we are essentially considering the two copies of the Employees table as if they are two distinct tables, one for the employees and another for the managers.

You can find more about the concept of the self join in our article An Illustrated Guide to the SQL Self Join.

Table Aliases in Self Join

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.

The aliases are in red in the following query. You can see their declaration in the FROM and JOIN clauses.

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

The JOIN keyword connects two tables and is usually followed by an ON or USING clause that specifies the common columns used for linking the two tables. Here, we see that the two references to the Employees table are linked by conditioning on the employee’s ManagerId to match the employee ID of the manager.

Examples

Let’s go through some common scenarios that use the self join.

Scenario 1: Processing a Hierarchy in SQL

The self join is commonly used in processing a hierarchy. As we saw earlier, a hierarchy assigns a row in a table to another row within the same table. You might think of it as having parent and child rows.

Let’s go back to the example with the employees and their managers. Here’s the Employees table again:

And the code to list every employee that has a manager with the name of his or her manager:

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

Here’s the result when you run the code:

This query uses the standard join, also known as the INNER JOIN. To read more about the INNER JOIN, please see our article An Illustrated Guide to the SQL INNER JOIN.

If we want to list all the employees whether or not they have managers, we can use a LEFT OUTER JOIN instead. The query below does this:

SELECT
	employee.Id,
    	employee.FullName,
    	employee.ManagerId,
    	manager.FullName as ManagerName
FROM Employees employee
LEFT OUTER JOIN Employees manager
ON employee.ManagerId = manager.Id

When you run this query, you get the following result:

The difference between JOIN and LEFT OUTER JOIN becomes clear when we compare this with the result of the previous SQL query. In the result of the (inner) JOIN query, only the employees with managers are included. In contrast, the LEFT OUTER JOIN query returns all employees, with or without managers. To read more about the OUTER JOIN, please read our article An Illustrated Guide to the SQL OUTER JOIN.

Another example of a hierarchy is the relationship between parents and their children. Consider the Human table shown here:

In the query below, the children are assigned to their respective parents by joining the Human table to itself:

SELECT
	child.Id as ChildId,
    	child.FirstName as ChildFirstName,
    	child.Age as ChildAge,
    	child.ParentId,
    	parent.FirstName as ParentFirstName,
    	parent.age as ParentAge
FROM Human child
INNER JOIN Human parent
ON child.ParentId = parent.Id

Here’s the result of this query:

The result of the query includes only the children who have parents. As was the case in the example of the employee-manager hierarchy, we could use a LEFT OUTER JOIN to include all rows from the table aliased child.

Here is yet another example of a hierarchy. Consider the table Category, shown below:

Let’s assign a parent category to each category wherever it is possible. Here’s a query to do that:

SELECT
	category.Id,
	category.Quantity,
    	category.Category,
    	category.ParentCategoryId,
    	parentcategory.Category as ParentCategory
FROM Category category
JOIN Category parentcategory
ON category.ParentCategoryId = parentcategory.Id

And here is the result:

The first four columns of the result above come from the reference to the table aliased category. The last column comes from the table aliased parentcategory and contains the parent category name matched by the respective Id.

The Category table has two different roles as denoted by the two separate references. The column ParentCategoryId from the table aliased category is matched with Id from the table aliased parentcategory. The ON clause specifies that ParentCategoryId from category must equal Id from parentcategory to connect the corresponding rows.

Scenario 2: Listing Pairs Within a Table

You can use a self join to generate pairs of rows based on the condition in the ON clause. Let’s start with a simple example that generates all possible pairs among the colleagues. Consider the following table, 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. Here’s the SQL code:

SELECT
	teammate1.FullName as Teammate1FullName,
	teammate1.Age as Teammate1Age,
    	teammate2.FullName as Teammate2FullName,
	teammate2.Age as Teammate2Age
FROM Colleagues teammate1
CROSS JOIN Colleagues teammate2
ON teammate1.FullName <> teammate2.FullName

And here’s the result:

The result matches every person with every single person in the table. Since we don’t want anyone to be paired with himself or herself, we have the ON clause condition teammate1.FullName <> teammate2.FullName. This means that each person will be paired with three other colleagues, because there are four colleagues at this event.

Now, let’s look at a slightly more complicated example. Consider the Human table shown below. We want to match all the ancestors to each person wherever the data allows, where a person is an ancestor if he or she has a higher Id value.

Below is the Human table used in this example.

Let’s find all the descendant-ancestor pairs in the above table. Here’s the SQL code:

SELECT
	descendant.Id,
    	descendant.FirstName,
    	descendant.Age,
    	descendant.ParentId,
    	ancestor.Id as AncestorId,
    	ancestor.FirstName as AncestorFirstName,
    	ancestor.Age as AncestorAge
FROM Human descendant
LEFT JOIN Human ancestor
ON descendant.ParentId <= ancestor.Id

And the result:

By specifying the ON clause condition descendant.ParentId <= ancestor.Id, we find all the ancestors of every person in the table where they exist; otherwise, the query returns null for the ancestor information.

The first four columns are taken from the table aliased descendant which contains information of the person for whom the ancestors are searched. The last three columns are taken from the table aliased ancestor and contain details about each ancestor.

Scenario 3: Self Join in Combination With Another Table

In SQL, it is possible to have a self join in combination with one or more different tables. While not a clean self join, this is very common in practice.

A real-life example of this is the flight information in airports, with an enormous amount of data each hour. Suppose we want to search for a flight identification number along with the details about their departure and destination airports. Consider the following tables:

Table Airport:

Table Flight:

Here, note that the column AirportId of the Airport table is the foreign key to the columns StartAirportId and EndAirportId of the Flight table. We will join the Airport table to the Flight table two separate times as follows:

• In the first JOIN, Airport takes the role of the table with the starting airports.
• In the second JOIN, Airport takes the role of the table with the destination airports.

The query looks like this:

SELECT
	flight.FlightId,
    	flight.AirplaneId,
    	flight.StartAirportId,
    	startairport.Country as StartAirportCountry,
    	startairport.City as StartAirportCity,
    	flight.EndAirportId,
    	endairport.Country as EndAirportCountry,
    	endairport.City as EndAirportCity
FROM Flight flight
JOIN Airport startairport
ON flight.StartAirportId = startairport.AirportId
JOIN Airport endairport
ON flight.EndAirportId = endairport.AirportId

And the result of the query looks like this:

Let’s analyze the result. The first three columns come from a straightforward SELECT of the Flight table. The next two columns come from Airport in the role of the starting airport table; the rows are matched based on AirportId and StartAirportId from the Airport and Flight tables, respectively. This is followed by a column from the Flight table. The last two columns come from Airport in the role of the destination airport table this time; the rows are matched based on AirportId and EndAirportId from the Airport and Flight tables, respectively.

Still a bit confused about all the JOINs? There are many more articles for you to browse through for help. I especially recommend the article on How to Learn SQL JOINs. And if you need to start practicing, please visit our article on How to Practice SQL JOINs.

Self Join: A Special Case of the Join

As we have seen, the self join is an important special case of the join. We have seen examples of various applications of the self join, including processing a hierarchy in a table and pairing the rows within a table. We can join the same table multiple times, but it is important to give each reference an alias that indicates its role. These table aliases are used to fetch columns from this single table based on the role for which it is referenced.

Joins are a vital part of SQL and a very helpful, frequently used feature for combining different tables. They are everywhere – be sure to check out our course on SQL JOINs to master this powerful tool!