在創(chuàng)建具有相互關(guān)聯(lián)的表的數(shù)據(jù)庫時，您最終可能需要檢索表之間連接的數(shù)據(jù)。

您可以使用 MySQL 連接來完成此操作。連接是一種將多個表中的列獲取到一組結(jié)果中的方法。這通常比嘗試執(zhí)行多個查詢并稍后組合它們更有效。

本文著眼于可以在 MySQL 中執(zhí)行的不同類型的連接，并展示了它們用于組合來自多個表的數(shù)據(jù)的不同方式。

示例場景

為了進(jìn)一步了解連接，我們將創(chuàng)建一個簡單的雜貨數(shù)據(jù)庫，每個商品都有一個類別。類別存儲在categories表中，項目存儲在單獨的items表中。

CREATE TABLE categories (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(250) NOT NULL
);

categories填充數(shù)據(jù)的示例表：

CREATE TABLE items (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(512),
  category_id int NULL
);

CREATE TABLE 產(chǎn)品（id int NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(255) NOT NULL, image_url varchar(255), category_id INT, KEY category_id_idx (category_id)）；

items填充數(shù)據(jù)的示例表：

我們將在整個教程中以這個示例為基礎(chǔ)來探索不同類型的連接以及如何使用它們。

內(nèi)連接

現(xiàn)在我們已經(jīng)存儲了項目和類別，我們可能希望將項目與類別名稱一起顯示，而不僅僅是category_id，因為“Deli”對人類來說比“2”更有意義。

為此，我們可以使用INNER JOIN，它選擇表之間的匹配記錄。這是 JOIN 的默認(rèn)行為，因此INNER JOIN與JOIN.

內(nèi)部連接的常見錯誤

警告：如果您在沒有ON子句的情況下進(jìn)行連接，您將執(zhí)行有時稱為 a 的操作CROSS JOIN，這將對右表中的每一行顯示左表中的每一行一次。這通常不是我們想要的——它會產(chǎn)生更多的結(jié)果。

讓我們看一個交叉連接的例子：

-- Don’t do this unless you know what you are doing:
SELECT * FROM items
JOIN categories; -- No ON columns specified!

我們最終得到的結(jié)果比我們預(yù)想的要多得多！如果有大量數(shù)據(jù)，這將是一團(tuán)糟。

指定要加入類別的列

為了得到我們想要的結(jié)果，我們必須說出哪些列是相關(guān)的。

換句話說，我們不得不說 的主鍵( id)categories與 的外鍵( category_id) 相關(guān)items。

SELECT * FROM items
-- JOIN is the same as INNER JOIN
JOIN categories ON items.category_id = categories.id;

現(xiàn)在我們已經(jīng)方便地返回了每個項目的類別名稱！

為列賦予唯一名稱

您可能會注意到上表中現(xiàn)在有兩個name字段，因為兩個表都有自己的name列。為了使查詢更有用，我們可以使用別名將列輸出為其他內(nèi)容。

對于此示例，我們將使用cforcategories和ifor items。

SELECT * FROM items AS i -- we now refer to items as i
JOIN categories AS c -- we now refer to categories as c
    ON i.category_id = c.id;

注意：使用AS是可選的，所以我們經(jīng)常會看到它被遺漏了。

指定我們要返回的所有列而不是用 請求所有列也是一個好主意*，尤其是在使用具有許多列的表時，因為這可以使查詢運行得更快。在此示例中，讓我們省略類別id列。

因為多次選擇相同的列名，我們還應(yīng)該指定這些列來自哪些表。我們可以為此使用i和c別名。

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name -- now refer to categories.name AS category_name
FROM items i
JOIN categories c ON i.category_id = c.id;

現(xiàn)在返回一個有用的結(jié)果，我們可以顯示給用戶！

回顧一下，我們使用內(nèi)部連接通過關(guān)系將兩個表中的數(shù)據(jù)組合起來。在我們的示例中，有一個左表——在FROM（在本例中為）之后指定的第一個表，以及在( )items之后指定的右表。JOINcategories

內(nèi)連接可以用這個維恩圖表示，顯示返回的唯一數(shù)據(jù)是項目和類別相關(guān)的數(shù)據(jù)。

左右連接

假設(shè)我們向表中添加更多數(shù)據(jù)：

• 沒有類別的項目
• 一個新類別（但還沒有使用它的項目）。

categories

items

如果我們INNER JOIN對這些數(shù)據(jù)進(jìn)行分析，我們會得到以下信息：

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
JOIN categories c ON i.category_id = c.id;

注意到有什么遺漏了嗎？

面包不在！為什么不？

當(dāng)我們對 進(jìn)行內(nèi)部連接時i.category_id = c.id，我們告訴 MySQL 只返回具有類別的記錄。由于“面包”有一個category_idthat is NULL，它不匹配任何東西，因此不返回。

同樣，由于沒有項目具有我們新的“乳制品”類別，因此這也不會出現(xiàn)在結(jié)果中。

通常，您仍然希望返回所有項目，即使是那些在它所連接的表中沒有匹配外鍵的項目。為此，我們可以使用LEFT JOIN確保返回第一個（左）表中的所有項目記錄。RIGHT JOIN工作方式幾乎完全相同，只是它返回正確表中的所有記錄——在本例中，categories.

如果我們LEFT JOIN對這些數(shù)據(jù)執(zhí)行 a ，我們將得到以下信息：

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id;

現(xiàn)在我們有了所有的項目，這要歸功于使用 aLEFT JOIN而不是INNER JOIN!

同樣，我們可以使用 aRIGHT JOIN返回所有類別（但不一定是所有項目）。

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id;

左連接和右連接可以用這些維恩圖表示。

全外連接

如果我們想顯示所有項目和所有類別，我們必須進(jìn)行有時稱為 a 的特殊連接FULL OUTER JOIN，盡管 MySQL 不支持這種類型的連接。但是，我們可以通過同時執(zhí)行aLEFT JOIN和RIGHT JOIN并將它們與 a 組合來模擬這一點UNION。

為此，我們必須添加一個WHERE子句，該子句僅包含來自查詢第二部分的NULL項目的記錄。id否則，那些具有類別的項目將全部顯示兩次。

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id
UNION ALL
SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id
-- This prevents duplicate items from showing
-- as we only want categories with no items.
WHERE i.id IS NULL; 

這種類型的“OUTER JOIN”由該圖表示。

只顯示不相關(guān)的數(shù)據(jù)（WHERE鍵是NULL）

有時只查詢不相關(guān)的記錄會很有幫助。我們可能只想找到未分類的項目——也許這樣我們就可以找到它們來清理它們。

為此，我們可以在 a和中添加一個附加WHERE子句。LEFTRIGHT JOIN

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id
WHERE c.id IS NULL; 

此處表示此 JOIN。

要僅顯示沒有項目的類別，我們可以使用類似RIGHT JOIN的 withWHERE子句，僅顯示帶有NULL項目的記錄id。

SELECT
    i.id,
    i.name,
    i.category_id,
    c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id
WHERE i.id IS NULL; 

此處表示此 JOIN。

僅包含不相關(guān)數(shù)據(jù)的完全外連接

最后，如果我們想同時顯示不相關(guān)的項目和類別，我們可以使用OUTER JOIN查詢類型，但要查找items或categories鍵NULL。

為了使這個查詢工作，它有助于將它的大部分括在括號中并將WHERE子句應(yīng)用于外部查詢。

SELECT * FROM (
SELECT i.id,
          i.name,
          i.category_id,
          c.name AS category_name
   FROM items i
         LEFT JOIN categories c ON i.category_id = c.id
   UNION ALL
   SELECT i.id,
          i.name,
          i.category_id,
          c.name AS category_name
   FROM items i
         RIGHT JOIN categories c ON i.category_id = c.id
   WHERE i.id IS NULL
) AS all_items_all_categories
WHERE id IS NULL OR category_id IS NULL; 

此處表示不相關(guān)項的此 JOIN。

總結(jié)

您現(xiàn)在應(yīng)該了解如何使用連接來組合來自多個表的數(shù)據(jù)以及每種連接類型的不同之處。總結(jié)一下：

• INNER JOIN或JOIN僅返回兩個表中具有匹配鍵的記錄。
• LEFT JOIN只有當(dāng)它們也被第二個表引用時，才返回第一個表中的記錄。
• RIGHT JOIN僅當(dāng)它們也被第一個表引用時才從第二個表返回記錄。
• FULL OUTER JOIN返回兩個表中的所有記錄，即使它們在另一個表中沒有匹配項。
• WHERE可以過濾連接結(jié)果以僅顯示帶有NULL鍵的記錄。
• UNION可以將兩個查詢的結(jié)果合并到一個結(jié)果集中。

對連接有很好的理解，你就可以在 MySQL 中進(jìn)行強大而高效的查詢。

原文標(biāo)題：Introduction to MySQL joins
原文作者：JD Lien
原文地址：https://planetscale.com/blog/introduction-to-sql-joins