如何在 MySQL 中執行 FULL OUTER JOIN？

Question

我想在MySQL中執行完全外連接。這可能嗎？ MySQL支援全外連線嗎？

P粉022723606 · Answer

Pablo Santa Cruz 給出的答案是正確的；但是，如果有人偶然發現此頁面並需要更多說明，這裡有詳細的細分。

範例表格

假設我們有下表：

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

內連線

內部連接，如下所示：

SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

只會讓我們看到兩個表中都出現的記錄，如下所示：

1 Tim  1 Tim

內部聯結沒有方向（例如左或右），因為它們是明確雙向的 - 我們需要兩側都匹配。

外連接

另一方面，外連接用於尋找在另一個表中可能不匹配的記錄。因此，您必須指定允許連線的哪一側有缺失記錄。

LEFT JOIN和RIGHT JOIN是LEFT OUTER JOIN和RIGHT OUTER JOIN的簡寫；我將在下面使用他們的全名來強化外連結與內連結的概念。

左外連接

左外連接，如下所示：

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

...將從左表中取得所有記錄，無論它們在右表中是否有符合項，如下所示：

1 Tim   1    Tim
2 Marta NULL NULL

右外連線

右外連接，如下所示：

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

...將從右表中取得所有記錄，無論它們在左表中是否有符合項，如下所示：

1    Tim   1  Tim
NULL NULL  3  Katarina

完全外連接

完整的外連接將為我們提供兩個表中的所有記錄，無論它們在另一個表中是否有匹配項，如果不匹配，則兩側都為 NULL。結果如下圖所示：

1    Tim   1    Tim
2    Marta NULL NULL
NULL NULL  3    Katarina

然而，正如 Pablo Santa Cruz 所指出的那樣，MySQL 不支持這一點。我們可以透過左連接和右連接的 UNION 來模擬它，如下所示：

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

您可以將 UNION 視為「執行這兩個查詢，然後將結果堆疊在一起」；有些行將來自第一個查詢，有些來自第二個查詢。

要注意的是，MySQL 中的UNION 會消除精確的重複項：Tim 會出現在此處的兩個查詢中，但UNION 的結果只會列出他一次。我的資料庫專家同事認為不應依賴這種行為。因此，為了更明確地說明這一點，我們可以在第二個查詢中加入一個 WHERE 子句：

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`id` IS NULL;

另一方面，如果您出於某種原因想要查看重複項，則可以使用UNION ALL。

P粉148782096 · Answer

您在 MySQL 中沒有完全連接，但您可以確定模擬它們。

對於從此堆疊溢位問題轉錄的程式碼範例你有：

有兩個表 t1、t2：

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

上面的查詢適用於完全外連接操作不會產生任何重複行的特殊情況。上面的查詢依賴 UNION 集合運算子來刪除查詢模式引入的重複行。我們可以透過對第二個查詢使用反連接模式來避免引入重複行，然後使用 UNION ALL 集合運算子來組合兩個集合。在更一般的情況下，完整的外部聯結將傳回重複的行，我們可以這樣做：

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL