您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> SQL 基礎

SQL FULL OUTER JOIN 完整教學：雙向外部聯結語法與實戰

編輯：SQL 基礎

SQL FULL OUTER JOIN 雙向外部聯結

FULL OUTER JOIN 核心概念

FULL OUTER JOIN 回傳左表與右表「所有」資料列。只要左表（table1）或右表（table2）任一邊有符合 ON 條件的資料，該列就會出現在結果中。若某邊無對應資料，對應欄位顯示 NULL。它等同於 LEFT JOIN 與 RIGHT JOIN 的聯集。

語法結構

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

演示資料庫：RUNOOB 範例資料

Websites 表

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘寶         | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鳥教程     | https://www.runoob.com/    | 4689  | CN      |
| 4  | 微博         | https://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow| https://stackoverflow.com/ | 0     | IND     |
+----+--------------+---------------------------+-------+---------+

access_log 表

+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
| 1   | 1       | 45    | 2016-05-10 |
| 2   | 3       | 100   | 2016-05-13 |
| 3   | 1       | 230   | 2016-05-14 |
| 4   | 2       | 10    | 2016-05-14 |
| 5   | 5       | 205   | 2016-05-14 |
| 6   | 4       | 13    | 2016-05-15 |
| 7   | 3       | 220   | 2016-05-15 |
| 8   | 5       | 545   | 2016-05-16 |
| 9   | 3       | 201   | 2016-05-17 |
+-----+---------+-------+------------+

實戰範例

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id = access_log.site_id
ORDER BY access_log.count DESC;

說明：

Websites 中 id=7 (stackoverflow) 在 access_log 無對應資料，結果中 name 顯示 stackoverflow，count/date 為 NULL。
access_log 中若有 site_id 不在 Websites（本範例無），則 name 為 NULL，count/date 保留。
MySQL 不支援 FULL OUTER JOIN，需用 LEFT JOIN UNION RIGHT JOIN 模擬，或改用支援的資料庫（SQL Server、PostgreSQL、Oracle）。

MySQL 模擬 FULL OUTER JOIN 寫法

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log ON Websites.id = access_log.site_id
UNION
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
RIGHT JOIN access_log ON Websites.id = access_log.site_id
ORDER BY access_log.count DESC;