sql - 如果一个或多个表中可能缺少该键，您能否在同一个键上连接三个表？

Question

我正在使用三个表并使用相同的主键将它们全部连接在一起，但不能保证所有值都存在于所有三个表中，我希望每个值都在最终连接中结束。

例如，有一个 TableA、TableB 和 TableC，每个只有一列(我们将这些列称为 colA、colB 和 colC)。值 10 存在于 TableA 和 TableC 中，但不存在于 TableB 中。

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.colA = TableB.colB 
                     FULL OUTER JOIN TableC ON TableB.colB = TableC.colC;

+--------+  +--------+  +--------+
|  colA  |  |  colB  |  |  colC  |
+--------+  +--------+  +--------+
|      5 |  |      5 |  |      5 |
|     10 |  |     15 |  |     10 |
|     15 |  +--------+  |     15 |
+--------+              +--------+

在上面的例子中，我在加入之后得到了这样的结果:

+--------+--------+--------+
|  colA  |  colB  |  colC  |
+--------+--------+--------+
| 5      | 5      | 5      |
| null   | null   | 10     |
| 15     | 15     | 15     |
| 10     | null   | null   |
+--------+--------+--------+

我的预期结果是这样的:

+--------+--------+--------+
|  colA  |  colB  |  colC  |
+--------+--------+--------+
|      5 | 5      |      5 |
|     10 | null   |     10 |
|     15 | 15     |     15 |
+--------+--------+--------+

我的联接逻辑是先联接TableA 和TableB，然后将结果与TableC 联接。在这种特定情况下，连接不起作用。联接可以尝试匹配从 TableA 传递的列或从 TableB 传递的列，但我不确定如何让它检查两者。

因为我先加入了TableA和TableB，所以TableB中没有匹配值。当我尝试在 TableB 上加入 TableC 时，它也找不到匹配项，即使该键存在于 TableA 中也是如此。

最接近我想要的结果的是使用这个查询:

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.colA = TableB.colB 
                     RIGHT OUTER JOIN TableC ON TableC.colC IN (TableA.colA, TableB.colB);

+------+  +------+  +------+     +------+------+------+
| colA |  | colB |  | colC |     | colA | colB | colC |
+------+  +------+  +------+     +------+------+------+
|    5 |  |    5 |  |    5 |     | 5    | 5    |    5 |
|   10 |  |   15 |  |   10 | --> | 10   | null |   10 |
|   15 |  |   25 |  |   15 |     | 15   | 15   |   15 |
+------+  +------+  |   20 |     | null | null |   20 |
                    +------+     +------+------+------+

预期结果:

+------+------+------+
| colA | colB | colC |
+------+------+------+
| 5    | 5    | 5    |
| 10   | null | 10   |
| 15   | 15   | 15   |
| null | null | 20   |
| null | 25   | null |
+------+------+------+

不幸的是，这不包括仅存在于最终连接表中的 TableA 或 TableB 中的值，这就是为什么我首先使用 FULL OUTER JOIN。对于 FULL OUTER JOIN，我不能使用 IN 子句。是否有解决方案可以满足我的需求？

数据库 fiddle :https://www.db-fiddle.com/f/faMLh4EAAKfBotXbaDofaQ/1

Answer 1

这是一个演示完全三向完全外部联接的版本。成功的查询将返回七行:

+------+  +------+  +------+     +------+------+------+| colA |  | colB |  | colC |     | colA | colB | colC |+------+  +------+  +------+     +------+------+------+|    1 |  |    2 |  |    4 |     |    1 | null | null ||    3 |  |    3 |  |    5 | --> | null |    2 | null ||    5 |  |    6 |  |    6 |     |    3 |    3 | null ||    7 |  |    7 |  |    7 |     | null | null |    4 |+------+  +------+  +------+     |    5 | null |    5 |                                 | null |    6 |    6 |                                 |    7 |    7 |    7 |                                 +------+------+------+

select ColA, ColB, ColC
  from TableA
  full join TableB
    on ColA=ColB
  full join TableC
    on ColC=coalesce(ColA, ColB)
order by coalesce(ColA, ColB, ColC);

秘诀是在每个附加表的连接条件中合并先前的表键。

您可以在实际中看到它 here