如何在SQL中无需多次JOIN语句关联多列员工编号表与姓名表

我有一张结构如下的表（暂称EmpHierarchy）：

Top|Second|Mid   |Bot
1  | 2    | 3    | 4
1  | 2    | 7    | null
1  | 5    | 6    | null
1  | 8    | null | null
1  | 9    |10    | 11

表中所有数字均为员工编号。另有一张员工信息表Employees，结构如下：

Emp No | Name
1      | A
2      | B
3      | C
...

我希望将两张表关联，为每个员工编号匹配对应的姓名，得到如下格式的结果：

Top|TopName |Second| SecName | Mid   | MidName | Bot | BotName
1  | A      | 2    | B       | 3     | C       | 4   |  D
1  | A      | 2    | B       | 7     | G       | null| null
1  | A      | 5    | E       | 6     | F       | null| null
1  | A      | 8    | H       | null  | null    | null| null
1  | A      | 9    | I       | 10    | J       |  11 | K

我知道可以通过多次JOIN分别关联Top、Second、Mid、Bot列实现，但想寻找更简洁的方案，有没有无需重复写多次JOIN的方法？

本质上因为每一列都是独立的员工编号，还是需要和员工表做四次关联，但可以通过以下两种方式简化写法，让代码更整洁：

方式一：横向连接（LATERAL JOIN/OUTER APPLY） #

这种方式避免重复写JOIN的表名和连接条件，代码结构更清晰。

PostgreSQL/MySQL 8+ 写法 #

SELECT
  eh.Top,
  t.Name AS TopName,
  eh.Second,
  s.Name AS SecName,
  eh.Mid,
  m.Name AS MidName,
  eh.Bot,
  b.Name AS BotName
FROM EmpHierarchy eh
LEFT JOIN LATERAL (SELECT Name FROM Employees WHERE "Emp No" = eh.Top) t ON true
LEFT JOIN LATERAL (SELECT Name FROM Employees WHERE "Emp No" = eh.Second) s ON true
LEFT JOIN LATERAL (SELECT Name FROM Employees WHERE "Emp No" = eh.Mid) m ON true
LEFT JOIN LATERAL (SELECT Name FROM Employees WHERE "Emp No" = eh.Bot) b ON true;

SQL Server 写法 #

SELECT
  eh.Top,
  t.Name AS TopName,
  eh.Second,
  s.Name AS SecName,
  eh.Mid,
  m.Name AS MidName,
  eh.Bot,
  b.Name AS BotName
FROM EmpHierarchy eh
OUTER APPLY (SELECT Name FROM Employees WHERE "Emp No" = eh.Top) t
OUTER APPLY (SELECT Name FROM Employees WHERE "Emp No" = eh.Second) s
OUTER APPLY (SELECT Name FROM Employees WHERE "Emp No" = eh.Mid) m
OUTER APPLY (SELECT Name FROM Employees WHERE "Emp No" = eh.Bot) b;

方式二：SELECT列表中子查询直接获取姓名 #

这种写法最紧凑，不需要额外的JOIN语句：

SELECT
  eh.Top,
  (SELECT Name FROM Employees WHERE "Emp No" = eh.Top) AS TopName,
  eh.Second,
  (SELECT Name FROM Employees WHERE "Emp No" = eh.Second) AS SecName,
  eh.Mid,
  (SELECT Name FROM Employees WHERE "Emp No" = eh.Mid) AS MidName,
  eh.Bot,
  (SELECT Name FROM Employees WHERE "Emp No" = eh.Bot) AS BotName
FROM EmpHierarchy eh;

• 如果员工编号在Employees表中可能不存在，要确保使用LEFT JOIN/OUTER APPLY或者子查询（天然返回NULL），避免过滤掉原有数据。
• 给Employees表的Emp No列创建索引，能大幅提升关联查询的性能。

内容的提问来源于stack exchange，提问作者Martin