學習SQL【9】-集合與聯結

05-10

現在我們開始學習使用2張以上的表的SQL語句。通過以行方向為單位的集合運算符和以列方向為單位的聯結，就可以將分散在多張表中的數據組合成期望的結果。

表的加減法

什麼是集合運算

集合在資料庫中表示為記錄的集合。具體來說，表、視圖和查詢的執行結果都是記錄的集合。

集合運算就是對滿足同一規則的記錄進行的加減等四則運算。

用來進行集合運算的運算符稱為集合運算符。

表的加法—UNION

UNION（並集）是進行記錄加法運算的集合運算符。在學習使用方法之前，我們先創建一張表：

--創建表Product2(商品2) CREATE TABLE Product2 ( product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER , purchase_price INTEGER , regist_date DATE , PRIMARY KEY (product_id));

將數據插入到表Product2中

--將數據插入到表Product2中 BEGIN TRANSACTION;BEGIN INSERT INTO Product2 VALUES (0001, T衫, 衣服, 1000, 500, 2017-09-20);INSERT 0 1 INSERT INTO Product2 VALUES (0002, 打孔器, 辦公用品, 500, 320, 2017-09-11);INSERT 0 1 INSERT INTO Product2 VALUES (0003, 運動T衫, 衣服, 4000, 2800, NULL);INSERT 0 1 INSERT INTO Product2 VALUES (0009, 手套, 衣服, 800,500, NULL);INSERT 0 1 INSERT INTO Product2 VALUES (0010, 水壺, 廚房用具, 2000, 1700, 2017-09-20);INSERT 0 1 COMMIT;COMMIT

確認一下我們創建的表：

SELECT * FROM Product2;

執行結果：

現在我們的準備工作已經做好，接下來，我們就對錶Product和表Product2進行加法運算吧。

--使用UNION對錶進行加法運算 SELECT product_id, product_name FROM Product UNION SELECT product_id, product_name FROM Product2;

執行結果：

product_id | product_name------------+-------------- 0009 | 手套 0004 | 菜刀 0002 | 打孔器 0001 | T衫 0003 | 運動T衫 0010 | 水壺 0006 | 叉子 0005 | 高壓鍋 0007 | 擦菜板 0008 | 圓珠筆(10 行記錄)

如上所示，執行結果包含了兩張表的全部商品，UNION就類似於數學裡面的並集運算。

注釋：集合運算符會除去重複的記錄。

集合運算的注意事項

注意事項1—作為運算對象的記錄的列數必須相同

例如，向下面這樣，一部分包含2列，另一部分包含3列，是無法進行運算的：

--列數不一致時會發生錯誤 SELECT product_id, product_name FROM Product UNION SELECT product_id, product_name, sale_price FROM Product2;

錯誤提示：

錯誤: 每一個 UNION 查詢必須有相同的欄位個數第4行SELECT product_id, product_name, sale_price

注意事項2—作為運算對象的記錄中列的數據類型必須一致

從左側開始，相同位置上的列必須為同一數據類型，否則會出錯：

--數據類型不一致時會發生錯誤 SELECT product_id, sale_price FROM Product UNION SELECT product_id, regist_date FROM Product2;

錯誤提示：

錯誤: UNION 的類型 integer 和 date 不匹配第4行SELECT product_id, regist_date

注意事項3—可以使用任意SELECT語句，但ORDER BY子句只能在最後使用一次

--ORDER BY子句只能在最後使用一次 SELECT product_id, product_name FROM Product WHERE product_type = 廚房用具 UNION SELECT product_id, product_name FROM Product2 WHERE product_type = 廚房用具 ORDER BY product_id;

執行結果：

product_id | product_name------------+-------------- 0004 | 菜刀 0005 | 高壓鍋 0006 | 叉子 0007 | 擦菜板 0010 | 水壺(5 行記錄)

包含重複行的集合運算—ALL選項

只需要在UNION後面添加關鍵字ALL就可以在運算結果中保留重複行：

--保留重複行 SELECT product_id, product_name FROM Product UNION ALL SELECT product_id, product_name FROM Product2;

執行結果：

product_id | product_name------------+-------------- 0001 | T衫 0002 | 打孔器 0003 | 運動T衫 0004 | 菜刀 0005 | 高壓鍋 0006 | 叉子 0007 | 擦菜板 0008 | 圓珠筆 0001 | T衫 0002 | 打孔器 0003 | 運動T衫 0009 | 手套 0010 | 水壺(13 行記錄)

選取表的公共部分—INTERSECT

INTERSECT（交集）可以選取兩個記錄集合中的公共部分，其語法和UNION相同：

--使用INTERSECT選取出表中的公共部分 SELECT product_id, product_name FROM Product INTERSECT SELECT product_id, product_name FROM Product2 ORDER BY product_id;

執行結果：

product_id | product_name------------+-------------- 0001 | T衫 0002 | 打孔器 0003 | 運動T衫(3 行記錄)

如上所示，結果只包含兩張表的公共部分，INTERSECT也就類似於數學裡面的交集運算。

記錄的減法—EXCEPT

EXCEPT（差集）是進行減法運算的集合運算符，其語法和UNION相同：

--使用EXCEPT對記錄進行減法運算 SELECT product_id, product_name FROM Product EXCEPT SELECT product_id, product_name FROM Product2 ORDER BY product_id;

執行結果：

product_id | product_name------------+-------------- 0004 | 菜刀 0005 | 高壓鍋 0006 | 叉子 0007 | 擦菜板 0008 | 圓珠筆(5 行記錄)

如上所示，結果中只包含了表Product中記錄除去表Product2中記錄之後的剩餘部分。

EXCEPT有一點與UNION和INTERSECT不同，那就是在減法運算中減數與被減數的位置不同，其結果也會不同：

--被減數與減數的位置不同，結果也不同 --從Product2表的記錄中除去Product表中的記錄 SELECT product_id, product_name FROM Product2 EXCEPT SELECT product_id, product_name FROM Product ORDER BY product_id;

執行結果：

product_id | product_name------------+-------------- 0009 | 手套 0010 | 水壺(2 行記錄)

如上所示，結果中只包含了表Product2中記錄除去表Product中記錄之後的剩餘部分。

聯結（以列為單位對錶進行聯結）

聯結（JOIN）就是將其他表中的列添加過來，進行「添加列」的集合運算。

UNION是以行為單位進行操作，而聯結則是對列為單位進行操作。

聯結大體上分為內聯結和外聯結兩種。

什麼是聯結

聯結（JOIN）運算，簡單來說，就是將其他表中的列添加過來，進行「添加列」的運算。

SQL中的聯結有很多種，我們主要學習內聯結和外聯結兩種。

內聯結—INNER JOIN

內聯結（INNER JOIN）是運用最廣泛的聯結運算。

我們繼續使用Product表和ShopProduct表來進行後續的學習。我們先來溫習一下兩張表的內容：

Product(商品)表：

product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+------------- 0001 | T衫 | 衣服 | 1000 | 500 | 2017-09-20 0002 | 打孔器 | 辦公用品 | 500 | 320 | 2017-09-11 0003 | 運動T衫 | 衣服 | 4000 | 2800 | 0004 | 菜刀 | 廚房用具 | 3000 | 2800 | 2017-09-20 0005 | 高壓鍋 | 廚房用具 | 6800 | 5000 | 2017-01-15 0006 | 叉子 | 廚房用具 | 500 | | 2017-09-20 0007 | 擦菜板 | 廚房用具 | 880 | 790 | 2016-04-28 0008 | 圓珠筆 | 辦公用品 | 100 | | 2017-11-11(8 行記錄)

ShopProduct(商店商品)表：

shop_id | shop_name | product_id | quantity---------+-----------+------------+---------- 000A | 成華區 | 0001 | 30 000A | 成華區 | 0002 | 50 000A | 成華區 | 0003 | 15 000B | 金牛區 | 0002 | 30 000B | 金牛區 | 0003 | 120 000B | 金牛區 | 0004 | 20 000B | 金牛區 | 0006 | 10 000B | 金牛區 | 0007 | 40 000C | 武侯區 | 0003 | 20 000C | 武侯區 | 0004 | 50 000C | 武侯區 | 0006 | 90 000C | 武侯區 | 0007 | 70 000D | 錦江區 | 0001 | 100(13 行記錄)

如上所示，兩張表都包含的列是：商品編號（product_id）

其他的列都是只存在一張表中。

所謂聯結運算，就是以兩張表都包含的列（如上面的商品編號列）作為橋樑，將其他滿足同樣條件的列彙集到同一結果中

下面我們就試著從Product表中取出商品名稱（product_name）和銷售單價（sale_price），並與ShopProduct表中的內容進行結合：

--將表Product和表ShopProduct進行內聯結 SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price FROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_id;

執行結果：

shop_id | shop_name | product_id | product_name | sale_price---------+-----------+------------+--------------+------------ 000D | 錦江區 | 0001 | T衫 | 1000 000A | 成華區 | 0001 | T衫 | 1000 000B | 金牛區 | 0002 | 打孔器 | 500 000A | 成華區 | 0002 | 打孔器 | 500 000C | 武侯區 | 0003 | 運動T衫 | 4000 000B | 金牛區 | 0003 | 運動T衫 | 4000 000A | 成華區 | 0003 | 運動T衫 | 4000 000C | 武侯區 | 0004 | 菜刀 | 3000 000B | 金牛區 | 0004 | 菜刀 | 3000 000C | 武侯區 | 0006 | 叉子 | 500 000B | 金牛區 | 0006 | 叉子 | 500 000C | 武侯區 | 0007 | 擦菜板 | 880 000B | 金牛區 | 0007 | 擦菜板 | 880(13 行記錄)

內聯結要點1—FROM子句

進行聯結是需要在FROM子句中使用多個表：

FROM ShopProduct AS SP INNER JOIN Product AS P

使用INNER JOIN關鍵字將兩張表聯結在一起，SP和P分別是這兩張表的別名。

內聯結要點2—ON子句

在ON之後指定兩張表聯結所使用的列（聯結鍵）：

ON SP.product_id = P.product_id

進行內聯結是必須使用ON子句，並且要書寫在FROM和WHERE子句之間。

內聯結要點3—SELECT子句

在SELECT子句中指定需要的列：

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price

使用聯結時SELECT子句中的列需要按照「<表的別名>.<列名>」的格式進行書寫。

內聯結和WHERE子句結合使用

選取出成華區商店的記錄：

--內聯結和WHERE子句結合使用 SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price FROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_id WHERE SP.shop_id = 000A;

執行結果：

shop_id | shop_name | product_id | product_name | sale_price---------+-----------+------------+--------------+------------ 000A | 成華區 | 0001 | T衫 | 1000 000A | 成華區 | 0002 | 打孔器 | 500 000A | 成華區 | 0003 | 運動T衫 | 4000(3 行記錄)

外聯結—OUTER JOIN

外聯結也是通過ON子句的聯結鍵將兩張表進行聯結，並從兩張表中同時選取出相應的列。基本的使用方法與內聯結相同，只是結果有所不同。我們把剛才的內聯結語句改為外聯結語句看看就知道差異了：

--將兩張表進行外聯結 SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P ON SP.product_id = P.product_id;

執行結果：

shop_id | shop_name | product_id | product_name | sale_price---------+-----------+------------+--------------+------------ 000D | 錦江區 | 0001 | T衫 | 1000 000A | 成華區 | 0001 | T衫 | 1000 000B | 金牛區 | 0002 | 打孔器 | 500 000A | 成華區 | 0002 | 打孔器 | 500 000C | 武侯區 | 0003 | 運動T衫 | 4000 000B | 金牛區 | 0003 | 運動T衫 | 4000 000A | 成華區 | 0003 | 運動T衫 | 4000 000C | 武侯區 | 0004 | 菜刀 | 3000 000B | 金牛區 | 0004 | 菜刀 | 3000 | | | 高壓鍋 | 6800 000C | 武侯區 | 0006 | 叉子 | 500 000B | 金牛區 | 0006 | 叉子 | 500 000C | 武侯區 | 0007 | 擦菜板 | 880 000B | 金牛區 | 0007 | 擦菜板 | 880 | | | 圓珠筆 | 100(15 行記錄)

如上所示，比內聯結時多出了兩行（高壓鍋和圓珠筆）

外聯結要點1—選取出單張表中全部的信息

內聯結只能選出同時存在於兩張表中的數據，相反，對於外聯結來說，只要數據存在於某一張表中，就能夠讀取出來。

外聯結要點2—關於主表問題

外聯結還有一點非常重要，那就是要把哪張表作為主表。最終結果會包含主表中所有的數據。指定主表的關鍵字是LEFT和RIGHT。顧名思義，使用LEFT時FROM子句中寫在左側的表是主表，使用RIGHT時右側的表是主表。

上面的例子使用的是RIGHT關鍵字：

FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P

說了這麼多，最要的是：使用二者所得到的結果完全相同！

所以不必糾結使用LEFT還是RIGHT，結果都是一樣的。

3張以上的表的聯結

原則上聯結表的數量並沒有限制，所以我們可以嘗試一下聯結3張表，

首先再創建一個用來管理庫存商品的表InventoryProduct:

--創建InventoryProduct表 CREATE TABLE InventoryProduct ( inventory_id CHAR(4) NOT NULL, product_id CHAR(4) NOT NULL, inventory_quantity INTEGER NOT NULL, PRIMARY KEY(inventory_id, product_id));CREATE TABLE --插入數據 BEGIN TRANSACTION;BEGIN INSERT INTO InventoryProduct VALUES (P001, 0001, 0);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P001, 0002, 120);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P001, 0003, 200);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P001, 0004, 3);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P001, 0005, 0);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P001, 0006, 99);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P001, 0007, 999);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P001, 0008, 200);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P002, 0001, 10);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P002, 0002, 25);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P002, 0003, 34);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P002, 0004, 19);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P002, 0005, 99);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P002, 0006, 0);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P002, 0007, 0);INSERT 0 1 INSERT INTO InventoryProduct VALUES (P002, 0008, 18);INSERT 0 1 COMMIT;COMMIT

確認一下創建的表的內容：

SELECT * FROM InventoryProduct;

執行結果：

inventory_id | product_id | inventory_quantity--------------+------------+-------------------- P001 | 0001 | 0 P001 | 0002 | 120 P001 | 0003 | 200 P001 | 0004 | 3 P001 | 0005 | 0 P001 | 0006 | 99 P001 | 0007 | 999 P001 | 0008 | 200 P002 | 0001 | 10 P002 | 0002 | 25 P002 | 0003 | 34 P002 | 0004 | 19 P002 | 0005 | 99 P002 | 0006 | 0 P002 | 0007 | 0 P002 | 0008 | 18(16 行記錄)

然後我們對三張表進行內聯結，聯結鍵為商品編號（product_id）:

--對3張表進行內聯結 SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price, IP.inventory_quantity FROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_id INNER JOIN InventoryProduct AS IP ON SP.product_id = IP.product_id WHERE IP.inventory_id = P001;

執行結果：

shop_id | shop_name | product_id | product_name | sale_price | inventory_quantity---------+-----------+------------+--------------+------------+-------------------- 000A | 成華區 | 0001 | T衫 | 1000 | 0 000A | 成華區 | 0002 | 打孔器 | 500 | 120 000A | 成華區 | 0003 | 運動T衫 | 4000 | 200 000B | 金牛區 | 0002 | 打孔器 | 500 | 120 000B | 金牛區 | 0003 | 運動T衫 | 4000 | 200 000B | 金牛區 | 0004 | 菜刀 | 3000 | 3 000B | 金牛區 | 0006 | 叉子 | 500 | 99 000B | 金牛區 | 0007 | 擦菜板 | 880 | 999 000C | 武侯區 | 0003 | 運動T衫 | 4000 | 200 000C | 武侯區 | 0004 | 菜刀 | 3000 | 3 000C | 武侯區 | 0006 | 叉子 | 500 | 99 000C | 武侯區 | 0007 | 擦菜板 | 880 | 999 000D | 錦江區 | 0001 | T衫 | 1000 | 0(13 行記錄)

交叉聯結—CROSS JOIN

交叉聯結的語法十分簡單，但結果特別長，例如對兩張表進行交叉聯結：

--將兩張表進行交叉聯結 SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name FROM ShopProduct AS SP CROSS JOIN Product AS P;

上述的SQL語句的執行結果有104行，我就不貼了，交叉聯結的原理就是笛卡爾積。因此結果中的記錄通常是兩張錶行數的乘積。本例中，因為ShopProduct表有13行記錄，Product表有8行記錄，所以，結果就有13 * 8 = 104行記錄。

每天學習一點點，每天進步一點點。