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 can join a table to itself, which is known as self join.
You use self join when you want to combine records with other records 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.
In the 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 employeeNumberand 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 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 manager or his manager no. is NULL.
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 NULL.
SELECT IFNULL(CONCAT(m.lastname,', ',m.firstname),'Top Manager') AS 'Manager', CONCAT(e.lastname,', ',e.firstname) AS 'Direct report' FROM employees e LEFT JOIN 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 customerstable to itself.
SELECT c1.city, c1.customerName, c2.customerName FROM customers c1 INNER JOIN customers c2 ON c1.city = c2.city AND c1.customername <> c2.customerName ORDER BY c1.city
We joined the customers table to itself with the following join conditions:
c1.city = c2.cityto make sure that both customers have the same cityc.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.