mysql self join employees table

上图中reportsTo 列的数据是manager id，找出manager的查询方法如下：

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

mysql self join example可是以上查询没有 top manager ，当reportsto为NULL时，他就是top manager。

等价转化为 当e.reportsto =NULL，m.employeeNumber=NULL，e就是top manager。

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

此时用left join 是因为employees m可能为空，这是被左连接所允许的。