sql - 外连接中出现意外的 NULL

Question

我有 3 个表要加入。我们称它们为 TableA、TableB 和 TableC:

DECLARE @TableA TABLE
(
    Key1 int,
    PRIMARY KEY
    (
        Key1
    )
)
DECLARE @TableB TABLE
(
    Key1 int,
    Key2 int,
    PRIMARY KEY
    (
        Key1,
        Key2
    )
)
DECLARE @TableC TABLE
(
    Key3 int NOT NULL,
    Key1 int NOT NULL,
    Key2 int NULL,
    PRIMARY KEY
    (
        Key3
    )
)

这是一些示例数据:

INSERT INTO @TableA (Key1) VALUES (1);
INSERT INTO @TableB (Key1, Key2) VALUES (1, 1), (1, 2), (1, 3), (1, 4)
INSERT INTO @TableC (Key3, Key1, Key2) VALUES (1, 1, NULL), (2, 1, NULL), (3, 1, 1), (4, 1, 3)

TableB 和 TableC 都有通过 Key1 到 TableA 的外键。实际上，如果 Key2 不为 null，则 TableC 也可以通过 Key1 和 Key2 组合来引用 TableB，但没有实际的外键。 Key3 是无关紧要的，除了 Key1 和 Key2 不是 TableC 的主键的一部分。

我正在尝试编写一个结合了 TableB 和 TableC 的查询:

SELECT
    TableA.Key1 AS [A1],
    TableB.Key1 AS [B1],
    TableB.Key2 AS [B2],
    TableC.Key1 AS [C1],
    TableC.Key2 AS [C2],
    TableC.Key3 AS [C3]
FROM @TableA AS TableA
FULL OUTER JOIN @TableC AS TableC
    ON TableC.Key1 = TableA.Key1
FULL OUTER JOIN @TableB AS TableB
    ON (TableB.Key1 = TableA.Key1 AND TableC.Key1 IS NULL)
    OR (TableC.Key1 = TableB.Key1 AND TableC.Key2 = TableB.Key2)
WHERE (TableA.Key1 = TableB.Key1 OR TableA.Key1 = TableC.Key1)
ORDER BY TableB.Key2, TableC.Key2

我的期望是 TableB 和 TableC 都应该包含它们的所有行，匹配在两个键上匹配的行，以及不匹配的 NULLS。

我希望得到这个:

A1       B1       B2       C1       C2      C3
1        NULL     NULL     1        NULL    1
1        NULL     NULL     1        NULL    2
1        1        1        1        1       3
1        1        2        NULL     NULL    NULL -- THIS ROW IS MISSING
1        1        3        1        3       4
1        1        4        NULL     NULL    NULL -- THIS ROW IS MISSING

但我得到的是:

A1       B1       B2       C1       C2      C3
1        NULL     NULL     1        NULL    1
1        NULL     NULL     1        NULL    2
1        1        1        1        1       3
1        1        3        1        3       4

如果我注释掉 WHERE 子句，我会得到我期望的所有行，除了 A1 对于缺失的行为 NULL:

A1       B1       B2       C1       C2      C3
1        NULL     NULL     1        NULL    1
1        NULL     NULL     1        NULL    2
1        1        1        1        1       3
NULL     1        2        NULL     NULL    NULL   -- A1 should be 1
1        1        3        1        3       4
NULL     1        4        NULL     NULL    NULL   -- A1 should be 1

为什么 TableA.Key1 返回 NULL 并导致它排除缺少 TableB.Key2 的行？

编辑:

这是我了解自己做错了什么之后的最终固定查询:

SELECT
    TableA.Key1 AS A1,
    Subquery.*
FROM @TableA AS TableA
INNER JOIN
(
    SELECT
        TableB.Key1 AS [B1],
        TableB.Key2 AS [B2],
        TableC.Key1 AS [C1],
        TableC.Key2 AS [C2],
        TableC.Key3 AS [C3]
    FROM @TableC AS TableC
    FULL OUTER JOIN @TableB AS TableB
        ON TableB.Key1 = TableC.Key1 AND TableB.Key2 = TableC.Key2
) AS Subquery 
    ON Subquery.B1 = TableA.Key1 OR Subquery.C1 = TableA.Key1
ORDER BY Subquery.B2, Subquery.C2

Answer 1

Why is TableA.Key1 coming back NULL and causing it to exclude rows where TableB.Key2 is missing?

完全外部联接与 INNER JOIN 相同但是来自任何一方的任何不匹配的行都被添加回 NULL对于另一侧的列。

您的查询对 A 进行了完全外部联接和 C首先，请先查看结果。

SELECT
    TableA.Key1 AS [A1],
    TableC.Key1 AS [C1],
    TableC.Key2 AS [C2],
    TableC.Key3 AS [C3]
FROM @TableA AS TableA
FULL OUTER JOIN @TableC AS TableC
    ON TableC.Key1 = TableA.Key1

这将返回进入下一阶段的以下虚拟表 (VT1)。因为这与 INNER JOIN 的结果相同我怀疑它需要任何解释。 @TableC 中的每一行成功匹配 @TableA 中的单行.

+----+----+------+----+
| A1 | C1 |  C2  | C3 |
+----+----+------+----+
|  1 |  1 | NULL |  1 |
|  1 |  1 | NULL |  2 |
|  1 |  1 | 1    |  3 |
|  1 |  1 | 3    |  4 |
+----+----+------+----+

然后将其完全外部连接到 B 上. B的内容是

+------+------+
| Key1 | Key2 |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    1 |    4 |
+------+------+

INNER JOIN这两个结果集的谓词 ON (TableB.Key1 = [A1] AND [C1] IS NULL) OR ([C1] = TableB.Key1 AND [C2] = TableB.Key2)仅返回 2 行。

+----+----+----+----+----+----+
| A1 | B1 | B2 | C1 | C2 | C3 |
+----+----+----+----+----+----+
|  1 |  1 |  1 |  1 |  1 |  3 |
|  1 |  1 |  3 |  1 |  3 |  4 |
+----+----+----+----+----+----+

来自 VT1 的不匹配行根据 LEFT JOIN 添加回(这些是 C3 是 1 或 2 的)

+----+------+------+----+------+----+
| A1 |  B1  |  B2  | C1 |  C2  | C3 |
+----+------+------+----+------+----+
|  1 | NULL | NULL |  1 | NULL |  1 |
|  1 | NULL | NULL |  1 | NULL |  2 |
|  1 | 1    | 1    |  1 | 1    |  3 |
|  1 | 1    | 3    |  1 | 3    |  4 |
+----+------+------+----+------+----+

和来自 B 的不匹配行根据 RIGHT JOIN (这些是 B2 是 2 或 4 的那些)

给你最终的结果

+------+------+------+------+------+------+
|  A1  |  B1  |  B2  |  C1  |  C2  |  C3  |
+------+------+------+------+------+------+
| 1    | NULL | NULL | 1    | NULL | 1    |
| 1    | NULL | NULL | 1    | NULL | 2    |
| 1    | 1    | 1    | 1    | 1    | 3    |
| 1    | 1    | 3    | 1    | 3    | 4    |
| NULL | 1    | 2    | NULL | NULL | NULL |
| NULL | 1    | 4    | NULL | NULL | NULL |
+------+------+------+------+------+------+