mysql - 在这种情况下如何在 MySQL 中使用连接

Question

我有一张员工表和一张部门表。我如何使用 join 来获得下面所需的结果。 employee 表中的 manager_id 只是员工 ID。请帮我找出答案

    Employee Table        

    id  | name  |   manager_id | department_id            
    ----------------------------------------              
    1   |  A    |   NULL       | 1                          
    2   |  B    |   1          | 2                        
    3   |  C    |   NULL       | 3                         
    4   |  D    |   3          | 2

     Department Table

     id    |   department_name
     -------------------------
     1    | Admin
     2    | HR
     3    | Finance 


    Required OutPut

    id  | name  |  manager_name   | department_name
    -----------------------------------------------
    1   |  A    |   NULL          | Admin                
    2   |  B    |   A             | HR                
    3   |  C    |   NULL          | Finance                
    4   |  D    |   C             | HR

Answer 1

同时使用内连接和外连接

select E1.ID, E1.Name, E2.Name, D.department_name
FROM Employee E1
LEFT OUTER JOIN Employee E2 ON E2.ID = E1.manager_id
INNER JOIN Department D ON D.id = E1.department_id