加载中...

MySQL內連線(INNER JOIN)


在本教學中,您將學習如何使用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子句連線兩個表:t1t2,我們來簡化上面的語法。

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition;

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

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

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

在MySQL INNER JOIN中避免列錯誤

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

例如,如果t1t2表都具有名為c的一個相同列名,則必須在SELECTON子句中使用表限定符,如使用t1.ct2.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表中的productCodeproductName列的值。
  • 獲取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子句從ordersorderdetails表中獲取訂單號,訂單狀態和總銷售額,如下所示:

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來查詢來自多個表中的資料。