首頁 > 軟體

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

2023-03-23 22:04:42

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的 UNIONUNION 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的實現的文章就介紹到這了,更多相關MySQL內連線和外連線內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!


IT145.com E-mail:sddin#qq.com