MySQL內連線、外接與SQL JOINS的實作方法是什麼

1. 內連接

內連接：合併具有同一列的兩個以上的表的行，結果集中不包含一個表與另一個表不符合的行。

說人話就是，查詢結果只包含它們相符的行，不符合的就不要了。

【範例】查詢員工編號 employee_id 和其對應的部門名稱 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`;

查詢結果：

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

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

#這種連接方式稱為內連接。

2.外連接

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

外連接又分為以下三類：

左外連接：

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

右外連接：

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

滿外連接：

在表格連接過程中，除了傳回符合連接條件的行之外，還會傳回左表和右表中不符合條件的行。如下圖中，滿外連接就是兩個圓所有部分。

【範例】根據部門編號department_id ，查詢員工表employees 中的所有員工編號employee_id 和部門表departments 中其對應的部門名稱department_name 。

【分析】凡是題目中出現要求查詢 所有 的字眼時，都要打起十二分精神，這說明需要我們使用外連接查詢。實作外部連接可使用SQL92和SQL99兩種語法，詳見[5.9 常用的SQL標準](# 5.9 常用的SQL標準) 。由於左表員工表 employees 共有 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語法的外接操作。但是Oracle是支援的。所以沒有白學。 MySQL只支援SQL99語法來實作多表查詢。
 

3. SQL99語法實作多表查詢

SQL99是指SQL在1999年頒布的SQL語法標準規格。儘管在之後發布了一系列新的SQL標準，但在學習MySQL的過程中，主要掌握SQL99和SQL92就已經足夠。從這段開始，MySQL的學習就算翻了半篇了，因為這一節之前都是SQL92文法，從這節開始，就專為SQL99文法。

SQL99语法使用 JOIN...ON 的方式实现多表查询，且可以同时实现内连接和三种外连接。MySQL是支持这种方式的。

3.1 SQL99实现内连接

【例子：三表查询】查询员工的员工编号 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 前面还省略了表示内连接的关键字 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 左外连接

【例子】根据部门编号 department_id ，查询员工表 employees 中的所有员工编号 employee_id 和部门表 departments 中其对应的部门名称 department_name 。

【分析】由于左表是员工表 employees ，有107条数据；而右表是部门表 departments ，有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的满外连接语法，Oracle是支持的。

我们需要使用别的方法实现MySQL中的满外连接，详见4.6 满外连接 。

4.总结：七种SQL JOINS的实现

在开始本节之前，需要您了解SQL的 UNION 和 UNION ALL 的定义和实现。如果需要了解，可以阅读这篇博文：《MySQL中 UNION 并的使用》。

4.1 内连接

根据部门编号 department_id ，查询员工表 employees 中的员工编号 employee_id 和部门表 departments 中其对应的部门名称 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`;

查询结果：

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;

查询结果：

作用是把员工表 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;

查询结果：

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的實作方法是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！