全部教學 » MySQL內連線（INNER JOIN）

MySQL內連線（INNER JOIN）

2019-10-16 22:57:16

在本教學中，您將學習如何使用MySQL INNER JOIN子句根據連線條件從多個表中查詢選擇資料。

MySQL INNER JOIN子句介紹

MySQL INNER JOIN子句將一個表中的行與其他表中的行進行匹配，並允許從兩個表中查詢包含列的行記錄。

INNER JOIN子句是SELECT語句的可選部分，它出現在FROM子句之後。

在使用INNER JOIN子句之前，必須指定以下條件：

  • 首先，在FROM子句中指定主表。
  • 其次，表中要連線的主表應該出現在INNER JOIN子句中。理論上說，可以連線多個其他表。 但是，為了獲得更好的效能，應該限制要連線的表的數量(最好不要超過三個表)。
  • 第三，連線條件或連線謂詞。連線條件出現在INNER JOIN子句的ON關鍵字之後。連線條件是將主表中的行與其他表中的行進行匹配的規則。

INNER JOIN子句的語法如下：

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;

假設使用INNER JOIN子句連線兩個表：t1和t2，我們來簡化上面的語法。

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition;

對於t1表中的每一行，INNER JOIN子句將它與t2表的每一行進行比較，以檢查它們是否都滿足連線條件。當滿足連線條件時，INNER JOIN將返回由t1和t2表中的列組成的新行。

請注意，t1和t2表中的行必須根據連線條件進行匹配。如果找不到匹配項，查詢將返回一個空結果集。當連線超過2個表時，也應用此邏輯。

以下維恩圖說明了INNER JOIN子句的工作原理。結果集中的行必須出現在兩個表中：t1和t2，如兩個圓的交叉部分所示 -

在MySQL INNER JOIN中避免列錯誤

如果連線具有相同列名稱的多個表，則必須使用表限定符參照SELECT和ON子句的列，以避免列錯誤。

例如，如果t1和t2表都具有名為c的一個相同列名，則必須在SELECT和ON子句中使用表限定符，如使用t1.c或t2.c指定參照是那個表中的c列。

為了節省書寫表限定符的時間，可以在查詢中使用表別名。 例如，可以長名稱verylonglonglong_tablename表使用表別名，並使用t.column參照其列，而不是使用verylonglonglong_tablename.column，但是如果喜歡書寫或使用這麼長的表名稱，那麼也應該允許你的開發夥伴罵你幾句類似：傻逼~等這樣的話！

MySQL INNER JOIN範例

下面來看看範例資料庫(yiibaidb)中的產品(products)和產品線(productlines)表。它們的 ER 圖如下所示 -

在上面圖中，products表中的productLine列參考參照productlines表的productline列。 products表中的productLine列稱為外來鍵列。

通常，連線具有外來鍵關係的表，如產品線(productlines)和產品(products)表。現在，如果想獲取以下資料 -

  • 獲取products表中的productCode和productName列的值。
  • 獲取productlines表產品線的描述 - textDescription列的值。

為此，需要通過使用INNER JOIN子句根據productline列匹配行來從兩個表中查詢選擇資料，如下所示：

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products t1
        INNER JOIN
    productlines t2 ON t1.productline = t2.productline;

執行上面查詢，得到下面的結果(部分)-

由於兩個表的連線列是使用相同一個列：productline，因此可以使用以下語法：

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products
        INNER JOIN
    productlines USING (productline);

上面語句返回相同的結果集，但是使用此語法，不必使用表的別名。

MySQL INNER JOIN GROUP BY子句

請參閱以下訂單和訂單詳細表，orders表和orderdetails表的結構如下所示 -

可以使用具有GROUP BY子句的INNER JOIN子句從orders和orderdetails表中獲取訂單號，訂單狀態和總銷售額，如下所示：

SELECT 
    T1.orderNumber,
    status,
    SUM(quantityOrdered * priceEach) total
FROM
    orders AS T1
        INNER JOIN
    orderdetails AS T2 ON T1.orderNumber = T2.orderNumber
GROUP BY orderNumber;

執行上面查詢，結果如下所示(部分) -

類似地，以下語句查詢與上述得到結果相同：

SELECT 
    orderNumber,
    status,
    SUM(quantityOrdered * priceEach) total
FROM
    orders
        INNER JOIN
    orderdetails USING (orderNumber)
GROUP BY orderNumber;

MySQL INNER JOIN使用等於以外的運算子

到目前為止，您已經看到連線謂詞使用相等的運算子(=)來匹配行。但是也可以使用大於(>)，小於(<)和不等於(<>)運算子的其他運算子來形成連線謂詞。

以下查詢使用少於(<)連線來查詢低於程式碼為S10_1678的產品的銷售價格的製造商建議零售價(MSRP)的所有產品。

SELECT 
    orderNumber, 
    productName, 
    msrp, 
    priceEach
FROM
    products p
        INNER JOIN
    orderdetails o ON p.productcode = o.productcode
        AND p.msrp > o.priceEach
WHERE
    p.productcode = 'S10_1678';

執行上面查詢語句，得到以下輸出結果 - 

mysql> SELECT 
    orderNumber, 
    productName, 
    msrp, 
    priceEach
FROM
    products p
        INNER JOIN
    orderdetails o ON p.productcode = o.productcode
        AND p.msrp > o.priceEach
WHERE
    p.productcode = 'S10_1678';
+-------------+---------------------------------------+------+-----------+
| orderNumber | productName                           | msrp | priceEach |
+-------------+---------------------------------------+------+-----------+
|       10107 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 81.35     |
|       10121 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 86.13     |
|       10134 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 90.92     |
|       10145 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 76.56     |
|       10159 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 81.35     |
|       10168 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 94.74     |
|       10399 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 77.52     |
|       10403 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 85.17     |
... ...
|       10417 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 79.43     |
+-------------+---------------------------------------+------+-----------+
26 rows in set

在本教學中，您已經學會了如何使用MySQL INNER JOIN來查詢來自多個表中的資料。