SQL 如何使用內聯結、外聯結和交叉聯結

本文介紹 SQL 如何使用內聯結（INNER JOIN）、外聯結（OUTER JOIN）和交叉聯結（CROSS JOIN）。簡單來說，就是將其他表中的列添加過來，進行“添加列”的運算。本文重點聯結（JOIN）就是將其他表中的列添加過來，進行“添加列”的集合運算。UNION 是以行（縱向）為單位進 ...

一、什麼是聯結
二、內聯結——INNER JOIN
三、外聯結——OUTER JOIN
- 3.1 外聯結要點 ① ——選取出單張表中全部的信息
- 3.2 外聯結要點 ② ——每張表都是主表嗎？
四、3 張以上的表的聯結
五、交叉聯結——CROSS JOIN
六、聯結的特定語法和過時語法

本文介紹 SQL 如何使用內聯結（INNER JOIN）、外聯結（OUTER JOIN）和交叉聯結（CROSS JOIN）。簡單來說，就是將其他表中的列添加過來，進行“添加列”的運算。

本文重點

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

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

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

一、什麼是聯結

在 SQL 如何進行並集、交集、差集等集合運算中，我們學習了 UNION 和 INTERSECT 等集合運算，這些集合運算的特征就是以行方向為單位進行操作。

通俗地說，就是進行這些集合運算時，會導致記錄行數的增減。使用 UNION 會增加記錄行數，而使用 INTERSECT 或者 EXCEPT 會減少記錄行數 ^[1]。

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

本文將要學習的聯結（JOIN）運算，簡單來說，就是將其他表中的列添加過來，進行“添加列”的運算（圖 5）。該操作通常用於無法從一張表中獲取期望數據（列）的情況。

截至目前，我們介紹的示例基本上都是從一張表中選取數據，但實際上，期望得到的數據往往會分散在不同的表之中。使用聯結就可以從多張表（3 張以上的表也沒關係）中選取數據了。

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

二、內聯結——INNER JOIN

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

本例中我們會繼續使用 Product 表和什麼是 SQL 謂詞創建的 ShopProduct 表。下麵我們再來回顧一下這兩張表的內容。

表 1 Product（商品）表

product_id（商品編號）	product_name（商品名稱）	product_type（商品種類）	sale_price（銷售單價）	purchase_price（進貨單價）	regist_date（登記日期）
0001	T 恤衫	衣服	1000	500	2009-09-20
0002	打孔器	辦公用品	500	320	2009-09-11
0003	運動 T 恤	衣服	4000	2800
0004	菜刀	廚房用具	3000	2800	2009-09-20
0005	高壓鍋	廚房用具	6800	5000	2009-01-15
0006	叉子	廚房用具	500		2009-09-20
0007	擦菜板	廚房用具	880	790	2008-04-28
0008	圓珠筆	辦公用品	100		2009-11-11

表 2 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

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

表 3 兩張表及其包含的列

	Product	ShopProduct
商品編號	○	○
商品名稱	○
商品種類	○
銷售單價	○
進貨單價	○
登記日期	○
商店編號		○
商店名稱		○
數量		○

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

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 後面的聯結條件。

ON SP.product_id = P.product_id

我們可以在 ON 之後指定兩張表聯結所使用的列（聯結鍵），本例中使用的是商品編號（product_id）。

也就是說，ON 是專門用來指定聯結條件的，它能起到與 WHERE 相同的作用。需要指定多個鍵時，同樣可以使用 AND、OR。

在進行內聯結時 ON 子句是必不可少的（如果沒有 ON 會發生錯誤），並且 ON 必須書寫在 FROM 和 WHERE 之間。

法則 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.4 內聯結和 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）的圖示

shop_id（編號）	shop_name（商店名稱）	product_id（商品編號）	product_name（商品名稱）	sale_price（銷售單價）
000A	東京	0001	T 恤衫	1000
000A	東京	0002	打孔器	500
000A	東京	0003	運動 T 恤	4000
000B	名古屋	0002	打孔器	500
000B	名古屋	0003	運動 T 恤	4000
000B	名古屋	0004	菜刀	3000
000B	名古屋	0006	叉子	500
000B	名古屋	0007	擦菜板	880
000C	大阪	0003	運動 T 恤	4000
000C	大阪	0004	菜刀	3000
000C	大阪	0006	叉子	500
000C	大阪	0007	擦菜板	880
000D	福岡	0001	T 恤衫	1000

三、外聯結——OUTER JOIN

內聯結之外比較重要的就是 外聯結（OUTER JOIN） 了。我們再來回顧一下前面的例子。

在前例中，我們將 Product 表和 ShopProduct 表進行內聯結，從兩張表中取出各個商店銷售的商品信息。其中，實現“從兩張表中取出”的就是聯結功能。

外聯結也是通過 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 表是主表。

我們還可以像代碼清單 7-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（庫存商品）表

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

創建該表及插入數據的 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 中執行時，無需用到 ① 的部分（請刪除）。

詳細內容請大家參考什麼是 SQL 事務中的“創建事務”。

下麵我們從上表中取出保存在 P001 倉庫中的商品數量，並將該列添加到代碼清單 11 所得到的結果中。

聯結方式為內聯結（外聯結的使用方法完全相同），聯結鍵為商品編號（product_id）（代碼清單 14）。

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

代碼清單 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）。其實這種聯結在實際業務中並不會使用（筆者使用這種聯結的次數也屈指可數），那為什麼還要在這裡進行介紹呢？這是因為交叉聯結是所有聯結運算的基礎。

交叉聯結本身非常簡單，但是其結果有點麻煩。下麵我們就試著將 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 條記錄。

可能這時會有讀者想起前面我們在 SQL 如何進行並集、交集、差集等集合運算中提到過集合運算中的乘法會在本文中進行詳細學習，這就是上面介紹的交叉聯結。

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

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

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

之前我們學習的內聯結和外聯結的語法都符合標準 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

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

專欄

關係除法

我們在 SQL 如何進行並集、交集、差集等集合運算和本文中我們學習了以下 4 個集合運算符。

UNION（並集）

EXCEPT（差集）

INTERSECT（交集）

CROSS JOIN（笛卡兒積）

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

難道集合運算中沒有除法嗎？當然不是，除法運算是存在的。

集合運算中的除法通常稱為關係除法。關係是數學領域中對錶或者視圖的稱謂，但是並沒有定義像 UNION 或者 EXCEPT 這樣專用的運算符。

如果要定義，估計應該是 DIVIDE（除）吧。但截至目前並沒有 DBMS 使用這樣的運算符。

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

我們使用表 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;

skill
Oracle
UNIX
Java

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

特定的 SQL

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

詳細內容請大家參考什麼是 SQL 事務中的“創建事務”。

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）。

圖 A　除法運算和乘法運算相輔相成的關係圖

關係除法中這樣的規則也是成立的。通過商和除數相乘，也就是交叉聯結，就能夠得到作為被除數的集合了（雖然不能恢覆成完整的被除數，但是這裡我們也不再追究了。）。

如上所述，除法運算是集合運算中最複雜的運算，但是其在實際業務中的應用十分廣泛，因此希望大家能在達到中級以上水平時掌握其使用方法。

原文鏈接：https://www.developerastrid.com/sql/sql-inner-outer-cross-join/

（完）

根據表中數據的不同，也存在行數不發生變化的情況。 ↩︎
在 FROM 子句中使用表的別名時，像 Product AS P 這樣使用 AS 是標準 SQL 正式的語法。但是在 Oracle 中使用 AS 會發生錯誤。因此，在 Oracle 中使用時，需要註意不要在 FROM 子句中使用 AS。 ↩︎