SQL JOIN 以列為單位對表進行聯結

• 一、什么是聯結
• 二、行內結——INNER JOIN
  • 2.1 行內結要點 ① ——FROM 子句
  • 2.2 行內結要點 ②—— ON 子句
  • 2.3 行內結要點 ③ ——SELECT 子句
    • 2.3.1 行內結和 WHERE 子句結合使用
• 三、外聯結——OUTER JOIN
  • 3.1 外聯結要點 ① ——選取出單張表中全部的資訊
  • 3.2 外聯結要點 ② ——每張表都是主表嗎？
• 四、3 張以上的表的聯結
• 五、交叉聯結——CROSS JOIN
• 六、聯結的特定語法和過時語法
• 請參閱

學習重點

• 聯結（JOIN）就是將其他表中的列添加過來，進行“添加列”的集合運算，UNION 是以行（縱向）為單位進行操作，而聯結則是以列（橫向）為單位進行的，

• 聯結大體上分為行內結和外聯結兩種，首先請大家牢牢掌握這兩種聯結的使用方法，

• 請大家一定要使用標準 SQL 的語法格式來寫聯結運算，對于那些過時的或者特定 SQL 中的寫法，了解一下即可，不建議使用，

一、什么是聯結

在 表的加減法 中我們學習了 UNION 和 INTERSECT 等集合運算，這些集合運算的特征就是以行方向為單位進行操作，通俗地說，就是進行這些集合運算時，會導致記錄行數的增減，使用 UNION 會增加記錄行數，而使用 INTERSECT 或者 EXCEPT 會減少記錄行數 ^[1]，

但是這些運算不會導致列數的改變，作為集合運算物件的表的前提就是列數要一致，因此，運算結果不會導致列的增減，

本文將要學習的 聯結（JOIN） 運算，簡單來說，就是將其他表中的列添加過來，進行“添加列”的運算（圖 5），該操作通常用于無法從一張表中獲取期望資料（列）的情況，截至目前，本教程中出現的示例基本上都是從一張表中選取資料，但實際上，期望得到的資料往往會分散在不同的表之中，使用聯結就可以從多張表（3 張以上的表也沒關系）中選取資料了，

KEYWORD

• 聯結（JOIN）

SQL 的聯結根據其用途可以分為很多種類，這里希望大家掌握的有兩種，行內結和外聯結，接下來，我們就以這兩種聯結為中心進行學習，

二、行內結——INNER JOIN

首先我們來學習行內結（INNER JOIN），它是應用最廣泛的聯結運算，大家現在可以暫時忽略“內”這個字，之后會給大家詳細說明，

KEYWORD

• 行內結（INNER JOIN）

本例中我們會繼續使用 Product 表和 謂詞 創建的 ShopProduct 表，下面我們再來回顧一下這兩張表的內容，

表 7-1　Product（商品）表

表 2　ShopProduct（商店商品）表

對這兩張表包含的列進行整理后的結果如表 3 所示，

表 3　兩張表及其包含的列

如上表所示，兩張表中的列可以分為如下兩類，

A：兩張表中都包含的列 → 商品編號

B：只存在于一張表內的列 → 商品編號之外的列

所謂聯結運算，一言以蔽之，就是“以 A 中的列作為橋梁，將 B 中滿足同樣條件的列匯集到同一結果之中”，具體程序如下所述，

從 ShopProduct 表中的資料我們能夠知道，東京店（000A）銷售商品編號為 0001、0002 和 0003 的商品，但這些商品的商品名稱（product_name）和銷售單價（sale_price）在 ShopProduct 表中并不存在，這些資訊都保存在 Product 表中，大阪店和名古屋店的情況也是如此，

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

執行結果

 shop_id  | shop_name | product_id  | product_name | sale_price
----------+-----------+-------------+--------------+-------------
 000A     | 東京      | 0002        | 打孔器       |        500
 000A     | 東京      | 0003        | 運動T恤      |       4000
 000A     | 東京      | 0001        | T恤衫        |       1000
 000B     | 名古屋    | 0007        | 擦菜板       |        880
 000B     | 名古屋    | 0002        | 打孔器       |        500
 000B     | 名古屋    | 0003        | 運動T恤      |       4000
 000B     | 名古屋    | 0004        | 菜刀         |       3000
 000B     | 名古屋    | 0006        | 叉子         |        500
 000C     | 大阪      | 0007        | 擦菜板       |        880
 000C     | 大阪      | 0006        | 叉子         |        500
 000C     | 大阪      | 0003        | 運動T恤      |       4000
 000C     | 大阪      | 0004        | 菜刀         |       3000
 000D     | 福岡      | 0001        | T恤衫        |       1000

能夠得到上述結果的 SELECT 陳述句如代碼清單 9 所示，

代碼清單 9　將兩張表進行行內結

SQL Server DB2 PostgreSQL MySQL

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;

特定的 SQL

在 Oracle 的 FROM 子句中不能使用 AS（會發生錯誤），因此，在 Oracle 中執行代碼清單 9 時，請將 ① 的部分變為“FROM ShopProduct SP INNER JOIN Product P”，

關于行內結，請大家注意以下三點，

2.1 行內結要點 ① ——FROM 子句

第一點要注意的是，之前的 FROM 子句中只有一張表，而這次我們同時使用了 ShopProduct 和 Product 兩張表，

FROM ShopProduct AS SP INNER JOIN Product AS P

使用關鍵字 INNER JOIN 就可以將兩張表聯結在一起了，SP 和 P 分別是這兩張表的別名，但別名并不是必需的，在 SELECT 子句中直接使用 ShopProduct 和 product_id 這樣的表的原名也沒有關系，但由于表名太長會影響 SQL 陳述句的可讀性，因此還是希望大家能夠習慣使用別名 ^[2]，

法則 3

進行聯結時需要在 FROM 子句中使用多張表，

2.2 行內結要點 ②—— ON 子句

第二點要注意的是 ON 后面的聯結條件，

KEYWORD

• ON 子句

ON SP.product_id = P.product_id

我們可以在 ON 之后指定兩張表聯結所使用的列（聯結鍵），本例中使用的是商品編號（product_id），也就是說，ON 是專門用來指定聯結條件的，它能起到與 WHERE 相同的作用，需要指定多個鍵時，同樣可以使用 AND、OR，在進行行內結時 ON 子句是必不可少的（如果沒有 ON 會發生錯誤），并且 ON 必須書寫在 FROM 和 WHERE 之間，

KEYWORD

• 聯結鍵

法則 4

進行行內結時必須使用 ON 子句，并且要書寫在 FROM 和 WHERE 之間，

舉個比較直觀的例子，ON 就像是連接河流兩岸城鎮的橋梁一樣（圖 6），

聯結條件也可以使用“=”來記述，在語法上，還可以使用 <= 和 BETWEEN 等謂詞，但因為實際應用中九成以上都可以用“=”進行聯結，所以開始時大家只要記住使用“=”就可以了，使用“=”將聯結鍵關聯起來，就能夠將兩張表中滿足相同條件的記錄進行“聯結”了，

2.3 行內結要點 ③ ——SELECT 子句

第三點要注意的是，在 SELECT 子句中指定的列，

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

在 SELECT 子句中，像 SP.shop_id 和 P.sale_price 這樣使用“<表的別名>.<列名>”的形式來指定列，和使用一張表時不同，由于多表聯結時，某個列到除錯于哪張表比較容易混亂，因此采用了這樣的防范措施，從語法上來說，只有那些同時存在于兩張表中的列（這里是 product_id）必須使用這樣的書寫方式，其他的列像 shop_id 這樣直接書寫列名也不會發生錯誤，但是就像前面說的那樣，為了避免混亂，還是希望大家能夠在使用聯結時按照“<表的別名>.<列名>”的格式來書寫 SELECT 子句中全部的列，

法則 5

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

2.3.1 行內結和 WHERE 子句結合使用

如果并不想了解所有商店的情況，例如只想知道東京店（000A）的資訊時，可以像之前學習的那樣在 WHERE 子句中添加條件，這樣我們就可以從代碼清單 9 中得到的全部商店的資訊中選取出東京店的記錄了，

代碼清單 10　行內結和 WHERE 子句結合使用

SQL Server DB2 PostgreSQL MySQL

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';

特定的 SQL

在 Oracle 中執行代碼清單 10 時，請將 ① 的部分變為“FROM ShopProduct SP INNER JOIN Product P”（刪掉 FROM 子句中的 AS），

執行結果

 shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+-----------
 000A    | 東京      | 0001       | T恤衫         |      1000
 000A    | 東京      | 0002       | 打孔器        |       500
 000A    | 東京      | 0003       | 運動T恤       |      4000

像這樣使用聯結運算將滿足相同規則的表聯結起來時，WHERE、GROUP BY、HAVING、ORDER BY 等工具都可以正常使用，我們可以將聯結之后的結果想象為新創建出來的一張表（表 4），對這張表使用 WHERE 子句等工具，這樣理解起來就容易多了吧，

當然，這張“表”只在 SELECT 陳述句執行期間存在，SELECT 陳述句執行之后就會消失，如果希望繼續使用這張“表”，還是將它創建成視圖吧，

表 4　通過聯結創建出的表（ProductJoinShopProduct）的圖示

三、外聯結——OUTER JOIN

行內結之外比較重要的就是 外聯結（OUTER JOIN） 了，我們再來回顧一下前面的例子，在前例中，我們將 Product 表和 ShopProduct 表進行行內結，從兩張表中取出各個商店銷售的商品資訊，其中，實作“從兩張表中取出”的就是聯結功能，

KEYWORD

• 外聯結（OUTER JOIN）

外聯結也是通過 ON 子句的聯結鍵將兩張表進行聯結，并從兩張表中同時選取相應的列的，基本的使用方法并沒有什么不同，只是結果卻有所不同，事實勝于雄辯，還是讓我們先把之前行內結的 SELECT 陳述句（代碼清單 9）轉換為外聯結試試看吧，轉換的結果請參考代碼清單 11，

代碼清單 11　將兩張表進行外聯結

SQL Server DB2 PostgreSQL MySQL

SELECT SP.shop_id, SP.shop_name, P.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;

特定的 SQL

在 Oracle 中執行代碼清單 11 時，請將 ① 的部分變為“FROM ShopProduct SP RIGHT OUTER JOIN Product P”（洗掉掉 FROM 子句中的 AS），

執行結果

3.1 外聯結要點 ① ——選取出單張表中全部的資訊

與行內結的結果相比，不同點顯而易見，那就是結果的行數不一樣，行內結的結果中有 13 條記錄，而外聯結的結果中有 15 條記錄，增加的 2 條記錄到底是什么呢？

這正是外聯結的關鍵點，多出的 2 條記錄是高壓鍋和圓珠筆，這 2 條記錄在 ShopProduct 表中并不存在，也就是說，這 2 種商品在任何商店中都沒有銷售，由于行內結只能選取出同時存在于兩張表中的資料，因此只在 Product 表中存在的 2 種商品并沒有出現在結果之中，

相反，對于外聯結來說，只要資料存在于某一張表當中，就能夠讀取出來，在實際的業務中，例如想要生成固定行數的單據時，就需要使用外聯結，如果使用行內結的話，根據 SELECT 陳述句執行時商店庫存狀況的不同，結果的行數也會發生改變，生成的單據的版式也會受到影響，而使用外聯結能夠得到固定行數的結果，

雖說如此，那些表中不存在的資訊我們還是無法得到，結果中高壓鍋和圓珠筆的商店編號和商店名稱都是 NULL（具體資訊大家都不知道，真是無可奈何），外聯結名稱的由來也跟 NULL 有關，即“結果中包含原表中不存在（在原表之外）的資訊”，相反，只包含表內資訊的聯結也就被稱為行內結了，

3.2 外聯結要點 ② ——每張表都是主表嗎？

外聯結還有一點非常重要，那就是要把哪張表作為主表，最終的結果中會包含主表內所有的資料，指定主表的關鍵字是 LEFT 和 RIGHT，顧名思義，使用 LEFT 時 FROM 子句中寫在左側的表是主表，使用 RIGHT 時右側的表是主表，代碼清單 11 中使用了 RIGHT，因此，右側的表，也就是 Product 表是主表，

KEYWORD

• LEFT 關鍵字

• RIGHT 關鍵字

我們還可以像代碼清單 12 這樣進行改寫，意思完全相同，

代碼清單 12　改寫后外聯結的結果完全相同

SQL Server DB2 PostgreSQL MySQL

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
  FROM Product AS P LEFT OUTER JOIN ShopProduct AS SP ①
    ON SP.product_id = P.product_id;

特定的 SQL

在 Oracle 中執行代碼清單 12 時，請將 ① 的部分變為“FROM ShopProduct SP LEFT OUTER JOIN Product P”（洗掉掉 FROM 子句中的 AS），

大家可能會猶豫到底應該使用 LEFT 還是 RIGHT，其實它們的功能沒有任何區別，使用哪一個都可以，通常使用 LEFT 的情況會多一些，但也并沒有非使用這個不可的理由，使用 RIGHT 也沒有問題，

法則 6

外聯結中使用 LEFT、RIGHT來指定主表，使用二者所得到的結果完全相同，

四、3 張以上的表的聯結

通常聯結只涉及 2 張表，但有時也會出現必須同時聯結 3 張以上的表的情況，原則上聯結表的數量并沒有限制，下面就讓我們來看一下 3 張表的聯結吧，

首先我們創建一張用來管理庫存商品的表（表 5），假設商品都保存在 P001 和 P002 這 2 個倉庫之中，

表 5　InventoryProduct（庫存商品）表

創建該表及插入資料的 SQL 陳述句請參考代碼清單 13，

代碼清單 13　創建 InventoryProduct 表并向其中插入資料

-- DDL ：創建表
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));

SQL Server PostgreSQL

-- DML ：插入資料
BEGIN TRANSACTION; ------------①

INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001',      '0001',   0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001',      '0002',   120);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001',      '0003',   200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001',      '0004',   3);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001',      '0005',   0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001',      '0006',   99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001',      '0007',   999);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001',      '0008',   200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002',      '0001',   10);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002',      '0002',   25);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002',      '0003',   34);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002',      '0004',   19);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002',      '0005',   99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002',      '0006',   0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002',      '0007',   0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002',      '0008',   18);

COMMIT;

特定的 SQL

不同的 DBMS 的事務處理的語法也不盡相同，代碼清單 13 中的 DML 陳述句在 MySQL 中執行時，需要將 ① 部分更改為“START TRANSACTION;”，在 Oracle 和 DB2 中執行時，無需用到 ① 的部分（請洗掉），

詳細內容請大家參考 事務 中的“創建事務”，

下面我們從上表中取出保存在 P001 倉庫中的商品數量，并將該列添加到代碼清單 11 所得到的結果中，聯結方式為行內結（外聯結的使用方法完全相同），聯結鍵為商品編號（product_id）（代碼清單 14），

代碼清單 14　對 3 張表進行行內結

特定的 SQL

在 Oracle 中執行代碼清單 14 時，請將 ① 的部分變為“FROM ShopProduct SP INNER JOIN Product P”，將 ② 的部分變為“INNER JOIN InventoryProduct IP”（洗掉掉 FROM 子句中的 AS）

執行結果

 shop_id | shop_name | product_id | product_name | sale_price | inventory_quantity
---------+-----------+------------+--------------+------------+-------------------
 000A    | 東京      | 0002       | 打孔器       |        500 |                120
 000A    | 東京      | 0003       | 運動T恤      |       4000 |                200
 000A    | 東京      | 0001       | T恤衫        |       1000 |                  0
 000B    | 名古屋    | 0007       | 擦菜板       |        880 |                999
 000B    | 名古屋    | 0002       | 打孔器       |        500 |                120
 000B    | 名古屋    | 0003       | 運動T恤      |       4000 |                200
 000B    | 名古屋    | 0004       | 菜刀         |       3000 |                  3
 000B    | 名古屋    | 0006       | 叉子         |        500 |                 99
 000C    | 大阪      | 0007       | 擦菜板       |        880 |                999
 000C    | 大阪      | 0006       | 叉子         |        500 |                 99
 000C    | 大阪      | 0003       | 運動T恤      |       4000 |                200
 000C    | 大阪      | 0004       | 菜刀         |       3000 |                  3
 000D    | 福岡      | 0001       | T恤衫        |       1000 |                  0

在代碼清單 11 行內結的 FROM 子句中，再次使用 INNER JOIN 將 InventoryProduct 表也添加了進來，

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

通過 ON 子句指定聯結條件的方式也沒有發生改變，使用等號將作為聯結條件的 Product 表和 ShopProduct 表中的商品編號（product_id）聯結起來，由于 Product 表和 ShopProduct 表已經進行了聯結，因此這里無需再對 Product 表和 InventoryProduct 表進行聯結了（雖然也可以進行聯結，但結果并不會發生改變），

即使想要把聯結的表增加到 4 張、5 張……使用 INNER JOIN 進行添加的方式也是完全相同的，

五、交叉聯結——CROSS JOIN

接下來和大家一起學習第 3 種聯結方式——交叉聯結（CROSS JOIN），其實這種聯結在實際業務中并不會使用（筆者使用這種聯結的次數也屈指可數），那為什么還要在這里進行介紹呢？這是因為交叉聯結是所有聯結運算的基礎，

KEYWORD

• 交叉聯結（CROSS JOIN）

交叉聯結本身非常簡單，但是其結果有點麻煩，下面我們就試著將 Product 表和 ShopProduct 表進行交叉聯結（代碼清單 15），

代碼清單 15　將兩張表進行交叉聯結

SQL Server DB2 PostgreSQL MySQL

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
  FROM ShopProduct AS SP CROSS JOIN Product AS P; -----①

特定的 SQL

在 Oracle 中執行代碼清單 15 時，請將 ① 的部分變為“FROM ShopProduct SP CROSS JOIN Product P;”（洗掉掉 FROM 子句中的 AS），

執行結果

 shop_id | shop_name | product_id | product_name
---------+-----------+------------+-------------
 000A    | 東京      | 0001       | T恤衫
 000A    | 東京      | 0002       | T恤衫
 000A    | 東京      | 0003       | T恤衫
 000B    | 名古屋    | 0002       | T恤衫
 000B    | 名古屋    | 0003       | T恤衫
 000B    | 名古屋    | 0004       | T恤衫
 000B    | 名古屋    | 0006       | T恤衫
 000B    | 名古屋    | 0007       | T恤衫
 000C    | 大阪      | 0003       | T恤衫
 000C    | 大阪      | 0004       | T恤衫
 000C    | 大阪      | 0006       | T恤衫
 000C    | 大阪      | 0007       | T恤衫
 000D    | 福岡      | 0001       | T恤衫
 000A    | 東京      | 0001       | 打孔器
 000A    | 東京      | 0002       | 打孔器
 000A    | 東京      | 0003       | 打孔器
 000B    | 名古屋    | 0002       | 打孔器
 000B    | 名古屋    | 0003       | 打孔器
 000B    | 名古屋    | 0004       | 打孔器
 000B    | 名古屋    | 0006       | 打孔器
 000B    | 名古屋    | 0007       | 打孔器
 000C    | 大阪      | 0003       | 打孔器
 000C    | 大阪      | 0004       | 打孔器
 000C    | 大阪      | 0006       | 打孔器
 000C    | 大阪      | 0007       | 打孔器
 000D    | 福岡      | 0001       | 打孔器
 000A    | 東京      | 0001       | 運動T恤
 000A    | 東京      | 0002       | 運動T恤
 000A    | 東京      | 0003       | 運動T恤
 000B    | 名古屋    | 0002       | 運動T恤
 000B    | 名古屋    | 0003       | 運動T恤
 000B    | 名古屋    | 0004       | 運動T恤
 000B    | 名古屋    | 0006       | 運動T恤
 000B    | 名古屋    | 0007       | 運動T恤
 000C    | 大阪      | 0003       | 運動T恤
 000C    | 大阪      | 0004       | 運動T恤
 000C    | 大阪      | 0006       | 運動T恤
 000C    | 大阪      | 0007       | 運動T恤
 000D    | 福岡      | 0001       | 運動T恤
 000A    | 東京      | 0001       | 菜刀
 000A    | 東京      | 0002       | 菜刀
 000A    | 東京      | 0003       | 菜刀
 000B    | 名古屋    | 0002       | 菜刀
 000B    | 名古屋    | 0003       | 菜刀
 000B    | 名古屋    | 0004       | 菜刀
 000B    | 名古屋    | 0006       | 菜刀
 000B    | 名古屋    | 0007       | 菜刀
 000C    | 大阪      | 0003       | 菜刀
 000C    | 大阪      | 0004       | 菜刀
 000C    | 大阪      | 0006       | 菜刀
 000C    | 大阪      | 0007       | 菜刀
 000D    | 福岡      | 0001       | 菜刀
 000A    | 東京      | 0001       | 高壓鍋
 000A    | 東京      | 0002       | 高壓鍋
 000A    | 東京      | 0003       | 高壓鍋
 000B    | 名古屋    | 0002       | 高壓鍋
 000B    | 名古屋    | 0003       | 高壓鍋
 000B    | 名古屋    | 0004       | 高壓鍋
 000B    | 名古屋    | 0006       | 高壓鍋
 000B    | 名古屋    | 0007       | 高壓鍋
 000C    | 大阪      | 0003       | 高壓鍋
 000C    | 大阪      | 0004       | 高壓鍋
 000C    | 大阪      | 0006       | 高壓鍋
 000C    | 大阪      | 0007       | 高壓鍋
 000D    | 福岡      | 0001       | 高壓鍋
 000A    | 東京      | 0001       | 叉子
 000A    | 東京      | 0002       | 叉子
 000A    | 東京      | 0003       | 叉子
 000B    | 名古屋    | 0002       | 叉子
 000B    | 名古屋    | 0003       | 叉子
 000B    | 名古屋    | 0004       | 叉子
 000B    | 名古屋    | 0006       | 叉子
 000B    | 名古屋    | 0007       | 叉子
 000C    | 大阪      | 0003       | 叉子
 000C    | 大阪      | 0004       | 叉子
 000C    | 大阪      | 0006       | 叉子
 000C    | 大阪      | 0007       | 叉子
 000D    | 福岡      | 0001       | 叉子
 000A    | 東京      | 0001       | 擦菜板
 000A    | 東京      | 0002       | 擦菜板
 000A    | 東京      | 0003       | 擦菜板
 000B    | 名古屋    | 0002       | 擦菜板
 000B    | 名古屋    | 0003       | 擦菜板
 000B    | 名古屋    | 0004       | 擦菜板
 000B    | 名古屋    | 0006       | 擦菜板
 000B    | 名古屋    | 0007       | 擦菜板
 000C    | 大阪      | 0003       | 擦菜板
 000C    | 大阪      | 0004       | 擦菜板
 000C    | 大阪      | 0006       | 擦菜板
 000C    | 大阪      | 0007       | 擦菜板
 000D    | 福岡      | 0001       | 擦菜板
 000A    | 東京      | 0001       | 圓珠筆
 000A    | 東京      | 0002       | 圓珠筆
 000A    | 東京      | 0003       | 圓珠筆
 000B    | 名古屋    | 0002       | 圓珠筆
 000B    | 名古屋    | 0003       | 圓珠筆
 000B    | 名古屋    | 0004       | 圓珠筆
 000B    | 名古屋    | 0006       | 圓珠筆
 000B    | 名古屋    | 0007       | 圓珠筆
 000C    | 大阪      | 0003       | 圓珠筆
 000C    | 大阪      | 0004       | 圓珠筆
 000C    | 大阪      | 0006       | 圓珠筆
 000C    | 大阪      | 0007       | 圓珠筆
 000D    | 福岡      | 0001       | 圓珠筆

可能大家會驚訝于結果的行數，但我們還是先來介紹一下語法結構吧，對滿足相同規則的表進行交叉聯結的集合運算子是 CROSS JOIN（笛卡兒積），進行交叉聯結時無法使用行內結和外聯結中所使用的 ON 子句，這是因為交叉聯結是對兩張表中的全部記錄進行交叉組合，因此結果中的記錄數通常是兩張表中行數的乘積，本例中，因為 ShopProduct 表存在 13 條記錄，Product 表存在 8 條記錄，所以結果中就包含了 13 × 8 = 104 條記錄，

KEYWORD

• CROSS JOIN（笛卡兒積）

可能這時會有讀者想起在 表的加減法 中我們提到過集合運算中的乘法會在本節中進行詳細學習，這就是上面介紹的交叉聯結，

行內結是交叉聯結的一部分，“內”也可以理解為“包含在交叉聯結結果中的部分”，相反，外聯結的“外”可以理解為“交叉聯結結果之外的部分”，

交叉聯結沒有應用到實際業務之中的原因有兩個，一是其結果沒有實用價值，二是由于其結果行數太多，需要花費大量的運算時間和高性能設備的支持，

六、聯結的特定語法和過時語法

之前我們學習的行內結和外聯結的語法都符合標準 SQL 的規定，可以在所有 DBMS 中執行，因此大家可以放心使用，但是如果大家之后從事系統開發作業的話，一定會碰到需要閱讀他人寫的代碼并進行維護的情況，而那些使用特定和過時語法的程式就會成為我們的麻煩，

SQL 是一門特定語法及過時語法非常多的語言，雖然之前本教程中也多次提及，但聯結是其中特定語法的部分，現在還有不少年長的程式員和系統工程師仍在使用這些特定的語法，

例如，將本文最初介紹的行內結的 SELECT 陳述句（代碼清單 9）替換為過時語法的結果如下所示（代碼清單 16），

代碼清單 16　使用過時語法的行內結（結果與代碼清單 9 相同）

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
  FROM ShopProduct SP, Product P
 WHERE SP.product_id = P.product_id
   AND SP.shop_id = '000A';

這樣的書寫方式所得到的結果與標準語法完全相同，并且這樣的語法可以在所有的 DBMS 中執行，并不能算是特定的語法，只是過時了而已，

但是，由于這樣的語法不僅過時，而且還存在很多其他的問題，因此不推薦大家使用，理由主要有以下三點，

第一，使用這樣的語法無法馬上判斷出到底是行內結還是外聯結（又或者是其他種類的聯結），

第二，由于聯結條件都寫在 WHERE 子句之中，因此無法在短時間內分辨出哪部分是聯結條件，哪部分是用來選取記錄的限制條件，

第三，我們不知道這樣的語法到底還能使用多久，每個 DBMS 的開發者都會考慮放棄過時的語法，轉而支持新的語法，雖然并不是馬上就不能使用了，但那一天總會到來的，

雖然這么說，但是現在使用這些過時語法撰寫的程式還有很多，到目前為止還都能正常執行，我想大家很可能會碰到這樣的代碼，因此還是希望大家能夠了解這些知識，

法則 7

對于聯結的過時語法和特定語法，雖然不建議使用，但還是希望大家能夠讀懂，

專欄

關系除法

這個部分的教程中我們學習了以下 4 個集合運算子，

• UNION（并集）

• EXCEPT（差集）

• INTERSECT（交集）

• CROSS JOIN（笛卡兒積）

雖然交集是一種獨立的集合運算，但實際上它也是“只包含公共部分的特殊 UNION”，剩下的 3 個在四則運算中也有對應的運算，但是，除法運算還沒有介紹，

難道集合運算中沒有除法嗎？當然不是，除法運算是存在的，集合運算中的除法通常稱為關系除法，關系是數學領域中對表或者視圖的稱謂，但是并沒有定義像 UNION 或者 EXCEPT 這樣專用的運算子，如果要定義，估計應該是 DIVIDE（除）吧，但截至目前并沒有 DBMS 使用這樣的運算子，

KEYWORD

• 關系除法

為什么只有除法運算不使用運算子（只有除法）對被除數進行運算呢？其中的理由有點復雜，還是讓我們先來介紹一下“表的除法”具體是一種什么樣的運算吧，

我們使用表 A 和表 B 兩張表作為示例用表，

表 A　Skills（技術）表：關系除法中的除數

skill	--
Oracle
UNIX
Java

表 B　EmpSkills（員工技術）表：關系除法中的被除數

emp	skill
相田	Oracle
相田	UNIX
相田	Java
相田	C#
神崎	Oracle
神崎	UNIX
神崎	Java
平井	UNIX
平井	Oracle
平井	PHP
平井	Perl
平井	C++
若田部	Perl
渡來	Oracle

創建上述兩張表并向其中插入資料的 SQL 陳述句請參考代碼清單 A，

代碼清單 A　創建 Skills/EmpSkills表并插入資料

-- DDL ：創建表
CREATE TABLE Skills
(skill VARCHAR(32),
PRIMARY KEY(skill));

CREATE TABLE EmpSkills
(emp   VARCHAR(32),
skill VARCHAR(32),
PRIMARY KEY(emp, skill));

SQL Server PostgreSQL

-- DML ：插入資料
BEGIN TRANSACTION; -------------①

INSERT INTO Skills VALUES('Oracle');
INSERT INTO Skills VALUES('UNIX');
INSERT INTO Skills VALUES('Java');

INSERT INTO EmpSkills VALUES('相田', 'Oracle');
INSERT INTO EmpSkills VALUES('相田', 'UNIX');
INSERT INTO EmpSkills VALUES('相田', 'Java');
INSERT INTO EmpSkills VALUES('相田', 'C#');
INSERT INTO EmpSkills VALUES('神崎', 'Oracle');
INSERT INTO EmpSkills VALUES('神崎', 'UNIX');
INSERT INTO EmpSkills VALUES('神崎', 'Java');
INSERT INTO EmpSkills VALUES('平井', 'UNIX');
INSERT INTO EmpSkills VALUES('平井', 'Oracle');
INSERT INTO EmpSkills VALUES('平井', 'PHP');
INSERT INTO EmpSkills VALUES('平井', 'Perl');
INSERT INTO EmpSkills VALUES('平井', 'C++');
INSERT INTO EmpSkills VALUES('若田部', 'Perl');
INSERT INTO EmpSkills VALUES('渡來', 'Oracle');

COMMIT;

特定的 SQL

不同的 DBMS 的事務處理的語法也不盡相同，代碼清單 A 中的 DML 陳述句在 MySQL 中執行時，需要將 ① 部分更改為“START TRANSACTION;”，在 Oracle 和 DB2 中執行時，無需用到 ① 的部分（請洗掉），

詳細內容請大家參考 事務 中的“創建事務”，

EmpSkills 表中保存了某個系統公司員工所掌握的技術資訊，例如，從該表中我們可以了解到相田掌握了 Oracle、UNIX、Java、C# 這 4 種技術，

下面我們來思考一下如何從該表中選取出掌握了 Skills 表中所有 3 個領域的技術的員工吧（代碼清單 B），

代碼清單 B　選取出掌握所有 3 個領域的技術的員工

SELECT DISTINCT emp
 FROM EmpSkills ES1
WHERE NOT EXISTS
       (SELECT skill
          FROM Skills
        EXCEPT
        SELECT skill
          FROM EmpSkills ES2
         WHERE ES1.emp = ES2.emp);

這樣我們就得到了包含相田和神崎 2 人的結果，雖然平井也掌握了 Orcale 和 UNIX，但很可惜他不會使用 Java，因此沒有選取出來，

執行結果（關系除法中的商）

emp
------
神崎
相田

這樣的結果滿足了除法運算的基本規則，肯定有讀者會產生這樣的疑問：“到底上述運算中什么地方是除法運算呢？”實際上這和數值的除法既相似又有所不同，大家從與除法相對的乘法運算的角度去思考就能得到答案了，

除法和乘法是相輔相成的關系，除法運算的結果（商）乘以除數就能得到除法運算前的被除數了，例如對于 20÷4 = 5 來說，就是 5(商)×4(除數) = 20( 被除數)（圖 A），

關系除法中這樣的規則也是成立的，通過商和除數相乘，也就是交叉聯結，就能夠得到作為被除數的集合了 ^[3]，

如上所述，除法運算是集合運算中最復雜的運算，但是其在實際業務中的應用十分廣泛，因此希望大家能在達到中級以上水平時掌握其使用方法，此外，想要詳細了解 SQL 中除法運算實作方法的讀者，可以參考《SQL進階教程》中的 1-4 節和 1-7 節，

請參閱

• 表的加減法
• SQL 聯結

（完）

標籤：SQL Server

上一篇：SQL UNION（并集）、INTERSECT（交集）、EXCEPT（差集）

下一篇：mysql與事務、恢復相關的引數決議