在 MySQL 中使用連線（Join）合併不同表的資料

內連線和交叉連線

預設的連線型別稱為內連線（inner join）。在 MySQL 中，這可以透過使用 INNER JOIN、僅 JOIN 或 CROSS JOIN 來指定。對於其他資料庫系統，INNER JOIN 和 CROSS JOIN 通常是兩個獨立的概念，但 MySQL 在相同的結構中實現了它們。

這是一個典型的例子，展示了內連線的語法

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

內連線是最嚴格的連線型別，因為它只顯示透過組合每個表中的行而建立的行。任何在構成表中沒有匹配對應項的行都將從結果中移除。例如，如果第一個表中比較列的值為“blue”，而第二個表中沒有具有該值的記錄，則該行將被從輸出中抑制。

如果您將結果表示為組成表的維恩圖，內連線允許您表示兩個圓的重疊區域。只存在於其中一個表中的值都不會顯示。

如上所述，MySQL 也使用這種格式來生成交叉連線。在 MySQL 中，您可以使用不帶任何匹配條件的內連線來生成交叉連線。交叉連線不使用任何比較來確定每個表中的行是否相互匹配。相反，結果是透過簡單地將第一個表中的每一行新增到第二個表的每一行來構建的。

這會生成兩個或多個表中行的笛卡爾積。實際上，這種連線方式是無條件地組合每個表中的行。因此，如果每個表有三行，則結果表將有九行，包含來自兩個表的所有列。

例如，如果您有一個名為 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

左連線

左連線（left join）是一種顯示內連線中所有記錄，外加第一個表中所有不匹配行的連線。在 MySQL 中，這可以指定為 LEFT OUTER JOIN 或僅 LEFT JOIN。

左連線的基本語法遵循以下模式

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

左連線的構建方式是：首先執行內連線以從兩個表中的所有匹配記錄構建行。然後，第一個表中不匹配的記錄也會被包含進來。由於連線中的每一行都包含兩個表的列，因此不匹配的列會使用 NULL 作為第二個表中所有列的值。

如果您將結果表示為組成表的維恩圖，左連線允許您表示整個左圓。左圓中由兩個圓交集表示的部分將包含由右表補充的額外資料。

右連線

右連線（right join）是一種顯示內連線中所有記錄，外加第二個表中所有不匹配行的連線。在 MySQL 中，這可以指定為 RIGHT OUTER JOIN 或僅 RIGHT JOIN。

右連線的基本語法遵循以下模式

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

右連線的構建方式是：首先執行內連線以從兩個表中的所有匹配記錄構建行。然後，第二個表中不匹配的記錄也會被包含進來。由於連線中的每一行都包含兩個表的列，因此不匹配的列會使用 NULL 作為第一個表中所有列的值。

如果您將結果表示為組成表的維恩圖，右連線允許您表示整個右圓。右圓中由兩個圓交集表示的部分將包含由左表補充的額外資料。

出於可移植性原因，MySQL 建議您儘可能使用左連線而不是右連線。

全連線

全連線（full join）是一種顯示內連線中所有記錄，外加兩個組成表中所有不匹配行的連線。MySQL 不原生實現全連線，但我們可以透過一些技巧來模擬其行為。

為了複製全外連線的結果，我們將對兩個表共享的所有結果以及左表所有不匹配的行執行左連線。然後我們將使用 UNION ALL 集合運算子將這些結果與右表的“反連線”組合起來。“反連線”是一種專門查詢表之間不共有結果的連線操作。

全連線的基本語法遵循以下模式

(   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
);

由於連線中的每一行都包含兩個表的列，因此不匹配的列會使用 NULL 作為不匹配的另一個表中所有列的值。

如果您將結果表示為組成表的維恩圖，全連線允許您完全表示兩個組成圓。兩個圓的交集將包含由每個組成表提供的值。圓中重疊區域之外的部分將包含它們所屬表中的值，並使用 NULL 填充在另一個表中找到的列。

自連線

自連線是任何將一個表的行與自身組合的連線。這可能不會立即看出其用途，但它實際上有許多常見的應用。

通常，表描述的實體之間可能相互扮演多種角色。例如，如果您有一個 people 表，每行都可能包含一個 mother 列，該列引用表中的其他 people。自連線將允許您透過將表的第二個例項連線到第一個例項（其中這些值匹配）來將這些不同的行拼接在一起。

由於自連線會兩次引用同一張表，因此需要表別名來消除引用歧義。例如，在上面的例子中，您可以使用別名 people AS children 和 people AS mothers 來連線 people 表的兩個例項。這樣，在定義連線條件時，您可以指定所引用的表例項。

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

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

ON 子句

定義表連線條件最標準的方式是使用 ON 子句。 ON 子句使用等號來指定每個表中將進行比較的精確列，以確定何時可以發生連線。MySQL 使用提供的列將每個表中的行拼接在一起。

ON 子句是可用連線條件中最冗長的，但也是最靈活的。它允許無論組合的每個表的列名有多標準化，都能進行精確指定。

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

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

這裡，當 table1 中的 id 列與 table2 中的 ident 列匹配時，table1 和 table2 的行將被連線。由於使用了內連線，結果將只顯示已連線的行。由於查詢使用了萬用字元 * 字元，因此將顯示來自兩個表的所有列。

這意味著 table1 中的 id 列和 table2 中的 ident 列都將顯示，儘管它們因滿足連線條件而具有完全相同的值。您可以透過在 SELECT 列列表中指定要顯示的精確列來避免這種重複。

USING 子句

USING 子句是指定 ON 子句條件的一種簡寫形式，當要比較的列在兩個表中具有相同的名稱時可以使用。 USING 子句接受一個用括號括起來的列表，其中包含應進行比較的共享列名。

USING 子句的通用語法使用以下格式

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

當兩個表共有的兩列（id 和 state）各自具有匹配值時，此連線將 table1 與 table2 組合起來。

同樣的連線可以使用 ON 更冗長地表達，如下所示

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

雖然上述兩種連線都會構建出相同資料的結果行，但它們的顯示方式會略有不同。 ON 子句包含兩個表的所有列，而 USING 子句會抑制重複列。因此，結果中不會有兩列獨立的 id 和兩列獨立的 state（每個表各一列），而是隻有共享列的各一個例項，然後是 table1 和 table2 提供的所有其他列。

NATURAL 子句

NATURAL 子句是另一種簡寫形式，可以進一步減少 USING 子句的冗長。 NATURAL 連線不指定任何要匹配的列。相反，MySQL 將根據每個資料庫中具有匹配列的所有列自動連線表。

NATURAL 連線子句的通用語法如下所示

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 子句會抑制重複列，因此結果中每個連線列將只出現一個例項。

雖然 NATURAL 子句可以減少查詢的冗長，但在使用時必須小心。因為用於連線表的列是自動計算的，如果組成表中的列發生變化，由於新的連線條件，結果可能會大相徑庭。