1. 內(nèi)連接

內(nèi)連接：合并具有同一列的兩個以上的表的行，結(jié)果集中不包含一個表與另一個表不匹配的行。

說人話就是，查詢結(jié)果只包含它們匹配的行，不匹配的就不要了。

【例子】查詢員工編號 employee_id 和其對應(yīng)的部門名稱 department_name 。其中部門名稱 department_name 只在部門表 departments 中，部門表 departments 如下圖所示：

員工表 employees 和部門表 departments 通過部門編號 department_id 匹配連接起來。查詢代碼如下所示：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp, departments dept
WHERE emp.`department_id` = dept.`department_id`;

查詢結(jié)果：

這里返回了 106 條記錄，但員工表 employees 總共是有107條記錄的，還少了 1 個人。原因是在員工表 employees 中，有一個員工的部門編號 department_id 為 (NULL) ，如下圖所示：

而部門表 departments 中卻沒有值為 (NULL) 的部門編號 department_id ，因此這一行不匹配的數(shù)據(jù)就被丟棄不顯示了。如下圖所示，內(nèi)連接只包含兩個表匹配的行，即下圖中兩圓相交的部分：

這種連接方式稱作內(nèi)連接。

2.外連接

外連接：合并具有同一列的兩個以上的表的行，結(jié)果集中除了包含一個表與另一個表匹配的行之外，還查詢到了左表或右表中不匹配的行。

外連接又分為以下三類：

左外連接：

兩個表在連接過程中除了返回滿足連接條件的行以外，還返回左表中不滿足條件的行。如下圖中，左外連接就是左邊一整個圓。

右外連接：

兩個表在連接過程中除了返回滿足連接條件的行以外，還返回右表中不滿足條件的行。如下圖中，右外連接就是右邊一整個圓。

滿外連接：

兩個表在連接過程中除了返回滿足連接條件的行以外，還返回左表和右表中不滿足條件的行。如下圖中，滿外連接就是兩個圓所有部分。

【例子】根據(jù)部門編號 department_id ，查詢員工表 employees 中的所有員工編號 employee_id 和部門表 departments 中其對應(yīng)的部門名稱 department_name 。

【分析】凡是題目中出現(xiàn)要求查詢 所有 的字眼時，都要打起十二分精神，這說明需要我們使用外連接查詢。實現(xiàn)外連接可使用SQL92和SQL99兩種語法，詳見[5.9 常用的SQL標(biāo)準(zhǔn)](# 5.9 常用的SQL標(biāo)準(zhǔn)) 。由于左表員工表 employees 共有 107 條數(shù)據(jù)，而右表和左表匹配的數(shù)據(jù)僅有106條，需要使用左外連接。

【SQL92語法實現(xiàn)外連接】使用 (+) 。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp, departments dept
WHERE emp.`department_id` = dept.`department_id`(+);

查詢結(jié)果：報錯

這是因為MySQL不支持SQL92語法的外連接操作。但是Oracle是支持的。所以沒有白學(xué)。MySQL只支持SQL99語法來實現(xiàn)多表查詢。

3. SQL99語法實現(xiàn)多表查詢

SQL99是指SQL在1999年頒布的SQL語法標(biāo)準(zhǔn)規(guī)范。盡管在之后發(fā)布了一系列新的SQL標(biāo)準(zhǔn)，但在學(xué)習(xí)MySQL的過程中，主要掌握SQL99和SQL92就已經(jīng)足夠。從這節(jié)開始，MySQL的學(xué)習(xí)就算翻了半篇了，因為這一節(jié)之前都是SQL92語法，從這節(jié)開始，就專為SQL99語法。

SQL99語法使用 JOIN...ON 的方式實現(xiàn)多表查詢，且可以同時實現(xiàn)內(nèi)連接和三種外連接。MySQL是支持這種方式的。

3.1 SQL99實現(xiàn)內(nèi)連接

【例子：三表查詢】查詢員工的員工編號 employee_id 、 姓名 last_name 、部門名稱 department_name 和所在城市 city 。

【分析】這個需求需要 3 張表共同查詢。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, loc.`city`
FROM employees emp JOIN departments dept
ON emp.`department_id` = dept.`department_id`
JOIN locations loc
ON dept.`location_id` = loc.`location_id`;

SQL99語法就是加一張表，就 JOIN 一張表，并在 ON 后加連接條件。注意，這里的 JOIN 前面還省略了表示內(nèi)連接的關(guān)鍵字 INNER ，在使用內(nèi)連接時可以忽略。即代碼還可以寫成完整形式：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, loc.`city`
FROM employees emp INNER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
JOIN locations loc
ON dept.`location_id` = loc.`location_id`;

查詢結(jié)果：

3.2 SQL99語法實現(xiàn)外連接

3.2.1 左外連接

【例子】根據(jù)部門編號 department_id ，查詢員工表 employees 中的所有員工編號 employee_id 和部門表 departments 中其對應(yīng)的部門名稱 department_name 。

【分析】由于左表是員工表 employees ，有107條數(shù)據(jù)；而右表是部門表 departments ，有27條數(shù)據(jù)。題目要求是返回所有員工的107條查詢結(jié)果，因此這里使用左外連接。SQL99實現(xiàn)左連接接很簡單，只需要在 JOIN 前加上兩個關(guān)鍵字 LEFT OUTER 即可表示左外連接。如下代碼所示：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

其中，OUTER 可以省略，即寫成：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

查詢結(jié)果：

3.2.2 右外連接

舉一反三地，右外連接就是在 OUTER JOIN 前加一個關(guān)鍵字 RIGHT 。

SELECT emp.`employeed/master/img/d`;

查詢結(jié)果：

查詢結(jié)果有122條記錄，這怎么解釋呢？再回想一下右外連接的定義：

兩個表在連接過程中除了返回滿足連接條件的行以外，還返回右表中不滿足條件的行。如下圖中，右外連接就是右邊一整個圓。

就不難理解，因為右表部是沒有人的。而左、右表匹配的數(shù)據(jù)有106條 (兩圓相交部分) ，因此一共就有 106 + 16 = 122 106+16=122 106+16=122 條記錄。如下圖所示：

這個例子能更好地幫助我們理解右外連接。

3.2.3 滿外連接

舉一反三地，滿外連接就是在 OUTER JOIN 前加一個關(guān)鍵字 FULL 。但很不幸，MySQL不支持SQL99的滿外連接語法，Oracle是支持的。

我們需要使用別的方法實現(xiàn)MySQL中的滿外連接，詳見4.6 滿外連接 。

4.總結(jié)：七種SQL JOINS的實現(xiàn)

在開始本節(jié)之前，需要您了解SQL的 UNION 和 UNION ALL 的定義和實現(xiàn)。如果需要了解，可以閱讀這篇博文：《MySQL中 UNION 并的使用》。

4.1 內(nèi)連接

根據(jù)部門編號 department_id ，查詢員工表 employees 中的員工編號 employee_id 和部門表 departments 中其對應(yīng)的部門名稱 department_name 。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

查詢結(jié)果：

4.2 左外連接

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

查詢結(jié)果：

4.3 右外連接

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

4.4 第四種JOIN

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL;

查詢結(jié)果：

作用是把員工表 employees 中，部門編號 department_id 為 (NULL) 的那一個員工查詢出來了，如下圖所示：

4.5 第五種JOIN

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, emp.`department_id`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

查詢結(jié)果：

4.6 滿外連接

由于MySQL不支持SQL99語法的滿外連接。因此，我們的實現(xiàn)方式就是求

4.2 左外連接 和 4.5 第五種JOIN 的并 UNION ALL 即可；或者求4.3 右外連接 和 4.4 第四種JOIN 的并 UNION ALL 也行，都是一樣的效果。

方法一

方法二

# 方法一
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_id`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

# 方法二
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`employee_id` = dept.`department_id`
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`employee_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL;

查詢結(jié)果：

4.7 第七種JOIN

實現(xiàn)下面這個操作只需要把 4.4 第四種JOIN 和 4.5 第五種JOIN 求 UNION ALL 即可。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

查詢結(jié)果：

到此這篇關(guān)于MySQL內(nèi)連接和外連接及七種SQL JOINS的實現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL內(nèi)連接和外連接內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論