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

来源:https://www.cnblogs.com/vin-c/archive/2022/06/15/16378161.html
-Advertisement-
Play Games

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


目錄

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

本文重點

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

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

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

一、什麼是聯結

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

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

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

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

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

聯結的圖示

圖 5　聯結的圖示

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

使用 ON 進行兩表加法運算（和集）的圖示

圖 6　使用 ON 進行兩表加法運算（和集）的圖示

聯結條件也可以使用“=”來記述。在語法上，還可以使用 <= 和 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;

特定的 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/

（完）

  1. 根據表中數據的不同，也存在行數不發生變化的情況。 ↩︎

  2. 在 FROM 子句中使用表的別名時，像 Product AS P 這樣使用 AS 是標準 SQL 正式的語法。但是在 Oracle 中使用 AS 會發生錯誤。因此，在 Oracle 中使用時，需要註意不要在 FROM 子句中使用 AS。 ↩︎


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 每次開機docker-desktop都啟動失敗了
    目錄 一、前景回顧 二、物理地址、線性地址和虛擬地址 三、記憶體為什麼要分頁 四、一級頁表 五、二級頁表 一、前景回顧 前面我們說到，保護模式下有著三大特點：地址映射、特權級和分時機制。從我的學習角度來說，我認為地址映射這一塊的知識點尤為繁雜，所以會花費相對比較多的時間來講述，話不多說，開整。 二、物 ...
  • Linux Accounting(中文翻譯): Delay Accounting
    Delay accounting 延時統計 任務在執行時等待某個內核資源會意外遇到延遲，例如可運行的任務正在等待空閑CPU。 per-task的延時統計功能測量下列情況下任務經歷的延遲： 正在等待CPU，waiting for a CPU (while being runnable) 同步塊I/O的 ...
  • 嘗鮮少代碼高性能的Svelte框架
    為什麼要使用Svelte Svelte 是一種全新的構建用戶界面的方法。傳統框架（如 React 和 Vue）在瀏覽器中完成大部分工作，而 Svelte 將這些工作轉移到構建應用程式時發生的編譯步驟。 Svelte 沒有使用虛擬 DOM diffing 之類的技術，而是編寫了在應用程式狀態發生變化時 ...
  • vite+vue3項目最佳起始點（保姆級）
    一 、通過雲開發平臺快速創建初始化應用 1.創建相關應用模版請參考鏈接：去中心化的前端構建工具 — Vite 2.完成創建後就可以在github中查看到新增的Vite倉庫 二 、 本地編寫 Vite後臺項目最佳起始點 1.將應用模版克隆到本地 首先假定你已經安裝了Git、node，沒有安裝請移步no ...
  • [自製操作系統] 第06回 邁入保護模式
    目錄 一、前景回顧 二、A20地址線 三、全局描述符表 四、CR0寄存器的PE位 五、邁入保護模式 六、測試 一、前景回顧 上回我們說到，保護模式下有著三大特點：地址映射、特權級和分時機制。本來接下來是要向這三點一一發起進攻，不過我們首先需要先邁入保護模式中，不然在實模式下講解保護模式顯得不倫不類。 ...
  • 什麼是 SQL 視窗函數（分析函數）
    本文介紹什麼是 SQL 視窗函數，視窗函數可以進行排序、生成序列號等一般的 聚合函數 無法實現的高級操作。 本文重點 視窗函數可以進行排序、生成序列號等一般的聚合函數無法實現的高級操作。 理解 PARTITION BY 和 ORDER BY 這兩個關鍵字的含義十分重要。 一、什麼是視窗函數 視窗函數 ...
  • 【MySQL】MySQL的安裝、卸載、配置、登陸和退出
    1 MySQL安裝 安裝環境:Win10 64位 軟體版本:MySQL 5.7.24 解壓版 1.1 下載 https://downloads.mysql.com/archives/community/ 點開上面的鏈接就能看到如下界面： 選擇選擇和自己系統位數相對應的版本點擊右邊的Download， ...
  • 緩存穿透，緩存雪崩，緩存擊穿
    緩存穿透，緩存雪崩，緩存擊穿 本文整理自黑馬程式員相關資料 緩存穿透 緩存穿透是指客戶端請求的數據在緩存中和資料庫中都部存在，這樣緩存永遠不會生效。因此這些請求都會穿過緩存，最終請求到資料庫上，對資料庫造成非常大的壓力。 常見的解決方案有兩種： 緩存空對象：當緩存和資料庫都沒有請求的數據時，將查詢的 ...
一周排行
    -Advertisement-
    Play Games
  • Dapr Outbox 執行流程
    Dapr Outbox 是1.12中的功能。 本文只介紹Dapr Outbox 執行流程，Dapr Outbox基本用法請閱讀官方文檔 。本文中appID=order-processor，topic=orders 本文前提知識：熟悉Dapr狀態管理、Dapr發佈訂閱和Outbox 模式。 Outbo ...
  • Advanced .Net Debugging 7：托管堆與垃圾收集
    引言 在前幾章我們深度講解了單元測試和集成測試的基礎知識，這一章我們來講解一下代碼覆蓋率,代碼覆蓋率是單元測試運行的度量值,覆蓋率通常以百分比表示，用於衡量代碼被測試覆蓋的程度，幫助開發人員評估測試用例的質量和代碼的健壯性。常見的覆蓋率包括語句覆蓋率（Line Coverage）、分支覆蓋率（Bra ...
  • C#S7.NET實現西門子PLCDB塊數據採集的完整步驟
    前言 本文介紹瞭如何使用S7.NET庫實現對西門子PLC DB塊數據的讀寫，記錄了使用電腦模擬，模擬PLC，自至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1.Windows環境下鏈路層網路訪問的行業標準工具（WinPcap_4_1_3.exe）下載鏈接：http ...
  • WPF控制項：密碼框綁定MVVM
    從依賴倒置原則（Dependency Inversion Principle, DIP）到控制反轉（Inversion of Control, IoC）再到依賴註入（Dependency Injection, DI）的演進過程，我們可以理解為一種逐步抽象和解耦的設計思想。這種思想在C#等面向對象的編 ...
  • Python中的私有屬性與私有方法
    關於Python中的私有屬性和私有方法 Python對於類的成員沒有嚴格的訪問控制限制，這與其他面相對對象語言有區別。關於私有屬性和私有方法，有如下要點： 1、通常我們約定，兩個下劃線開頭的屬性是私有的（private）。其他為公共的（public）； 2、類內部可以訪問私有屬性（方法）； 3、類外 ...
  • C++ 訪問說明符詳解：封裝數據，控制訪問，提升安全性
    C++ 訪問說明符 訪問說明符是 C++ 中控制類成員（屬性和方法）可訪問性的關鍵字。它們用於封裝類數據並保護其免受意外修改或濫用。 三種訪問說明符： public：允許從類外部的任何地方訪問成員。 private：僅允許在類內部訪問成員。 protected：允許在類內部及其派生類中訪問成員。 示 ...
  • static_cast與dynamic_cast到底是什麼？
    寫這個隨筆說一下C++的static_cast和dynamic_cast用在子類與父類的指針轉換時的一些事宜。首先，【static_cast，dynamic_cast】【父類指針，子類指針】，兩兩一組，共有4種組合：用 static_cast 父類轉子類、用 static_cast 子類轉父類、使用 ...
  • C語言數據結構：雙向鏈表的增刪操作
    /******************************************************************************************************** * * * 設計雙向鏈表的介面 * * * * Copyright (c) 2023-2 ...
  • @ComponentScan註解的實現，Spring掃描包的過程
    相信接觸過spring做開發的小伙伴們一定使用過@ComponentScan註解 @ComponentScan("com.wangm.lifecycle") public class AppConfig { } @ComponentScan指定basePackage，將包下的類按照一定規則註冊成Be ...
  • opensips開啟python支持
    操作系統 ：CentOS 7.6_x64 opensips版本： 2.4.9 python版本：2.7.5 python作為腳本語言，使用起來很方便，查了下opensips的文檔，支持使用python腳本寫邏輯代碼。今天整理下CentOS7環境下opensips2.4.9的python模塊筆記及使用 ...
所有分類