1，什么是多表查詢

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

可進行多表查詢的前提條件： 這些一起查詢的表之間是有關系的（一對一、一對多），它們之間一定是有關聯(lián)字段，如下圖：員工表和部門表，這兩個表依靠“部門編號”進行關聯(lián)，因此符合多表查詢的條件。

2，多表查詢的分類

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

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

接下來我們詳細看一下這些不同種類多表查詢的定義和應用。

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

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

• 等值連接通過（=）運算符進行比較；
• 非等值連接通過其他運算符進行比較；

3.1，等值連接的定義及應用

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

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

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

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

此時正確的SQL語句如下：

SELECT employee_id,department_name
FROM employees,departments

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

運行結果如下：

此即為一個等值連接的應用示例。

3.2，非等值連接的定義及應用

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

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

當我們有如下需求時：

需求：查看員工的姓名、工資、工資等級

SQL語句如下：

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

運行結果如下：

此即為一個非等值連接的應用示例。

4，自連接和非自連接

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

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

4.1，自連接的定義及應用

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

自連接對應了表中自我引用的關系。如下圖員工表的例子所示，104號和105號員工的主管是103號員工（103號員工是一名員工，同時擔任主管）。

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

SQL語句如下：

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

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

查詢結果如下：

此即為一個自連接的示例。

4.2，非自連接的定義及應用

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

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

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

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

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

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

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

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

SQL語句如下：

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語法）
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;

查詢結果如下：

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

5.2，外連接的定義及應用

外連接包括主表中的所有記錄，即使它們在另一個表中沒有匹配項。

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

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

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

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

SQL語句如下：

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

或者：

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

運行結果如下：

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

此即為一個外連接的示例，具體而言是左外連接。接下來我們詳細學習其中JOIN操作。

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

6.1，UNION和UNION ALL

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

應用案例：

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

實現(xiàn)方式1：

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

實現(xiàn)方式2：

SELECT * FROM employees  WHERE email LIKE '%a%'
# union會自動去重
UNION
SELECT * FROM employees  WHERE department_id>90;

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

實現(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操作，如下圖所示。但實際上常用的只有四種，它們分別是：

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

  

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

6.2.1，內(nèi)連接

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

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

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

SQL語句如下：

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

6.2.2，左外連接

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

左外連接圖示如下 ：

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

SQL語句如下：

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

6.2.3，右外連接

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

右外連接圖示如下：

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

SQL語句如下：

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的基礎上添加WHERE B.Key IS NULL來實現(xiàn)的，左排除連接返回的是表A中那些在表B中沒有匹配項的記錄。

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

 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

運行結果如下：

6.2.5，右排除連接

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

右排除連接圖示如下：

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

SQL語句如下：

 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

運行結果如下：

6.2.6，全外連接

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

全外連接圖示如下：

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

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

需求： 查詢所有員工（無論是否有對應部門）和所有部門（無論是否有員工）信息。

方式一SQL語句如下：（實際上是合并了兩個SQL語句的查詢結果，通過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語句如下：

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ù)庫不識別FULL OUTER JOIN關鍵字（Oracle數(shù)據(jù)庫支持），全外連接一般通過如上并集的方式等價實現(xiàn)；
• 使用UNION ALL而不用UNION的原因是UNION ALL無需去重操作， 效率更高；

6.2.7，外排除連接

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

外排除連接圖示如下：

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

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

SQL語句如下：

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

總結 

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

最新評論