Summary: in this tutorial, you will learn how to use MySQL self join that joins a table to itself using join statement.
In the previous tutorial, you have learned how to join a table to the other tables using INNER JOIN, LEFT JOIN or
RIGHT JOIN statement. However, there is a special case that you join a table to itself, which is known as self join.
You use self join when you want to combine rows with other rows in the same table. To perform the self join operation, you must use a table alias to help MySQL distinguish the left table from the right table of the same table.
MySQL self join examples
Let’s take a look at the
employees table in the sample database.
employees table, we store not only employees data but also organization structure data. The
reportsto column is used to determine the manager ID of an employee.
In order to get the whole organization structure, we can join the
employees table to itself using the
reportsTo columns. The
employees table has two roles: one is Manager and the other is Direct Report.
SELECT CONCAT(m.lastname,', ',m.firstname) AS 'Manager',
CONCAT(e.lastname,', ',e.firstname) AS 'Direct report'
FROM employees e
INNER JOIN employees m ON m.employeeNumber = e.reportsto
ORDER BY manager;
In the above example, we only see employees who have a manager. However, we don’t see the top manager because his name is filtered out due to the
INNER JOIN clause. The top manager is the employee who does not have any manager or his manager no is
Let’s change the
INNER JOIN clause to the
LEFT JOIN clause in the query above to include the top manager. We also need to use the IFNULL function to display the top manager if the manger’s name is
IFNULL(CONCAT(m.lastname, ', ', m.firstname),
'Top Manager') AS 'Manager',
CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report'
employees m ON m.employeeNumber = e.reportsto
ORDER BY manager DESC
By using MySQL self join, we can display a list of customers who locate in the same city by joining the
customers table to itself.
INNER JOIN customers c2 ON c1.city = c2.city
AND c1.customername > c2.customerName
We joined the
customers table to itself with the following join conditions:
c1.city = c2.cityto make sure that both customers have the same city
c.customerName > c2.customerNameto ensure that we don’t get the same customer.
In this tutorial, we have introduced you to MySQL self join that allows you to join a table to itself by using
INNER JOIN or
LEFT JOIN clauses.