无匹配关联时如何连接两个数据表？含示例表与预期结果

嘿，看你的需求，常规的LEFT/RIGHT JOIN确实搞不定——你要的是表A的每一行，都要和表B里的每个ID_B分组做一次匹配，哪怕这个ID_B分组里没有对应表A的ID_A数据，也要保留表A的行，同时补NULL。

先把你的表结构和预期结果再理清楚：

原始表结构 #

表A #

ID_A  VALUE_A
-----------------
A     1
B     2

表B #

ID_B  ID_A  VALUE_B
-------------------------
90    A     1
90    C     1
90    E     1
91    A     1
91    B     1
92    B     1
92    E     1
92    F     1

预期输出 #

ID_A  VALUE_A  ID_B  ID_A  VALUE_B
-------------------------------------------------
A     1        90    A     1
B     2        90    NULL  NULL
A     1        91    A     1
B     2        91    B     1
A     1        92    NULL  NULL
B     2        92    B     1

解决方案 #

核心思路是先拿到表B里所有唯一的ID_B，和表A做笛卡尔积（CROSS JOIN），生成每个ID_B对应表A所有行的基础组合，再LEFT JOIN回表B匹配对应数据。具体SQL如下：

SELECT
    a.ID_A,
    a.VALUE_A,
    b_unique.ID_B,
    b.ID_A AS matched_ID_A,
    b.VALUE_B
FROM
    表A a
-- 先提取表B中所有不重复的ID_B
CROSS JOIN (SELECT DISTINCT ID_B FROM 表B) b_unique
-- 左连接表B，匹配ID_B和ID_A
LEFT JOIN 表B b
    ON b.ID_B = b_unique.ID_B
    AND b.ID_A = a.ID_A
-- 按ID_B和表A的ID_A排序，和预期结果一致
ORDER BY
    b_unique.ID_B,
    a.ID_A;

代码解释 #

1. SELECT DISTINCT ID_B FROM 表B：先把表B里的ID_B去重，得到90、91、92三个唯一值；
2. CROSS JOIN：把这三个ID_B和表A的每一行组合，这样就得到了6条基础数据（3个ID_B × 2行表A）；
3. LEFT JOIN 表B：用ID_B和ID_A同时关联，这样如果某个ID_B下没有对应表A的ID_A，就会返回NULL，完美符合你的预期；
4. ORDER BY：按ID_B和表A的ID_A排序，让结果和你想要的格式一致。

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