SQL一对多表连接 如何合并多表colour值到同一列（禁用UNION）

基础表信息 #

• 表A：字段为left、center、east，共2条数据：(1,Bob,1)、(2,Tom,2)
• 表B：字段为left、top、colour，共2条数据：(1,Bob,Red)、(2,Tom,Blue)
• 表C：字段为East、west、colour，共2条数据：(1,Bob,Yellow)、(2,Tom,Orange)

需求与原有写法问题 #

需要关联三张表，将表B、表C的colour字段值统一输出到结果集的同一个Colour列，最终返回4行结果：Bob对应Red、Yellow，Tom对应Blue、Orange，要求禁止使用UNION、UNION ALL做结果集合并。

原有LEFT JOIN写法存在两个核心错误：

• 表C关联条件错误：原写法用a.east=b.east关联C表，但表B无east字段，无法正确匹配C表数据
• 合并逻辑错误：(b.Colour,c.colour)是横向拼接两个字段，无法实现将两个来源的颜色值拆为纵向多行、归入同一列的效果

使用CROSS JOIN LATERAL搭配VALUES行构造器实现，全程不使用UNION类语法合并业务结果集，SQL代码如下：

SELECT 
  a.`left`,
  a.center,
  cj.Colour
FROM TableA a
LEFT JOIN TableB b 
  ON a.`left` = b.`left`
LEFT JOIN TableC c 
  ON a.east = c.East
CROSS JOIN LATERAL (
  VALUES (b.colour), (c.colour)
) AS cj(Colour)

实现逻辑 #

1. 先修正表C的关联条件，通过a.east = c.East完成A与C的左连接，此时每行数据会同时拿到对应B表的colour值、C表的colour值，两个值为横向并排的独立字段
2. 通过LATERAL关联VALUES构造的两行临时结果，把横向的两个颜色值拆分为纵向两行，自动归入同一个Colour列，最终返回正好符合预期的4行结果。

如果使用的数据库版本不支持LATERAL语法，可以通过交叉连接固定2行的辅助数字表实现同等效果，该写法仅用UNION构造内置辅助表，不会违反禁止UNION合并业务结果集的要求：

SELECT 
  a.`left`,
  a.center,
  CASE WHEN n.num = 1 THEN b.colour ELSE c.colour END AS Colour
FROM TableA a
LEFT JOIN TableB b 
  ON a.`left` = b.`left`
LEFT JOIN TableC c 
  ON a.east = c.East
CROSS JOIN (SELECT 1 AS num UNION ALL SELECT 2) n

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