Back to articles list October 13, 2020 - 11 minutes read What Is a Self Join in SQL? An Explanation With Seven Examples Martyna Sławińska Martyna is a software developer with a passion for programming, automation, and innovation. Currently working for the global corporate IT consulting sector, she is a new-generation techie who hails from Warsaw University of Technology, Poland. She has certifications in both database administration and Java development. Her interests include creating small automation devices using embedded systems and various electronic components. In her free time, she practices yoga, which helps her to center her thoughts and come up with new ideas. Tags: sql learn sql joins 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. The latter 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. Also, different scenarios that use the self join are presented with examples. 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: IdFullNameSalaryManagerId 1John Smith100003 2Jane Anderson120003 3Tom Lanon150004 4Anne Connor20000 5Jeremy York90001 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: IdFullNameManagerIdManagerName 1John Smith3Tom Lanon 2Jane Anderson3Tom Lanon 3Tom Lanon4Anne Connor 5Jeremy York1John Smith 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: IdFullNameSalaryManagerId 1John Smith100003 2Jane Anderson120003 3Tom Lanon150004 4Anne Connor20000John Smith 5Jeremy York90001 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: IdFullNameManagerIdManagerName 1John Smith3Tom Lanon 2Jane Anderson3Tom Lanon 3Tom Lanon4Anne Connor 5Jeremy York1John Smith 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: IdFullNameManagerIdManagerName 1John Smith3Tom Lanon 2Jane Anderson3Tom Lanon 3Tom Lanon4Anne Connor 4Anne Connor 5Jeremy York1John Smith 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: IdNameAgeParentId 1Jonathan53 2Alexandra73 3Barbara30 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: ChildIdChildFirstNameChildAgeParentIdParentFirstNameParentAge 1Jonathan53Barbara30 2Alexandra73Barbara30 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: IdQuantityCategoryParentCategoryId 160Food 250Fruit1 340Apple2 420Granny Smith3 5100Milk1 660Soy Milk5 740Cow Milk5 830Whole Milk7 910Fat-Free Milk7 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: IdQuantityCategoryParentCategoryIdParentCategory 250Fruit1Food 340Apple2Fruit 420Granny Smith3Apple 5100Milk1Food 660Soy Milk5Milk 740Cow Milk5Milk 830Whole Milk7Cow Milk 910Fat-Free Milk7Cow Milk 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: IdFullNameAge 1Bart Thompson43 2Catherine Anderson44 3John Burkin35 4Nicole McGregor29 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: Teammate1FullNameTeammate1AgeTeammate2FullNameTeammate2Age Catherine Anderson44Bart Thompson43 John Burkin35Bart Thompson43 Nicole McGregor29Bart Thompson43 Bart Thompson43Catherine Anderson44 John Burkin35Catherine Anderson44 Nicole McGregor29Catherine Anderson44 Bart Thompson43John Burkin35 Catherine Anderson44John Burkin35 Nicole McGregor29John Burkin35 Bart Thompson43Nicole McGregor29 Catherine Anderson44Nicole McGregor29 John Burkin35Nicole McGregor29 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. IdFirstNameAgeParentId 1Jonathan53 2Alexandra73 3Barbara304 4Tom506 5George556 6Amy807 7Josephine9935 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: IdFirstNameAgeParentIdAncestorIdAncestorFirstNameAncestorAge 1Jonathan533Barbara30 1Jonathan534Tom50 1Jonathan535George55 1Jonathan536Amy80 1Jonathan537Josephine99 2Alexandra733Barbara30 2Alexandra734Tom50 2Alexandra735George55 2Alexandra736Amy80 2Alexandra737Josephine99 3Barbara3044Tom50 3Barbara3045George55 3Barbara3046Amy80 3Barbara3047Josephine99 4Tom5066Amy80 4Tom5067Josephine99 5George5566Amy80 5George5567Josephine99 6Amy8077Josephine99 7Josephine99 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: AirportIdCountryCity 1USANew York 2CanadaToronto 3GermanyFrankfurt 4FranceParis 5ItalyRome Table Flight: FlightIdAirplaneIdStartTimestampEndTimestampStartAirportIdEndAirportId 25558772020-01-14 13:00:002020-01-14 15:00:0034 32225362020-02-04 01:00:002020-02-04 16:00:0015 41117452020-02-15 09:00:002020-02-15 12:00:0054 57775242020-02-24 03:00:002020-02-24 19:00:0042 68885212020-03-25 10:00:002020-03-25 12:00:0021 74449372020-04-01 00:00:002020-04-01 17:00:0031 2431116542020-01-01 02:00:002020-01-01 04:00:0012 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: FlightIdAirplaneIdStartAirportIdStartAirportCountryStartAirportCityEndAirportIdEndAirportCountryEndAirportCity 11116541USANew York2CanadaToronto 25558773GermanyFrankfurt4FranceParis 32225361USANew York5ItalyRome 41117455ItalyRome4FranceParis 57775244FranceParis2CanadaToronto 68885212CanadaToronto1USANew York 74449373GermanyFrankfurt1USANew York 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! Tags: sql learn sql joins You may also like How to Join the Same Table Twice Learn when and why you need to join the same table twice in SQL, including when you should use self joins. Read more How to Learn SQL JOINs Learn how to use SQL JOINs to effectively combine data across multiple tables and analyze sophisticated data sets. Read more SQL JOIN Cheat Sheet This 2-page SQL JOIN Cheat Sheet covers the syntax of different JOINs (even the rare ones!) Download it in PDF or PNG format. Read more How to Join 3 Tables (or More) in SQL Have you ever wondered how to join three tables in SQL? It's easy when you know the basics. Joining three tables can be as easy as joining two tables. Read more What's the Difference Between Having Multiple Tables in FROM and Using JOIN? What’s your approach to SQL JOINs? Do you use JOIN or simply list tables in FROM? Learn why SQL practitioners prefer the new syntax with the JOIN keyword. Read more Can you Join two Tables Without a Common Column? Do you need to combine two tables without a common column? Learn how the CROSS JOIN and the UNION operators can help you with this task. Read more Practical Examples of When to Use Non-Equi JOINs in SQL Do you only use equals in your JOIN conditions? Learn how non-equi JOINs can assist with listing duplicates and joining tables based on a range of values. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.