歡迎！您似乎正在使用廣告攔截器。請考慮通過禁用您的廣告攔截器來支持我。

J.J.'s Blogs

Like Share Discussion Bookmark Smile

J.J. Huang   2020-07-07   MySQL   瀏覽次數：次   DMCA.com Protection Status {{moment(1594090800000).fromNow()}}

MySQL - 第十六章 | 高級聯結表

使用表別名

前面有介紹了如何使用別名引用被檢索的表列。給列起別名的語法如下:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT Concat(RTrim(vend_name), ' (', RTrim(Vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.00 sec)

別名除了用於列名和計算欄位外，SQL還允許給表名起別名。這樣做有兩個主要理由:

  • 縮短SQL語句;
  • 允許在單條SELECT語句中多次使用相同的表。
1
2
3
4
5
6
7
8
mysql> SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num AND prod_id='TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.01 sec)

分析：可以看到，FROM子句中3個表全都具有別名。customers AS c 建立c作為customers的別名，等等。這使得能使用省寫的c而不是全名customers。在此例子中，表別名只用於WHERE子句。但是，表別名不僅能用於WHERE子句，它還可以用於SELECT的列表、ORDER BY子句以及語句的其他部分。
應該注意，表別名只在查詢執行中使用。與列別名不一樣，表別名不返回到客戶端。

使用不同類型的聯結

迄今為止，我們使用的只是稱為內部聯結或等值聯結(equijoin)的簡單聯結。現在來看其他聯結，它們分別是自聯結、自然聯結和外部聯結。

自聯結

如前所述，使用表別名的主要原因之一是能在單條SELECT語句中不止一次引用相同的表。下面舉一個例子。

假如你發現某物品(其ID為DTNTR)存在問題，因此想知道生產該物 品的供應商生產的其他物品是否也存在這些問題。此查詢要求首先找到生產ID為DTNTR的物品的供應商，然後找出這個供應商生產的其他物品。

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.01 sec)

分析：這是第一種解決方案，它使用了子查詢。內部的SELECT語句做了一個簡單的檢索，返回生產ID為DTNTR的物品供應商的vend_id。該ID用於外部查詢的WHERE子句中，以便檢索出這個供應商生產的所有物品。

使用聯結的相同查詢。

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.00 sec)

分析：此查詢中需要的兩個表實際上是相同的表，因此products表在FROM子句中出現了兩次。雖然這是完全合法的，但對products的引用具有二義性，因為MySQL不知道你引用的是products表中的哪個實例。
為解決此問題，使用了表別名。products的第一次出現為別名p1，第二次出現為別名p2。現在可以將這些別名用作表名。例如，SELECT語句使用p1前綴明確地給出所需列的全名。如果不這樣，MySQL將返回錯誤，因為分別存在兩個名為prod_id、prod_name的列。MySQL不知道想要的是哪一個列(即使它們事實上是同一個列)。WHERE(通過匹配p1中的vend_id和p2中的vend_id)首先聯結兩個表，然後按第二個表中的prod_id過濾資料，返回所需的資料。

提示：用自聯結而不用子查詢 自聯結通常作為外部語句用來替代從相同表中檢索資料時使用的子查詢語句。雖然最終的結果是相同的，但有時候處理聯結遠比處理子查詢快得多。應該試一下兩種方法，以確定哪一種的性能更好。

自然聯結

無論何時對表進行聯結，應該至少有一個列出現在不止一個表中(被聯結的列)。標準的聯結(前一章中介紹的內部聯結)返回所有資料，甚至相同的列多次出現。自然聯結排除多次出現，使每個列只返回一次。

怎樣完成這項工作呢?答案是，系統不完成這項工作，由你自己完成它。自然聯結是這樣一種聯結，其中你只能選擇那些唯一的列。這一般是通過對表使用萬用字元(SELECT *)，對所有其他表的列使用明確的子集來完成的。

1
2
3
4
5
6
7
8
mysql> SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB';
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email      | order_num | order_date          | prod_id | quantity | item_price |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20005 | 2005-09-01 00:00:00 | FB      |        1 |      10.00 |
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20009 | 2005-10-08 00:00:00 | FB      |        1 |      10.00 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
2 rows in set (0.01 sec)

分析：在這個例子中，萬用字元只對第一個表使用。所有其他列明確列出，所以沒有重復的列被檢索出來。

事實上，迄今為止我們建立的每個內部聯結都是自然聯結，很可能我們永遠都不會用到不是自然聯結的內部聯結。

外部聯結

許多聯結將一個表中的行與另一個表中的行相關聯。但有時候會需要包含沒有關聯行的那些行。例如，可能需要使用聯結來完成以下工作:

  • 對每個客戶下了多少訂單進行計數，包括那些至今尚未下訂單的客戶;
  • 列出所有產品以及訂購數量，包括沒有人訂購的產品;
  • 計算平均銷售規模，包括那些至今尚未下訂單的客戶。

在上述例子中，聯結包含了那些在相關表中沒有關聯行的行。這種類型的聯結稱為外部聯結。

內部聯結：

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT customers.cust_id, orders.order_num FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.01 sec)

外部聯結語法類似。為了檢索所有客戶，包括那些沒有訂單的客戶：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.00 sec)

分析：類似於上一章中所看到的內部聯結，這條SELECT語句使用了關鍵字OUTER JOIN來指定聯結的類型(而不是在WHERE子句中指定)。但是，與內部聯結關聯兩個表中的行不同的是，外部聯結還包括沒有關聯行的行。在使用OUTER JOIN語法時，必須使用RIGHT或LEFT關鍵字指定包括其所有行的表(RIGHT指出的是OUTER JOIN右邊的表，而LEFT指出的是OUTER JOIN左邊的表)。上面的例子使用LEFT OUTER JOIN從FROM子句的左邊表(customers表)中選擇所有行。為了從右邊的表中選擇所有行，應該使用RIGHT OUTER JOIN。

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.00 sec)

說明：沒有=操作符 MySQL不支持簡化字符=和=*的使用，這兩種操作符在其他DBMS中是很流行的。

提示：外部聯結的類型 存在兩種基本的外部聯結形式:左外部聯結和右外部聯結。它們之間的唯一差別是所關聯的表的順序不同。換句話說，左外部聯結可通過顛倒FROM或WHERE子句中表的順序轉換為右外部聯結。因此，兩種類型的外部聯結可互換使用，而究竟使用哪一種純粹是根據方便而定。

使用帶聚集函數的聯結

聚集函數用來匯總資料。雖然至今為止聚集函數的所有例子只是從單個表匯總資料，但這些函數也可以與聯結一起使用。
為說明這一點，請看一個例子。如果要檢索所有客戶及每個客戶所下的訂單數，下面使用了COUNT()函數的代碼可完成。

1
2
3
4
5
6
7
8
9
10
mysql> SELECT c.cust_name, c.cust_id, COUNT(o.order_num) AS num_ord FROM customers AS c INNER JOIN orders AS o ON c.cust_id = o.cust_id GROUP BY c.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
4 rows in set (0.02 sec)

分析：此SELECT語句使用INNER JOIN將customers和orders表互相關聯。GROUP BY子句按客戶分組資料，因此，函數調用COUNT(orders.order_num)對每個客戶的訂單計數，將它作為num_ord返回。

聚集函數也可以方便地與其他聯結一起使用。

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT c.cust_name, c.cust_id, COUNT(o.order_num) AS num_ord FROM customers AS c LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id GROUP BY c.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Mouse House    |   10002 |       0 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
5 rows in set (0.01 sec)

分析：這個例子使用左外部聯結來包含所有客戶，甚至包含那些沒有任何下訂單的客戶。結果顯示也包含了客戶Mouse House，它有0個訂單。

使用聯結和聯結條件

在總結關於聯結的這兩章前，有必要匯總一下關於聯結及其使用的某些要點。

  • 注意所使用的聯結類型。一般我們使用內部聯結，但使用外部聯結也是有效的。
  • 保證使用正確的聯結條件，否則將返回不正確的資料。
  • 應該總是提供聯結條件，否則會得出笛卡兒積。
  • 在一個聯結中可以包含多個表，甚至對於每個聯結可以採用不同的聯結類型。雖然這樣做是合法的，一般也很有用，但應該在一起測試它們前，分別測試每個聯結。這將使故障排除更為簡單。

結語

我相信到這邊已經被多種JOIN語法給搞亂了吧。其實我最常使用的就是INNER JOIN和LEFT JOIN；這兩個基本上就已經將大多數的業務邏輯需求給解決掉了。

但是！絕對不是這樣就足夠了，建議還是把所有的JOIN語法稍微都熟悉一下，有些時候可以很方便解決需求。

這邊提供一張圖片給予參考，可以快速知道其JOIN後的結果。

註：以上參考了
MySQL必知必会 MySQL Crash Course
[stackoverflow]https://stackoverflow.com/questions/30358982/sql-server-replaces-left-join-for-left-outer-join-in-view-query

本文由J.J. Huang 創作，採用CC BY 3.0 TW協議 進行許可。可自由轉載、引用，但需署名作者且註明文章出處。