MySQL內連接和外連接及七種SQL?JOINS的實現

MySQL內連接、外連接及七種實現

更新時間：2023年3月23日 15:52:02 作者：子木君

本文主要介紹Mysql中內連接和外連接的區別以及七種實現方式。 相信看完這篇文章你就能夠了解SQL內、外連接的多表查詢了。 有需要的朋友可以參考以下

目錄

1. 內連接

內連接：將兩個或多個表中具有同一列的行進行合并，并且結果集中不包含一個表中與另一個表不匹配的行。

換句話說，查詢結果只包含匹配的行，不匹配的行將被丟棄。

【示例】查詢員工編號及其對應的部門名稱。 其中，部門名稱僅存在于部門表中，部門表如下圖所示：

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

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

搜索結果：

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

但部門表中不存在值為（NULL）的部門號，因此該行不匹配的數據將被丟棄，不顯示。 如下圖所示，內連接只包含兩個表的匹配行，也就是下圖中兩個圓相交的部分：

這種類型的連接稱為內部連接。

2. 外連接

外連接：合并兩個或多個表中具有同一列的行。 結果集除了查詢一張表中與另一表匹配的行外，還會查詢左表或右表中不匹配的行。

外連接分為三種類型：

左外連接：

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

右外連接：

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

完全外連接：

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

【示例】根據部門編號，查詢員工表中所有員工編號及其在部門表中對應的部門名稱。

【分析】每當有要求查詢標題中的所有單詞時，我們必須支付12分，這意味著我們需要使用外連接查詢。 可以使用SQL92和SQL99兩種語法來實現外連接，具體參見[5.9常用SQL標準](#5.9常用SQL標準)。 由于左表中表有107條數據，而右表和左表之間只有106條數據匹配，所以需要進行左外連接。

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

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

查詢結果：錯誤

這是因為MySQL不支持SQL92語法的外連接操作。 但它是支持的。 所以說，學習沒有白費。 MySQL僅支持SQL99語法來實現多表查詢。

3、SQL99語法實現多表查詢

SQL99是指SQL在1999年發布的SQL語法標準規范，雖然后來發布了一系列新的SQL標準，但是在學習MySQL的過程中，主要掌握SQL99和SQL92就足夠了。 從本節開始，MySQL的學習就翻了一半，因為本節之前是SQL92語法，從本節開始，專門學習SQL99語法。

SQL99語法使用JOIN...ON實現多表查詢，可以同時實現內連接和三種外連接。 MySQL 支持這種方法。

3.1 SQL99實現內連接

【例：三表查詢】查詢員工的工號、姓名、部門名稱、城市。

【分析】這個需求需要三張表一起查詢。

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前面省略了表示內連接的關鍵字INNER，使用內連接時可以忽略。 也就是說，代碼也可以寫成完整形式：

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

搜索結果：

3.2 SQL99語法實現外連接 3.2.1 左外連接

【示例】根據部門編號，查詢員工表中所有員工編號及其在部門表中對應的部門名稱。

【分析】由于左表是員工表，所以有107條數據； 右表是部門表，有27條數據。 題目要求是返回所有員工的107條查詢結果，所以這里使用了左外連接。 在SQL99中實現左連接非常簡單，只需在JOIN前添加兩個關鍵字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`;

搜索結果：

3.2.2 右外連接

以此類推，右外連接就是在OUTER JOIN之前加一個關鍵字RIGHT。

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

搜索結果：

查詢結果有122條記錄，如何解釋？ 回想一下右外連接的定義：

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

這并不難理解，因為右邊沒有人。 左右表（兩個圓的交點）有106條匹配數據，所以總共有106 + 16 = 122 106+16=122 106+16=122條記錄。 如下所示：

這個例子可以更好的幫助我們理解右外連接。

3.2.3 全外連接

打個比方，全外連接就是在OUTER JOIN之前加一個關鍵字FULL。 但不幸的是，MySQL不支持SQL99的完整外連接語法，但它是支持的。

在MySQL中，我們需要使用其他方法來實現全外連接，具體請參見4.6 全外連接。

4.總結：七種SQL JOINS的實現

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

4.1 內連接

根據部門編號，查詢員工表中的員工編號以及部門表中對應的部門名稱。

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

搜索結果：

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

搜索結果：

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;

搜索結果：

功能是查詢員工表中部門號為（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;

搜索結果：

4.6 全外連接

因為MySQL不支持SQL99語法中的全外連接。因此，我們的實現是求

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;

搜索結果：

4.7 第七種JOIN

要實現下面的操作，只需要找到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;

搜索結果：

至此，這篇關于MySQL內、外連接以及七種SQL JOINS的實現的文章就介紹到這里了。 更多關于MySQL內、外連接的知識請搜索之前的文章 - 個人文章 - 思否