Summary: In this chapter, we will learn how to use MySQL self join that joins a table to itself using join statement.
Introducing to MySQL Self Join
In the previous chapters, we learned already 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.
We use self join when we want to combine rows with other rows in the same table. To perform the self join operation, we must use a table alias to help MySQL distinguish the left table from the right table of the same table.
MySQL self join example
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 organisation structure data. The reportto column is used to determine the manager ID of an employee.
In order to get the whole organisation structure, we can join the employees table to itself using the employeeid and reportto columns. The employees table has two roles itself one is Reporting Manager and the other is who is reporting to directly him.
In the below example, we can see some employees is directly reporting to those employees who have a manager role. However, we don’t see the top manager like CEO ceo 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 NULL .
1
2
3
4
5
6
7
8
| SELECT CONCAT(m.firstname,', ',m.lastname) AS 'Reporting Manager', CONCAT(e.firstname,', ',e.lastname) AS 'Reporting employees'FROM employees eINNER JOIN employees m ON m.employeeid = e.reporttoORDER BY 'Reporting Manager'; |
In this chapter, we just introduced to MySQL self join that allows join a table to itself by using INNER JOIN.
MySQL RIGHT JOIN MySQL GROUP BY
MySQL RIGHT JOIN MySQL GROUP BY
