MySQL - 第十五章 | 聯結表

聯結

SQL最強大的功能之一就是能在資料檢索查詢的執行中聯結(join)表。聯結是利用SQL的SELECT能執行的最重要的操作，很好地理解聯結及其語法是學習SQL的一個極為重要的組成部分。
在能夠有效地使用聯結前，必須瞭解關係表以及關係資料庫設計的一些基礎知識。

關係表

理解關係表的最好方法是來看一個現實世界中的例子。
假如有一個包含產品目錄的資料庫表，其中每種類別的物品佔一行。對於每種物品要存儲的訊息包括產品描述和價格，以及生產該產品的供應商訊息。
現在，假如有由同一供應商生產的多種物品，那麼在何處存儲供應商訊息(如，供應商名、地址、聯繫方法等)呢?將這些資料與產品訊息分開存儲的理由如下。

因為同一供應商生產的每個產品的供應商訊息都是相同的，對每個產品重復此訊息既浪費時間又浪費存儲空間。
如果供應商訊息改變(例如，供應商搬家或電話號碼變動)，只需改動一次即可。
如果有重複資料(即每種產品都存儲供應商訊息)，很難保證每次輸入該資料的方式都相同。不一致的資料在報表中很難利用。

關鍵是，相同資料出現多次決不是一件好事，此因素是關係資料庫設計的基礎。關係表的設計就是要保證把訊息分解成多個表，一類資料一個表。各表通過某些常用的值(即關係設計中的關係(relational))互相關聯。
在這個例子中，可建立兩個表，一個存儲供應商訊息，另一個存儲產品訊息。vendors表包含所有供應商訊息，每個供應商佔一行，每個供應商具有唯一的標識。此標識稱為主鍵(primary key)，可以是供應商ID或任何其他唯一值。
products表只存儲產品訊息，它除了存儲供應商ID(vendors表的主鍵)外不存儲其他供應商訊息。vendors表的主鍵又叫作products的外鍵，它將vendors表與products表關聯，利用供應商ID能從vendors表中找出相應供應商的詳細訊息。

術語：外鍵(foreignkey) 外鍵為某個表中的一列，它包含另一個表的主鍵值，定義了兩個表之間的關係。

好處如下:

供應商訊息不重復，從而不浪費時間和空間;
如果供應商訊息變動，可以只更新vendors表中的單個記錄，相關表中的資料不用改動;
由於資料無重復，顯然資料是一致的，這使得處理資料更簡單。

總之，關係資料可以有效地存儲和方便地處理。因此，關係資料庫的可伸縮性遠比非關係資料庫要好。

術語：可伸縮性(scale) 能夠適應不斷增加的工作量而不失敗。設計良好的資料庫或應用程序稱之為可伸縮性好(scale well)。

為什麼要使用聯結

正如所述，分解資料為多個表能更有效地存儲，更方便地處理，並且具有更大的可伸縮性。但這些好處是有代價的。
如果資料存儲在多個表中，怎樣用單條SELECT語句檢索出資料?
答案是使用聯結。簡單地說，聯結是一種機制，用來在一條SELECT語句中關聯表，因此稱之為聯結。使用特殊的語法，可以聯結多個表返回一組輸出，聯結在運行時關聯表中正確的行。

說明：維護引用完整性重要的是，要理解聯結不是物理實體。換句話說，它在實際的資料庫表中不存在。聯結由MySQL根據需要建立，它存在於查詢的執行當中。
在使用關係表時，僅在關係列中插入合法的資料非常重要。回到這裡的例子，如果在products表中插入擁有非法供應商ID(即沒有在vendors表中出現)的供應商生產的產品，則這些產品是不可訪問的，因為它們沒有關聯到某個供應商。
為防止這種情況發生，可指示MySQL只允許在products表的供應商ID列中出現合法值(即出現在vendors表中的供應商)。這就是維護引用完整性，它是通過在表的定義中指定主鍵和外鍵來實現的。

建立聯結

聯結的建立非常簡單，規定要聯結的所有表以及它們如何關聯即可。

mysql> SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id=products.vend_id ORDER BY vend_name,prod_name;
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)

我們來考察一下此程式碼。SELECT語句與前面所有語句一樣指定要檢索的列。這裡，最大的差別是所指定的兩個列(prod_name和prod_price)在一個表中，而另一個列(vend_name)在另一個表中。
現在來看FROM子句。與以前的SELECT語句不一樣，這條語句的FROM子句列出了兩個表，分別是vendors和products。它們就是這條SELECT語句聯結的兩個表的名字。這兩個表用WHERE子句正確聯結，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。
可以看到要匹配的兩個列以vendors.vend_id和products.vend_id指定。這裡需要這種完全限定列名，因為如果只給出vend_id，則MySQL不知道指的是哪一個(它們有兩個，每個表中一個)。

注意：完全限定列名在引用的列可能出現二義性時，必須使用完全限定列名(用一個點分隔的表名和列名)。如果引用一個沒有用表名限制的具有二義性的列名，MySQL將返回錯誤。

WHERE子句的重要性

利用WHERE子句建立聯結關係似乎有點奇怪，但實際上，有一個很充分的理由。請記住，在一條SELECT語句中聯結幾個表時，相應的關係是在運行中構造的。在資料庫表的定義中不存在能指示MySQL如何對表進行聯結的東西。你必須自己做這件事情。在聯結兩個表時，你實際上做的是將第一個表中的每一行與第二個表中的每一行配對。WHERE子句作為過濾條件，它只包含那些匹配給定條件(這裡是聯結條件)的行。沒有WHERE子句，第一個表中的每個行將與第二個表中的每個行配對，而不管它們邏輯上是否可以配在一起。

術語：笛卡兒積(cartesian product) 由沒有聯結條件的表關係返回的結果為笛卡兒積。檢索出的行的數目將是第一個表中的行數乘以第二個表中的行數。

mysql> SELECT vend_name,prod_name,prod_price FROM vendors,products ORDER BY vend_name,prod_name;
+----------------+----------------+------------+
| vend_name      | prod_name      | prod_price |
+----------------+----------------+------------+
| ACME           | .5 ton anvil   |       5.99 |
| ACME           | 1 ton anvil    |       9.99 |
| ACME           | 2 ton anvil    |      14.99 |
| ACME           | Bird seed      |      10.00 |
| ACME           | Carrots        |       2.50 |
| ACME           | Detonator      |      13.00 |
| ACME           | Fuses          |       3.42 |
| ACME           | JetPack 1000   |      35.00 |
| ACME           | JetPack 2000   |      55.00 |
| ACME           | Oil can        |       8.99 |
| ACME           | Safe           |      50.00 |
| ACME           | Sling          |       4.49 |
| ACME           | TNT (1 stick)  |       2.50 |
| ACME           | TNT (5 sticks) |      10.00 |
| Anvils R Us    | .5 ton anvil   |       5.99 |
| Anvils R Us    | 1 ton anvil    |       9.99 |
| Anvils R Us    | 2 ton anvil    |      14.99 |
| Anvils R Us    | Bird seed      |      10.00 |
| Anvils R Us    | Carrots        |       2.50 |
| Anvils R Us    | Detonator      |      13.00 |
| Anvils R Us    | Fuses          |       3.42 |
| Anvils R Us    | JetPack 1000   |      35.00 |
| Anvils R Us    | JetPack 2000   |      55.00 |
| Anvils R Us    | Oil can        |       8.99 |
| Anvils R Us    | Safe           |      50.00 |
| Anvils R Us    | Sling          |       4.49 |
| Anvils R Us    | TNT (1 stick)  |       2.50 |
| Anvils R Us    | TNT (5 sticks) |      10.00 |
| Furball Inc.   | .5 ton anvil   |       5.99 |
| Furball Inc.   | 1 ton anvil    |       9.99 |
| Furball Inc.   | 2 ton anvil    |      14.99 |
| Furball Inc.   | Bird seed      |      10.00 |
| Furball Inc.   | Carrots        |       2.50 |
| Furball Inc.   | Detonator      |      13.00 |
| Furball Inc.   | Fuses          |       3.42 |
| Furball Inc.   | JetPack 1000   |      35.00 |
| Furball Inc.   | JetPack 2000   |      55.00 |
| Furball Inc.   | Oil can        |       8.99 |
| Furball Inc.   | Safe           |      50.00 |
| Furball Inc.   | Sling          |       4.49 |
| Furball Inc.   | TNT (1 stick)  |       2.50 |
| Furball Inc.   | TNT (5 sticks) |      10.00 |
| Jet Set        | .5 ton anvil   |       5.99 |
| Jet Set        | 1 ton anvil    |       9.99 |
| Jet Set        | 2 ton anvil    |      14.99 |
| Jet Set        | Bird seed      |      10.00 |
| Jet Set        | Carrots        |       2.50 |
| Jet Set        | Detonator      |      13.00 |
| Jet Set        | Fuses          |       3.42 |
| Jet Set        | JetPack 1000   |      35.00 |
| Jet Set        | JetPack 2000   |      55.00 |
| Jet Set        | Oil can        |       8.99 |
| Jet Set        | Safe           |      50.00 |
| Jet Set        | Sling          |       4.49 |
| Jet Set        | TNT (1 stick)  |       2.50 |
| Jet Set        | TNT (5 sticks) |      10.00 |
| Jouets Et Ours | .5 ton anvil   |       5.99 |
| Jouets Et Ours | 1 ton anvil    |       9.99 |
| Jouets Et Ours | 2 ton anvil    |      14.99 |
| Jouets Et Ours | Bird seed      |      10.00 |
| Jouets Et Ours | Carrots        |       2.50 |
| Jouets Et Ours | Detonator      |      13.00 |
| Jouets Et Ours | Fuses          |       3.42 |
| Jouets Et Ours | JetPack 1000   |      35.00 |
| Jouets Et Ours | JetPack 2000   |      55.00 |
| Jouets Et Ours | Oil can        |       8.99 |
| Jouets Et Ours | Safe           |      50.00 |
| Jouets Et Ours | Sling          |       4.49 |
| Jouets Et Ours | TNT (1 stick)  |       2.50 |
| Jouets Et Ours | TNT (5 sticks) |      10.00 |
| LT Supplies    | .5 ton anvil   |       5.99 |
| LT Supplies    | 1 ton anvil    |       9.99 |
| LT Supplies    | 2 ton anvil    |      14.99 |
| LT Supplies    | Bird seed      |      10.00 |
| LT Supplies    | Carrots        |       2.50 |
| LT Supplies    | Detonator      |      13.00 |
| LT Supplies    | Fuses          |       3.42 |
| LT Supplies    | JetPack 1000   |      35.00 |
| LT Supplies    | JetPack 2000   |      55.00 |
| LT Supplies    | Oil can        |       8.99 |
| LT Supplies    | Safe           |      50.00 |
| LT Supplies    | Sling          |       4.49 |
| LT Supplies    | TNT (1 stick)  |       2.50 |
| LT Supplies    | TNT (5 sticks) |      10.00 |
+----------------+----------------+------------+
84 rows in set (0.00 sec)

分析：從上面的輸出中可以看到，相應的笛卡兒積不是我們所想要的。這裡返回的資料用每個供應商匹配了每個產品，它包括了供應商不正確的產品。實際上有的供應商根本就沒有產品。

注意：不要忘了WHERE子句應該保證所有聯結都有WHERE子句，否則MySQL將返回比想要的資料多得多的資料。同理，應該保證WHERE子句的正確性。不正確的過濾條件將導致MySQL返回不正確的資料。

提示：叉聯結有時我們會聽到返回稱為叉聯結(crossjoin)的笛卡兒積的聯結類型。

內部聯結

目前為止所用的聯結稱為等值聯結(equijoin)，它基於兩個表之間的相等測試。這種聯結也稱為內部聯結。其實，對於這種聯結可以使用稍微不同的語法來明確指定聯結的類型。

mysql> SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id ORDER BY vend_name,prod_name;
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)

分析：此語句中的SELECT與前面的SELECT語句相同，但FROM子句不同。這裡，兩個表之間的關係是FROM子句的組成部分，以INNER JOIN指定。在使用這種語法時，聯結條件用特定的ON子句而不是WHERE子句給出。傳遞給ON的實際條件與傳遞給WHERE的相同。

說明：使用哪種語法 ANSI SQL規範首選INNER JOIN語法。此外，儘管使用WHERE子句定義聯結的確比較簡單，但是使用明確的聯結語法能夠確保不會忘記聯結條件，有時候這樣做也能影響性能。

聯結多個表

SQL對一條SELECT語句中可以聯結的表的數目沒有限制。建立聯結的基本規則也相同。首先列出所有表，然後定義表之間的關係。

mysql> SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;
+----------------+-------------+------------+----------+
| prod_name      | vend_name   | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil   | Anvils R Us |       5.99 |       10 |
| 1 ton anvil    | Anvils R Us |       9.99 |        3 |
| TNT (5 sticks) | ACME        |      10.00 |        5 |
| Bird seed      | ACME        |      10.00 |        1 |
+----------------+-------------+------------+----------+
4 rows in set (0.00 sec)

分析：此例子顯示編號為20005的訂單中的物品。訂單物品存儲在orderitems表中。每個產品按其產品ID存儲，它引用products表中的產品。這些產品通過供應商ID聯結到vendors表中相應的供應商，供應商ID存儲在每個產品的記錄中。這裡的FROM子句列出了3個表，而WHERE子句定義了這兩個聯結條件，而第三個聯結條件用來過濾出訂單20005中的物品。

注意：性能考慮 MySQL在運行時關聯指定的每個表以處理聯結。這種處理可能是非常耗費資源的，因此應該仔細，不要聯結不必要的表。聯結的表越多，性能下降越厲害。

修改前一章節中使用子查詢的語法：

SELECT
	cust_name,
	cust_contact
FROM
	customers
WHERE
	cust_id IN(
		SELECT
			cust_id FROM orders
		WHERE
			order_num IN(
				SELECT
					order_num FROM orderitems
				WHERE
					prod_id = 'TNT2'));

mysql> SELECT cust_name,cust_contact FROM customers,orders,orderitems WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num AND prod_id='TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

分析：這個查詢中返回資料需要使用3個表。但這裡我們沒有在嵌套子查詢中使用它們，而是使用了兩個聯結。這裡有3個WHERE子句條件。前兩個關聯聯結中的表，後一個過濾產品TNT2的資料。

提示：多做實驗正如所見，為執行任一給定的SQL操作，一般存在不止一種方法。很少有絕對正確或絕對錯誤的方法。性能可能會受操作類型、表中資料量、是否存在索引或鍵以及其他一些條件的影響。因此，有必要對不同的選擇機制進行實驗，以找出最適合具體情況的方法。

結語

聯結這個觀念，我覺得在整個SQL裡面是佔了非常大的比重，因為要設計資料庫來儲存資料，基本上就是希望可以好好規劃和分類資料；但是又希望在查詢的時候，可以一次將所需資訊查出。這就是聯結的使用。不管是在專案還是自己學習過程中，都會常常碰到這類問題。

這篇文章中只是先簡單說明表的聯結使用。後面的章節我已經先看了。會有更多的JOIN做使用，一定要將其觀念弄清楚，不然在未來開發很容易發生非預期的Bug。

註：以上參考了
MySQL必知必会 MySQL Crash Course

MySQL - 第十四章 | 使用子查詢

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

↑
If you enjoy the article, please feel free to donate~ Thx.
若本文對您有幫助，您也願意支持打賞，謝謝您的鼓勵。

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

J.J.'s Blogs

J.J. Huang 2020-07-06 MySQL 瀏覽次數：次 {{moment(1594006800000).fromNow()}}