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.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. 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.

/*
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.

/*
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.

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. Using UNION to Append Result Sets

Back to Tutorial Index Page

	Custom Search