SQL Join2

1. 前言

上一小節(jié)中介紹了連接操作中內(nèi)連接，本小節(jié)，我們將學(xué)習(xí)外連接。

外連接有些許不同，它并不要求兩張表中的記錄都能夠匹配，即使沒有匹配到也會保留數(shù)據(jù)，被保留全部數(shù)據(jù)的表被稱為保留表。

外連接可以根據(jù)保留表來進一步分為：左外連接（左邊的表數(shù)據(jù)會被保留），右外連接（右邊的表數(shù)據(jù)會被保留）和全連接（兩邊的表均被保留）。

外連接沒有隱式的連接方式，必須通過 Join 與 On 顯式的指定連接方式和連接條件。

本小節(jié)測試數(shù)據(jù)如下，請先在數(shù)據(jù)庫中執(zhí)行：

DROP TABLE IF EXISTS imooc_class;
CREATE TABLE imooc_class
(
  id int PRIMARY KEY,
  class_name varchar(20)
);
INSERT INTO imooc_class(id,class_name) VALUES(1,'SQL必知必會'), (2,'C語言入門'),
(3,'JAVA高效編程'),(4,'JVM花落知多少');

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  class_id int references imooc_class(id)
);
INSERT INTO imooc_user(id,username,class_id) VALUES(1,'pedro', 1), (2,'peter', 1),
(3,'faker', 2), (4,'lucy', 4),(5,'jery', NULL);

說明： 我們分別新建了 imooc_class 表——課程表，和 imooc_user 表——用戶表；其中 imooc_user 表中的 class_id 作為外鍵指向 imooc_class 的主鍵id；若 class_id 為 NULL 則表示該用戶暫時還未加入任何課程，否則 class_id 表示用戶參加課程的 id 。

2. 左外連接

左外連接（Left Outer Join），簡稱左連接（Left Join）;若 A 和 B 兩表進行左外連接，會在結(jié)果中包含左表（即表 A）的所有記錄，即使那些記錄在右表B 沒有符合連接條件相應(yīng)的匹配記錄，未匹配的記錄會給予 NULL 填充。

維恩圖表示如下：

2.1 例1 左連接查詢

請書寫 SQL 語句，查詢imooc_user表中每一個用戶姓名和該用戶所參加課程的名稱。

分析：

由題干可知，查詢的結(jié)果應(yīng)是用戶姓名以及參加課程，因此 imooc_user 表應(yīng)該作為保留表；考慮到使用左連接，所以 imooc_user 表是左表，imooc_class 表作為右表。

語句：

整理可得語句如下：

SELECT username,class_name FROM imooc_user LEFT OUTER JOIN imooc_class ON imooc_user.class_id = imooc_class.id;

結(jié)果如下：

+----------+---------------+
| username | class_name    |
+----------+---------------+
| pedro    | SQL必知必會    |
| peter    | SQL必知必會    |
| faker    | C語言入門      |
| lucy     | JVM花落知多少  |
| jery     | <null>        |
+----------+---------------+

3. 右外連接

右外連接（Right Outer Join），簡稱右連接（Right Join）;若 A 和 B 兩表進行右外連接，會在結(jié)果中包含右表（即表 B）的所有記錄，即使那些記錄在左表 A 中沒有符合連接條件相應(yīng)的匹配記錄，未匹配的記錄會給予 NULL 填充。

維恩圖表示如下：

3.1 例2 右連接

請書寫SQL語句，查詢imooc_user表中每一門課程和該課程下參與的用戶。

分析：

由題干可知，imooc_class 應(yīng)作為保留表，考慮到使用右連接，因此 imooc_class 是右表；連接條件是外鍵 class_id。

語句：

整理可得語句如下：

SELECT class_name,username FROM imooc_user RIGHT OUTER JOIN imooc_class ON imooc_class.id = imooc_user.class_id;

結(jié)果如下：

+---------------+----------+
| class_name    | username |
+---------------+----------+
| SQL必知必會    | pedro    |
| SQL必知必會    | peter    |
| C語言入門      | faker    |
| JVM花落知多少  | lucy     |
| JAVA高效編程   | <null>   |
+---------------+----------+

提示：SQLite 是不支持右連接的，卻可以通過更換保留表的位置用左連接來模擬右連接。

4. 全連接

全連接是左、右外連接的并集。查詢結(jié)果會包含被連接表的所有記錄，若缺少匹配的記錄，將以 NULL 填充。

維恩圖表示如下：

4.1 例3 全連接

請書寫 SQL 語句，返回imooc_class和imooc_user表的全連接。

SELECT * FROM imooc_class FULL OUTER JOIN imooc_user ON imooc_class.id = imooc_user.class_id;

查詢結(jié)果如下：

+--------+----------+----------+--------+---------------+
| id     | username | class_id | id     | class_name    |
+--------+----------+----------+--------+---------------+
| 1      | pedro    | 1        | 1      | SQL必知必會   |
| 2      | peter    | 1        | 1      | SQL必知必會   |
| 3      | faker    | 2        | 2      | C語言入門     |
| 4      | lucy     | 4        | 4      | JVM花落知多少 |
| 5      | jery     | <null>   | <null> | <null>       |
| <null> | <null>   | <null>   | 3      | JAVA高效編程  |
+--------+----------+----------+--------+---------------+

一些數(shù)據(jù)庫，比如 MySQL 是不支持全連接的，但可以通過左、右外連接的并集（Union）來模擬實現(xiàn)，如下：

SELECT *
FROM imooc_user
LEFT JOIN imooc_class 
ON imooc_class.id = imooc_user.class_id
UNION
SELECT *
FROM imooc_user
RIGHT JOIN imooc_class
ON imooc_class.id = imooc_user.class_id
WHERE imooc_user.class_id IS NULL;

提示： SQLite 不支持右連接和全連接，也可以通過左連接來模擬右連接，從而實現(xiàn)全連接。

5. 個人經(jīng)驗

• 外連接是連接操作的重點，請多多聯(lián)系并熟練掌握。
• 左、右連接可以通過更換表的位置來實現(xiàn)同等作用，因此一些數(shù)據(jù)庫只支持左連接。
• 全連接的使用較少，所以有些數(shù)據(jù)庫會不支持，但是使用左、右連接也可以達到相同效果。