1，什么是多表查詢

多表查詢，也稱為關(guān)聯(lián)查詢，指兩個(gè)或更多個(gè)表一起完成查詢操作。

可進(jìn)行多表查詢的前提條件： 這些一起查詢的表之間是有關(guān)系的（一對(duì)一、一對(duì)多），它們之間一定是有關(guān)聯(lián)字段，如下圖：?jiǎn)T工表和部門表，這兩個(gè)表依靠“部門編號(hào)”進(jìn)行關(guān)聯(lián)，因此符合多表查詢的條件。

2，多表查詢的分類

連接查詢通過表之間的關(guān)聯(lián)條件，將多張表的數(shù)據(jù)合并輸出。根據(jù)匹配邏輯和結(jié)果集范圍，可分為以下類型：

• 等值連接和非等值連接；
• 自連接和非自連接 ；
• 內(nèi)連接和外連接；

接下來我們?cè)敿?xì)看一下這些不同種類多表查詢的定義和應(yīng)用。

3，等值連接和非等值連接

根據(jù)多表查詢的連接條件的類型可分為等值連接和非等值連接。

• 等值連接通過（=）運(yùn)算符進(jìn)行比較；
• 非等值連接通過其他運(yùn)算符進(jìn)行比較；

3.1，等值連接的定義及應(yīng)用

等值連接是最常見的一種連接類型，它基于兩個(gè)表之間的相等條件來連接記錄。這通常意味著連接條件中的兩個(gè)字段通過等于（=）操作符進(jìn)行比較。

我們根據(jù)前面介紹已知EMPLOTYEES表和DEPARTMENTS表滿足多表查詢的前提條件。當(dāng)我們有如下需求時(shí)：

需求：查詢每一位員工的employee_id和department_name。

注意： 如下圖所示，員工的employee_id位于EMPLOYEES表，而department_name字段位于DEPARTMENTS表。

此時(shí)正確的SQL語(yǔ)句如下：

SELECT employee_id,department_name
FROM employees,departments

# 兩個(gè)表的連接條件
WHERE employees.department_id = departments.department_id;

運(yùn)行結(jié)果如下：

此即為一個(gè)等值連接的應(yīng)用示例。

3.2，非等值連接的定義及應(yīng)用

非等值連接則不局限于等于（=）操作符，而是可能使用其他比較操作符（如>、<、>=、<=、<>等），或者通過表達(dá)式或函數(shù)來連接兩個(gè)表。

EMPLOYEES表中每個(gè)員工都有SALARY（工資）字段；而JOB_GRADES表中又對(duì)不同薪資范圍做了等級(jí)的劃分。

當(dāng)我們有如下需求時(shí)：

需求：查看員工的姓名、工資、工資等級(jí)

SQL語(yǔ)句如下：

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
# 非等值連接條件
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal

或者：

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
# # 非等值連接條件
WHERE e.salary >= j.lowest_sal AND e.salary<=j.highest_sal

運(yùn)行結(jié)果如下：

此即為一個(gè)非等值連接的應(yīng)用示例。

4，自連接和非自連接

根據(jù)多表查詢連接的表是否為同一張表可分為自連接和非自連接。（本節(jié)之前列舉的多表查詢例子連接的表為不同表，因此均為非自連接）

• 自連接指連接的表為同一張表；
• 非自連接連接的表不是同一張表；

4.1，自連接的定義及應(yīng)用

自連接是指同一張表與其自身進(jìn)行連接的操作。這種類型的連接通常用于處理具有層級(jí)關(guān)系的數(shù)據(jù)，比如在員工表中查找每個(gè)員工的直接上級(jí)。為了實(shí)現(xiàn)這一點(diǎn)，需要為同一個(gè)表賦予不同的別名，以便在查詢時(shí)區(qū)分不同的實(shí)例。

自連接對(duì)應(yīng)了表中自我引用的關(guān)系。如下圖員工表的例子所示，104號(hào)和105號(hào)員工的主管是103號(hào)員工（103號(hào)員工是一名員工，同時(shí)擔(dān)任主管）。

需求： 要查詢員工ID、員工姓名及其管理者ID和姓名

SQL語(yǔ)句如下：

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name

# 給同一張表起兩個(gè)別名，一份看作員工，一份看作管理者
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

查詢結(jié)果如下：

此即為一個(gè)自連接的示例。

4.2，非自連接的定義及應(yīng)用

非自連接是最常見的連接形式。非自連接和自連接相反，非自連接指的是不同表之間的連接，用于處理兩個(gè)或多個(gè)獨(dú)立表之間的數(shù)據(jù)關(guān)系。

由于4章節(jié)節(jié)之前列舉的多表查詢例子連接的表均為不同表，因此均為非自連接。此處不再贅述。

5，內(nèi)連接和外連接

根據(jù)多表查詢連接結(jié)果中是否包含未匹配的行可分為內(nèi)連接和外連接。

• 連接結(jié)果中不包含未匹配行即為內(nèi)連接；
• 連接結(jié)果中包含未匹配行即為外連接；

5.1，內(nèi)連接的定義及應(yīng)用

內(nèi)連接返回的是滿足連接條件的所有行的交集部分。 這意味著只有當(dāng)兩個(gè)表中存在相應(yīng)的匹配記錄時(shí)，這些記錄才會(huì)出現(xiàn)在結(jié)果集中。

需求：查詢員工ID及部門名

SQL語(yǔ)句如下：

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id

或者：

SELECT employee_id,department_name
# INNER JOIN表示內(nèi)連接（SQL99語(yǔ)法）
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

查詢結(jié)果如下：

此即為一個(gè)內(nèi)連接的簡(jiǎn)單例子。這段SQL語(yǔ)句的核心在于只把左表和右表中滿足連接條件的數(shù)據(jù)查出來了，此即為內(nèi)連接。比如：如果某員工的department_id為空，則不會(huì)出現(xiàn)在查詢得到的結(jié)果集中。

5.2，外連接的定義及應(yīng)用

外連接包括主表中的所有記錄，即使它們?cè)诹硪粋€(gè)表中沒有匹配項(xiàng)。

而外連接又分為左外連接、右外連接和全外連接。

• 左外連接會(huì)返回左表中的所有記錄以及右表中符合條件的記錄；
• 右外連接會(huì)返回右表中的所有記錄以及左表中符合條件的記錄；
• 全外連接則返回兩張表中的所有記錄，對(duì)于沒有匹配項(xiàng)的部分用NULL填充。

需求： 查詢所有的員工姓名、所在部門名信息

注意：提及所有的員工，說明是外連接。

SQL語(yǔ)句如下：

SELECT last_name,department_name
# LEFT OUTER JOIN 表示左外連接 ，以左表employees為基礎(chǔ)
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

或者：

SELECT last_name,department_name
# 省略O(shè)UTER，LEFT JOIN 也可表示左外連接 
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

運(yùn)行結(jié)果如下：

從查詢結(jié)果可以看到，結(jié)果包含左表中所有的記錄以及右表中符合條件的記錄。即使EMPLOYEES表內(nèi)存在一個(gè)員工的department_name為Null，經(jīng)過左外連接查詢后依然現(xiàn)實(shí)中查詢得到的結(jié)果集中。

此即為一個(gè)外連接的示例，具體而言是左外連接。接下來我們?cè)敿?xì)學(xué)習(xí)其中JOIN操作。

6，使用SQL語(yǔ)言實(shí)現(xiàn)七種JOIN操作（面試重點(diǎn)）

6.1，UNION和UNION ALL

• 使用UNION操作符可以返回兩個(gè)查詢的結(jié)果集的并集，去除重復(fù)記錄 ；
• 使用UNION ALL操作符可以返回兩個(gè)查詢的結(jié)果集的并集，對(duì)于兩個(gè)結(jié)果集的重復(fù)部分，不去重；
• 執(zhí)行UNION ALL語(yǔ)句時(shí)所需要的資源比UNION語(yǔ)句少。 如果明確知道合并數(shù)據(jù)后的結(jié)果數(shù)據(jù)
  不存在重復(fù)數(shù)據(jù)，或者不需要去除重復(fù)的數(shù)據(jù)，則盡量使用UNION ALL語(yǔ)句，以提高數(shù)據(jù)查詢的效率。

應(yīng)用案例：

需求1： 查詢部門編號(hào)>90或郵箱包含a的員工信息

實(shí)現(xiàn)方式1：

 SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

實(shí)現(xiàn)方式2：

SELECT * FROM employees  WHERE email LIKE '%a%'
# union會(huì)自動(dòng)去重
UNION
SELECT * FROM employees  WHERE department_id>90;

需求2：：查詢中國(guó)用戶中男性的信息以及美國(guó)用戶中年男性的用戶信息

實(shí)現(xiàn)方式 ：

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

6.2，MySQL的7種JOIN操作

MySQL中共有7種JOIN操作，如下圖所示。但實(shí)際上常用的只有四種，它們分別是：

• 內(nèi)連接；
• 左外連接；
• 右外連接；
• 全外連接；

  

  
  接下來我們一一實(shí)現(xiàn)這些JOIN操作。

6.2.1，內(nèi)連接

內(nèi)連接返回的是滿足連接條件的所有行的交集部分。 這意味著只有當(dāng)兩個(gè)表中存在相應(yīng)的匹配記錄時(shí)，這些記錄才會(huì)出現(xiàn)在結(jié)果集中。

內(nèi)連接圖示如下：

需求： 查詢出已分配有效部門的員工的ID和部門名字：

SQL語(yǔ)句如下：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

6.2.2，左外連接

左外連接會(huì)返回左表中的所有記錄以及右表中符合條件的記錄；

左外連接圖示如下 ：

需求： 查詢所有員工ID以及部門姓名

SQL語(yǔ)句如下：

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

6.2.3，右外連接

右外連接會(huì)返回右表中的所有記錄以及左表中符合條件的記錄；

右外連接圖示如下：

需求： 列出所有部門（包括沒有員工的部門），并顯示每個(gè)部門中的員工信息（如果有的話）

SQL語(yǔ)句如下：

SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

6.2.4，左排除連接

左排除連接圖示如下：

左排除連接通過在LEFT JOIN的基礎(chǔ)上添加WHERE B.Key IS NULL來實(shí)現(xiàn)的，左排除連接返回的是表A中那些在表B中沒有匹配項(xiàng)的記錄。

需求： 查找沒有分配到任何部門的員工的信息

 SELECT employee_id,last_name,department_name
 FROM employees e LEFT JOIN departments d
 ON e.`department_id` = d.`department_id`
 WHERE d.`department_id` IS NULL

運(yùn)行結(jié)果如下：

6.2.5，右排除連接

右排除連接用于從右表中選擇那些在左表中沒有匹配記錄的數(shù)據(jù)行。簡(jiǎn)單來說，右排除連接返回的是右表中的所有在左表中找不到匹配項(xiàng)的記錄。

右排除連接圖示如下：

需求： 查詢沒有員工關(guān)聯(lián)的部門信息，即列出那些沒有任何員工分配到的部門。

SQL語(yǔ)句如下：

 SELECT employee_id,last_name,department_name
 FROM employees e RIGHT JOIN departments d
 ON e.`department_id` = d.`department_id`
 WHERE e.`department_id` IS NULL

運(yùn)行結(jié)果如下：

6.2.6，全外連接

全外連接則返回兩張表中的所有記錄，對(duì)于沒有匹配項(xiàng)的部分用NULL填充。

全外連接圖示如下：

觀察示意圖，可以發(fā)現(xiàn)：全外連接可以由兩種其它JOIN操作的并集組合而成。具體有兩種組合方式：

• 方式一：左外連接 UNION ALL 右排除連接；
• 方式二：右外連接 UNION ALL 左排除連接；

需求： 查詢所有員工（無(wú)論是否有對(duì)應(yīng)部門）和所有部門（無(wú)論是否有員工）信息。

方式一SQL語(yǔ)句如下：（實(shí)際上是合并了兩個(gè)SQL語(yǔ)句的查詢結(jié)果，通過UNION ALL合并）

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

方式二SQL語(yǔ)句如下：

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

注意：

• 由于MySQL數(shù)據(jù)庫(kù)不識(shí)別FULL OUTER JOIN關(guān)鍵字（Oracle數(shù)據(jù)庫(kù)支持），全外連接一般通過如上并集的方式等價(jià)實(shí)現(xiàn)；
• 使用UNION ALL而不用UNION的原因是UNION ALL無(wú)需去重操作， 效率更高；

6.2.7，外排除連接

外排除連接是由左排除連接和右排除連接組合而成。 。它返回左表和右表中沒有與對(duì)方表匹配的行，而匹配的行將被排除在結(jié)果集之外。

外排除連接圖示如下：

觀察示意圖，可以發(fā)現(xiàn)：外排除連接是由左排除連接和右排除連接組合而成。

需求： 聯(lián)合查詢員工表與部門表之間的不匹配記錄，找出 沒有對(duì)應(yīng)部門的員工以及沒有員工的部門

SQL語(yǔ)句如下：

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

總結(jié) 

到此這篇關(guān)于MySQL中多表查詢分類及七種JOIN操作實(shí)現(xiàn)方法的文章就介紹到這了,更多相關(guān)MySQL多表查詢分類及JOIN操作內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論