使用 JOIN 合併 MySQL 中不同表格的資料

內部 JOIN 和交叉 JOIN

預設的 JOIN 稱為內部 JOIN。在 MySQL 中，可以使用 INNER JOIN、僅 JOIN 或 CROSS JOIN 來指定。對於其他資料庫系統，INNER JOIN 和 CROSS JOIN 通常是兩個獨立的概念，但 MySQL 在相同的建構中實作它們。

以下是一個典型的範例，示範內部 JOIN 的語法

SELECT
    *
FROM
    table_1
[INNER] JOIN table_2
    ON table_1.id = table_2.table_1_id;

內部 JOIN 是限制性最強的 JOIN 類型，因為它只顯示透過結合每個表格中的列所建立的列。組成表格中任何在另一個表格中沒有匹配對應項的列都會從結果中移除。例如，如果第一個表格在比較欄位中的值為「藍色」，而第二個表格沒有具有該值的記錄，則該列將從輸出中隱藏。

如果您將結果表示為組成表格的 Venn 圖，則內部 JOIN 允許您表示兩個圓圈的重疊區域。僅存在於其中一個表格中的任何值都不會顯示。

如上所述，MySQL 也使用此格式來產生交叉 JOIN。在 MySQL 中，您可以使用沒有任何匹配條件的內部 JOIN 來產生交叉 JOIN。交叉 JOIN 不使用任何比較來確定每個表格中的列是否彼此匹配。相反地，結果是透過簡單地將第一個表格中的每個列添加到第二個表格的每個列來建構的。

這會產生兩個或多個表格中列的笛卡爾積。實際上，這種 JOIN 樣式會無條件地結合來自每個表格的列。因此，如果每個表格都有三列，則產生的表格將有九列，其中包含來自兩個表格的所有欄位。

例如，如果您有一個名為 t1 的表格，與一個名為 t2 的表格結合，每個表格都有列 r1、r2 和 r3，則結果將是九列，如下所示結合

t1.r1 + t2.r1
t1.r1 + t2.r2
t1.r1 + t2.r3
t1.r2 + t2.r1
t1.r2 + t2.r2
t1.r2 + t2.r3
t1.r3 + t2.r1
t1.r3 + t2.r2
t1.r3 + t2.r3

左側 JOIN

左側 JOIN 是一種 JOIN，它顯示內部 JOIN 中找到的所有記錄，以及來自第一個表格的所有不匹配的列。在 MYSQL 中，可以指定為 LEFT OUTER JOIN 或僅指定為 LEFT JOIN。

左側 JOIN 的基本語法遵循此模式

SELECT
    *
FROM
    table_1
LEFT JOIN table_2
    ON table_1.id = table_2.table_1_id;

左側 JOIN 的建構方式是先執行內部 JOIN，從兩個表格中的所有相符記錄建構列。之後，也包含來自第一個表格的不匹配記錄。由於 JOIN 中的每個列都包含兩個表格的欄位，因此不匹配的欄位使用 NULL 作為第二個表格中所有欄位的值。

如果您將結果表示為組成表格的 Venn 圖，則左側 JOIN 允許您表示整個左側圓圈。左側圓圈中由兩個圓圈之間的交集表示的部分將具有由右側表格補充的其他資料。

右側 JOIN

右側 JOIN 是一種 JOIN，它顯示內部 JOIN 中找到的所有記錄，以及來自第二個表格的所有不匹配的列。在 MySQL 中，可以指定為 RIGHT OUTER JOIN 或僅指定為 RIGHT JOIN。

右側 JOIN 的基本語法遵循此模式

SELECT
    *
FROM
    table_1
RIGHT JOIN table_2
    ON table_1.id = table_2.table_1_id;

右側 JOIN 的建構方式是先執行內部 JOIN，從兩個表格中的所有相符記錄建構列。之後，也包含來自第二個表格的不匹配記錄。由於 JOIN 中的每個列都包含兩個表格的欄位，因此不匹配的欄位使用 NULL 作為第一個表格中所有欄位的值。

如果您將結果表示為組成表格的 Venn 圖，則右側 JOIN 允許您表示整個右側圓圈。右側圓圈中由兩個圓圈之間的交集表示的部分將具有由左側表格補充的其他資料。

為了便於移植，MySQL 建議您在可能的情況下使用左側 JOIN 而不是右側 JOIN。

完整 JOIN

完整 JOIN 是一種 JOIN，它顯示內部 JOIN 中找到的所有記錄，以及來自兩個組成表格的所有不匹配的列。MySQL 原生不實作完整 JOIN，但我們可以使用一些技巧來模擬其行為。

為了複製完整外部 JOIN 的結果，我們將執行左側 JOIN，以取得兩個表格共用的所有結果，以及來自左側表格的所有不匹配列。然後，我們將使用 UNION ALL 集合運算子，將這些結果與右側表格的「反 JOIN」結合。「反 JOIN」是一種 JOIN 運算，專門尋找表格之間不共有的結果。

完整 JOIN 的基本語法遵循此模式

(   SELECT
        *
    FROM
        table_1
    LEFT JOIN table_2
        ON table_1.id = table_2.table_1_id
)
UNION ALL
(   SELECT
        *
    FROM
        table_1
    RIGHT JOIN table_2
        ON table_1.id = table_2.table_1_id
    WHERE table_1.id IS NULL
);

由於 JOIN 中的每個列都包含兩個表格的欄位，因此不匹配的欄位使用 NULL 作為不匹配的另一個表格中所有欄位的值。

如果您將結果表示為組成表格的 Venn 圖，則完整 JOIN 允許您完整表示兩個組成圓圈。兩個圓圈的交集將具有由每個組成表格提供的值。圓圈中重疊區域外部的部分將具有來自它們所屬表格的值，並使用 NULL 來填入在另一個表格中找到的欄位。

自我 JOIN

自我 JOIN 是任何將表格的列與自身結合的 JOIN。可能不容易立即看出這有什麼用處，但它實際上有很多常見的應用。

通常，表格描述可以履行彼此關係中多個角色的實體。例如，如果您有一個 people 表格，則每列都可能包含一個 mother 欄位，該欄位參考表格中的其他 people。自我 JOIN 允許您透過將表格的第二個實例 JOIN 到第一個實例來將這些不同的列縫合在一起，其中這些值匹配。

由於自我 JOIN 兩次參考相同的表格，因此需要表格別名來消除參考的歧義。例如，在上面的範例中，您可以透過使用別名 people AS children 和 people AS mothers 來 JOIN people 表格的兩個實例。這樣，您就可以在定義 JOIN 條件時指定您要參考的表格實例。

這是另一個範例，這次表示員工和經理之間的關係

SELECT
    *
FROM
    people AS employee
JOIN people AS manager
    ON employee.manager_id = manager.id;

ON 子句

定義表格 JOIN 條件最標準的方式是使用 ON 子句。ON 子句使用等號來指定每個表格中的確切欄位，這些欄位將被比較以確定何時可能發生 JOIN。MySQL 使用提供的欄位將每個表格中的列縫合在一起。

ON 子句是最冗長的，但也是可用的 JOIN 條件中最靈活的。無論要合併的每個表格的欄位名稱標準化程度如何，它都允許特異性。

ON 子句的基本語法如下所示

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.ident;

在這裡，每當 table1 的 id 欄位與 table2 的 ident 欄位相符時，table1 和 table2 中的列將被 JOIN 在一起。由於使用了內部 JOIN，因此結果將僅顯示已 JOIN 的列。由於查詢使用了萬用字元 *，因此將顯示兩個表格的所有欄位。

這表示將顯示 table1 的 id 欄位和 table2 的 ident 欄位，即使它們由於滿足 JOIN 條件而具有完全相同的值。您可以透過在 SELECT 欄位清單中呼叫您希望顯示的確切欄位來避免這種重複。

USING 子句

USING 子句是用於指定 ON 子句條件的簡寫，當要比較的欄位在兩個表格中具有相同的名稱時可以使用。USING 子句採用括在括號中的共用欄位名稱清單，這些欄位名稱應進行比較。

USING 子句的一般語法使用此格式

SELECT
    *
FROM
    table1
JOIN
    table2
USING
    (id, state);

當兩個表格共有的兩個欄位（id 和 state）各自具有匹配的值時，此 JOIN 會將 table1 與 table2 結合。

可以使用 ON 更詳細地表示相同的 JOIN，如下所示

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.id AND table1.state = table2.state;

雖然上述兩個 JOIN 都會導致建構相同的列，並呈現相同的資料，但它們的顯示方式會略有不同。雖然 ON 子句包含兩個表格的所有欄位，但 USING 子句會隱藏重複的欄位。因此，結果不會有兩個單獨的 id 欄位和兩個單獨的 state 欄位（每個表格一個），而只會有每個共用欄位的一個，然後是 table1 和 table2 提供的所有其他欄位。

NATURAL 子句

NATURAL 子句是另一個簡寫，可以進一步減少 USING 子句的冗長性。NATURAL JOIN 不指定要匹配的任何欄位。相反地，MySQL 將根據每個資料庫中具有匹配欄位的所有欄位自動 JOIN 表格。

NATURAL JOIN 子句的一般語法如下所示

SELECT
    *
FROM
    table1
NATURAL JOIN
    table2;

假設 table1 和 table2 都具有名為 id、state 和 company 的欄位，則上述查詢將等效於使用 ON 子句的此查詢

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.id AND table1.state = table2.state AND table1.company = table2.company;

以及使用 USING 子句的此查詢

SELECT
    *
FROM
    table1
JOIN
    table2
USING
    (id, state, company);

與 USING 子句一樣，NATURAL 子句會隱藏重複的欄位，因此在結果中只會有每個 JOIN 欄位的單個實例。

雖然 NATURAL 子句可以減少查詢的冗長性，但在使用它時必須謹慎。由於用於 JOIN 表格的欄位是自動計算的，因此如果組成表格中的欄位發生變更，則由於新的 JOIN 條件，結果可能會大相逕庭。