Like Share Discussion Bookmark Smile

J.J. Huang   2020-07-06   MySQL   瀏覽次數:

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表中的供應商)。這就是維護引用完整性,它是通過在表的定義中指定主鍵和外鍵來實現的。

建立聯結

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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) 由沒有聯結條件的表關係返回的結果為笛卡兒積。檢索出的行的數目將是第一個表中的行數乘以第二個表中的行數。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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),它基於兩個表之間的相等測試。這種聯結也稱為內部聯結。其實,對於這種聯結可以使用稍微不同的語法來明確指定聯結的類型。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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語句中可以聯結的表的數目沒有限制。建立聯結的基本規則也相同。首先列出所有表,然後定義表之間的關係。

1
2
3
4
5
6
7
8
9
10
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在運行時關聯指定的每個表以處理聯結。這種處理可能是非常耗費資源的,因此應該仔細,不要聯結不必要的表。聯結的表越多,性能下降越厲害。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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'));
1
2
3
4
5
6
7
8
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