How to Use Self Joins (join a table to itself) to Combine Data from the Same Tables in MySQL

Using Self Joins to Combine Data from the Same Table

When you join a table to itself on columns with common values, you can picture how each record is related to one another. This is known as self-join.

Self-join is normally used to represent hierarchical relationship or tree structure in a table. In Northwind employees table, an employee has a manager who is also an employee. Every employee has a ReportsTo value which stores the EmployeeID of employee's manager.

In employees table, EmployeeID is primary key and ReportsTo is foreign key which relates back to EmployeeID in the same table. So we can use ReportsTo and EmployeeID to join the employees table to itself and find out the manager for each employee.

Practice #1: Use self-join for employees table.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query displays manager and staff relationship.
 
The query uses self-join where employees table 
joined with itself. 
 
Joined columns:
 
Foreign key column ReportsTo in the employees table 
which is aliased as staff table.
 
Primary key column EmployeeID in the employees table
which is aliased as manager table.
*/
select manager_tbl.FirstName as Manager,
       staff_tbl.FirstName as Staff       
from employees as staff_tbl
inner join employees as manager_tbl 
on staff_tbl.ReportsTo=manager_tbl.EmployeeID;

Query result set - 8 rows returned:
Using self join.

Practice #2: Use self-join for orders table.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query displays a list of employee IDs who serviced
the same customers. The where clause eliminates duplicate
rows where emp1 is equal to emp2.
 
Note that the first row and the second row are actually 
the same. This kind of duplication is removed in Practice #3.
*/
select distinct a.EmployeeID as emp1,
       a.CustomerID,
       b.EmployeeID as emp2
from orders as a
inner join orders as b
on a.CustomerID=b.CustomerID
where a.EmployeeID <> b.EmployeeID
order by a.CustomerID, a.EmployeeID;

Query result set - 2238 rows returned:
Using self join.

Practice #3: Use self-join for orders table.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query displays a list of employee IDs who serviced
the same customers. The where clause eliminates all kinds
of duplicated rows.
 
Note that this query fixed the problem in Practice #2 where
mirrored records existed for row 1 and row 2.
*/
select distinct a.EmployeeID as emp1,
       a.CustomerID,
       b.EmployeeID as emp2
from orders as a
inner join orders as b
on a.CustomerID=b.CustomerID
where a.EmployeeID > b.EmployeeID
order by a.CustomerID, a.EmployeeID;

Query result set - 1119 rows returned:
Using self join.

Happy Coding!

Other tutorials in this category

1. Using Inner Joins to Combine Data from Two Tables

2. Using Outer Joins to Combine Data from Two Tables

3. Another Example for Outer Joins (three tables)

4. SQL Set Operators - a Visual Guide to UNION, UNION ALL, MIMUS/EXCEPT, INTERSECT

5. Using UNION to Append Result Sets

6. Create working tables to demonstrate how to mimic set operators MINUS, EXCEPT, INTERSECT in MySQL

7. How to do MINUS/EXCEPT and INTERSECT in MySQL

8. How to Simulate Full Join in MySQL - Part 1: return both matched and unmatched rows

9. How to Simulate Full Join in MySQL - Part 2: return unmatched rows only

10. How to Simulate Full Join in MySQL - Part 3: use UNION to simulate FULL JOIN

11. Use Cross Join to Combine Data in MySQL

12. Differences Between Join and Union

Back to Tutorial Index Page

	Custom Search