MySQL左連線(LEFT JOIN)

2019-10-16 22:58:03

在本教學中,您將了解MySQL LEFT JOIN子句以及如何將其應用於從兩個或多個資料庫表查詢資料。

1. MySQL LEFT JOIN簡介

MySQL LEFT JOIN子句允許您從兩個或多個資料庫表查詢資料。LEFT JOIN子句是SELECT語句的可選部分,出現在FROM子句之後。

我們假設要從兩個表t1t2查詢資料。以下語句說明了連線兩個表的LEFT JOIN子句的語法:

SELECT 
    t1.c1, t1.c2, t2.c1, t2.c2
FROM
    t1
        LEFT JOIN
    t2 ON t1.c1 = t2.c1;

當使用LEFT JOIN子句將t1表加入t2表時,如果來自左表t1的行與基於連線條件(t1.c1 = t2.c1)的右表t2匹配,則該行將被包含在結果集中。

如果左表中的行與右表中的行不匹配,則還將選擇左表中的行並與右表中的「」行組合。「」行對於SELECT子句中的所有相應列都包含NULL值。

換句話說,LEFT JOIN子句允許您從匹配的左右表中查詢選擇行記錄,連線左表(t1)中的所有行,即使在右表(t2)中找不到匹配的行也顯示出來,但使用NULL值代替。

下圖可幫助您視覺化LEFT JOIN子句的工作原理。 兩個圓圈之間的交點是兩個表中匹配的行,左圓的剩餘部分(白色部分)是t1表中不存在t2表中任何匹配行的行。 因此,左表中的所有行都包含在結果集中。

請注意,如果這些子句在查詢中可用,返回的行也必須與WHEREHAVING子句中的條件相匹配。

2. MySQL LEFT JOIN範例

2.1 使用MySQL LEFT JOIN子句來連線兩個表

我們來看看在範例資料庫(yiibaidb)中的兩個表:訂單表和客戶表,兩個表的 ER 圖如下所示 -

在上面的資料庫圖中:

  • 訂單(orders)表中的每個訂單必須屬於客戶(customers)表中的客戶。
  • 客戶(customers)表中的每個客戶在訂單(orders)表中可以有零個或多個訂單。

要查詢每個客戶的所有訂單,可以使用LEFT JOIN子句,如下所示:

SELECT
 c.customerNumber,
 c.customerName,
 orderNumber,
 o.status
FROM
 customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber;

執行上面查詢語句,得到以下結果(部分) -

mysql> SELECT
 c.customerNumber,
 c.customerName,
 orderNumber,
 o.status
FROM
 customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber;
+----------------+------------------------------------+-------------+------------+
| customerNumber | customerName                       | orderNumber | status     |
+----------------+------------------------------------+-------------+------------+
|            103 | Atelier graphique                  |       10123 | Shipped    |
|            103 | Atelier graphique                  |       10298 | Shipped   |
... 省略部分 ...
|            477 | Mit Vergngen & Co.                 | NULL        | NULL       |
|            480 | Kremlin Collectables, Co.          | NULL        | NULL       |
|            481 | Raanan Stores, Inc                 | NULL        | NULL       |
|            484 | Iberia Gift Imports, Corp.         |       10184 | Shipped    |
|            484 | Iberia Gift Imports, Corp.         |       10303 | Shipped    |
|            486 | Motor Mint Distributors Inc.       |       10109 | Shipped    |
|            486 | Motor Mint Distributors Inc.       |       10236 | Shipped  |
+----------------+------------------------------------+-------------+------------+
350 rows in set

左表是customers表,因此,所有客戶都包含在結果集中。 但是,結果集中有一些行具有客戶資料,但沒有訂單資料。如:customerNumber列值為:477480等。這些行中的訂單資料為NULL。也就是說這些客戶在orders表中沒有任何訂單(未購買過任何產品)。

因為我們使用相同的列名(orderNumber)來連線兩個表,所以可以使用以下語法使查詢更短:

SELECT
 c.customerNumber,
 customerName,
 orderNumber,
 status
FROM
 customers c
LEFT JOIN orders USING (customerNumber);

在上面查詢語句中,下面的子句 -

USING (customerNumber)

相當於 -

ON c.customerNumber = o.customerNumber

如果使用INNER JOIN子句替換LEFT JOIN子句,則只能獲得至少有下過一個訂單的客戶。

2.2 使用MySQL LEFT JOIN子句來查詢不匹配的行

當您想要找到右表中與不匹配的左表中的行時,LEFT JOIN子句非常有用。要查詢兩個表之間的不匹配行,可以向SELECT語句新增一個WHERE子句,以僅查詢右表中的列值包含NULL值的行。

例如,要查詢沒有下過訂單的所有客戶,請使用以下查詢:

SELECT 
    c.customerNumber, 
    c.customerName, 
    orderNumber, 
    o.status
FROM
    customers c
        LEFT JOIN
    orders o ON c.customerNumber = o.customerNumber
WHERE
    orderNumber IS NULL;

執行上面查詢語句,得到以下結果 -

mysql> SELECT 
    c.customerNumber, 
    c.customerName, 
    orderNumber, 
    o.status
FROM
    customers c
        LEFT JOIN
    orders o ON c.customerNumber = o.customerNumber
WHERE
    orderNumber IS NULL;
+----------------+--------------------------------+-------------+--------+
| customerNumber | customerName                   | orderNumber | status |
+----------------+--------------------------------+-------------+--------+
|            125 | Havel & Zbyszek Co             | NULL        | NULL   |
|            168 | American Souvenirs Inc         | NULL        | NULL   |
|            169 | Porto Imports Co.              | NULL        | NULL   |
|            206 | Asian Shopping Network, Co     | NULL        | NULL   |
|            223 | Natrlich Autos                 | NULL        | NULL   |
|            237 | ANG Resellers                  | NULL        | NULL   |
|            247 | Messner Shopping Network       | NULL        | NULL   |
|            273 | Franken Gifts, Co              | NULL        | NULL   |
|            293 | BG&E Collectables              | NULL        | NULL   |
|            303 | Schuyler Imports               | NULL        | NULL   |
|            307 | Der Hund Imports               | NULL        | NULL   |
|            335 | Cramer Spezialitten, Ltd       | NULL        | NULL   |
|            348 | Asian Treasures, Inc.          | NULL        | NULL   |
|            356 | SAR Distributors, Co           | NULL        | NULL   |
|            361 | Kommission Auto                | NULL        | NULL   |
|            369 | Lisboa Souveniers, Inc         | NULL        | NULL   |
|            376 | Precious Collectables          | NULL        | NULL   |
|            409 | Stuttgart Collectable Exchange | NULL        | NULL   |
|            443 | Feuer Online Stores, Inc       | NULL        | NULL   |
|            459 | Warburg Exchange               | NULL        | NULL   |
|            465 | Anton Designs, Ltd.            | NULL        | NULL   |
|            477 | Mit Vergngen & Co.             | NULL        | NULL   |
|            480 | Kremlin Collectables, Co.      | NULL        | NULL   |
|            481 | Raanan Stores, Inc             | NULL        | NULL   |
+----------------+--------------------------------+-------------+--------+
24 rows in set

3. WHERE子句與ON子句中的條件

請參見以下範例。

SELECT 
    o.orderNumber, 
    customerNumber, 
    productCode
FROM
    orders o
        LEFT JOIN
    orderDetails USING (orderNumber)
WHERE
    orderNumber = 10123;

在本範例中,我們使用LEFT JOIN子句來查詢orders表和orderDetails表中的資料。 該查詢返回訂單號為10123的訂單及其購買產品明細資訊(如果有的話)。

mysql> SELECT 
    o.orderNumber, 
    customerNumber, 
    productCode
FROM
    orders o
        LEFT JOIN
    orderDetails USING (orderNumber)
WHERE
    orderNumber = 10123;
+-------------+----------------+-------------+
| orderNumber | customerNumber | productCode |
+-------------+----------------+-------------+
|       10123 |            103 | S18_1589    |
|       10123 |            103 | S18_2870    |
|       10123 |            103 | S18_3685    |
|       10123 |            103 | S24_1628    |
+-------------+----------------+-------------+
4 rows in set

但是,如果將條件從WHERE子句移動到ON子句:

SELECT 
    o.orderNumber, 
    customerNumber, 
    productCode
FROM
    orders o
        LEFT JOIN
    orderDetails d ON o.orderNumber = d.orderNumber
        AND o.orderNumber = 10123;

想想上面程式碼將會輸出什麼結果 -

mysql> SELECT 
    o.orderNumber, 
    customerNumber, 
    productCode
FROM
    orders o
        LEFT JOIN
    orderDetails d ON o.orderNumber = d.orderNumber
        AND o.orderNumber = 10123;
+-------------+----------------+-------------+
| orderNumber | customerNumber | productCode |
+-------------+----------------+-------------+
|       10123 |            103 | S18_1589    |
|       10123 |            103 | S18_2870    |
|       10123 |            103 | S18_3685    |
|       10123 |            103 | S24_1628    |
|       10298 |            103 | NULL        |
|       10345 |            103 | NULL        |
|       10124 |            112 | NULL        |
.... .... 
|       10179 |            496 | NULL        |
|       10360 |            496 | NULL        |
|       10399 |            496 | NULL        |
+-------------+----------------+-------------+
329 rows in set

請注意,對於INNER JOIN子句,ON子句中的條件等同於WHERE子句中的條件。

在本教學中,我們解釋了MySQL LEFT JOIN子句,並向您展示了如何將使用它來從多個資料庫表中查詢資料。