MySQL多表查詢與7種JOINS的實現舉例

2023-02-03 18:03:55

前言

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

前提條件：這些一起查詢的表之間是有關係的（一對一、一對多），它們之間一定是有關聯欄位，這個關聯欄位可能建立了外來鍵，也可能沒有建立外來鍵。比如：員工表和部門表，這兩個表依靠“部門編號”進行關聯。

1.案例多表連線

案例說明

從多個表中獲取資料：

# 錯誤的實現方式：每個員工都與每個部門匹配了一遍。
SELECT employee_id,department_name
FROM employees,departments;  
# 查詢出2889條記錄

分析錯誤情況：

SELECT COUNT(employee_id) FROM employees;
#輸出107行
SELECT COUNT(department_id)FROM departments;
# 輸出27行
SELECT 107*27 FROM dual;

因此把多表查詢中出現的問題稱為：笛卡爾積的錯誤。

笛卡爾積（或交叉連線）

笛卡爾乘積是一個數學運算。假設我有兩個集合 X 和 Y，那麼 X 和 Y 的笛卡爾積就是 X 和 Y 的所有可能組合，也就是第一個物件來自於 X，第二個物件來自於 Y 的所有可能。組合的個數即為兩個集合中元素個數的乘積數。

SQL92中，笛卡爾積也稱為交叉連線，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交叉連線。它的作用就是可以把任意表進行連線，即使這兩張表不相關。

因此上面的程式碼可以等價於：

# 錯誤的方式
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;
# 查詢出2889條記錄

笛卡爾積的錯誤會在下面條件下產生：

省略多個表的連線條件（或關聯條件）
連線條件（或關聯條件）無效
所有表中的所有行互相連線

為了避免笛卡爾積，可以在 WHERE 加入有效的連線條件。加入連線條件後，查詢語法：

# 在表中有相同列時，在列名之前加上表名字首。
SELECT last_name, department_name, departments.department_id;
FROM employees, departments
# 連線條件
WHERE employees.department_id = departments.department_id;

注意：在表中有相同列時，在列名之前加上表名字首。

建議：從sql優化的角度，建議多表查詢時，每個欄位前都指明其所在的表。

此外，方便起見，表名也可以用別名代替。但是如果給表起了別名，一旦在SELECT或WHERE中使用表名的話，則必須使用表的別名，而不能再使用表的原名。

多個連線條件的拼接需要使用 AND 關鍵字。例如：

SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

如果有 n 個表實現多表的查詢，則需要至少 n-1 個連線條件。

2. 多表查詢分類講解

角度1：等值連線與非等值連線

這裡涉及 job_grades 表，通過查詢可知，每個薪水都有其相應的等級區間。

SELECT *
FROM job_grades;

這裡通過員工表與其相應的區間等級做一個匹配：

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`;
# WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`highest_sal`;

角度2：自連線與非自連線

當table1和table2本質上是同一張表，只是用取別名的方式虛擬成兩張表以代表不同的意義。然後兩個表再進行內連線，外連線等查詢。

連線的條件是WORKER表中的MANAGER_ID和MANAGER表中的EMPLOYEE_ID相等。

題目：查詢employees表，返回“Xxx works for Xxx”

SELECT CONCAT(worker.last_name ,' works for ', manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

CONCAT的作用是連線字串。

角度3：內連線與外連線

內連線: 合併具有同一列的兩個以上的表的行, 結果集中不包含一個表與另一個表不匹配的行

外連線: 兩個表在連線過程中除了返回滿足連線條件的行以外還返回左（或右）表中不滿足條件的行，這種連線稱為左（或右）外連線。沒有匹配的行時, 結果表中相應的列為空(NULL)。

如果是左外連線，則連線條件中左邊的表也稱為主表，右邊的表稱為從表。

如果是右外連線，則連線條件中右邊的表也稱為主表，左邊的表稱為從表。

SQL92：使用(+)建立連線

在 SQL92 中採用（+）代表從表所在的位置。即左或右外連線中，(+) 表示哪個是從表。

但是Oracle 對 SQL92 支援，而 MySQL 則不支援 SQL92 的外連線。

# 左外連線
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
# 右外連線
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

在 SQL92 中，只有左外連線和右外連線，沒有滿（或全）外連線。

3. SQL99語法實現多表查詢

SQL99語法中使用 JOIN …ON 的方式實現多表的查詢。這種方式也能解決外連線的問題。

MySQL是支援此種方式的。

可以使用 ON 子句指定額外的連線條件。
這個連線條件是與其它條件分開的。
關鍵字 JOIN、INNER JOIN、CROSS JOIN 的含義是一樣的，都表示內連線

內連線(INNER JOIN)的實現

SELECT 欄位列表
FROM A表 INNER JOIN B表
ON 關聯條件
WHERE 等其他子句;

外連線(OUTER JOIN)的實現

左外連線(LEFT OUTER JOIN)

語法：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

右外連線(RIGHT OUTER JOIN)

語法：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

滿外連線(FULL OUTER JOIN)

滿外連線的結果 = 左右表匹配的資料 + 左表沒有匹配到的資料 + 右表沒有匹配到的資料。

SQL99是支援滿外連線的。使用FULL JOIN 或 FULL OUTER JOIN來實現。
但是MySQL不支援FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN代替。

4. UNION的使用

合併查詢結果

利用UNION關鍵字，可以給出多條SELECT語句，並將它們的結果組合成單個結果集。

合併時，兩個表對應的列數和資料型別必須相同，並且相互對應。
各個SELECT語句之間使用UNION或UNION ALL關鍵字分隔。

語法格式：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION操作符

UNION 操作符返回兩個查詢的結果集的並集，去除重複記錄。由於需要去除重複，因此它的效能相對低一點。

UNION ALL操作符

UNION ALL操作符返回兩個查詢的結果集的並集。對於兩個結果集的重複部分，不去重。

如果明確知道合併資料後的結果資料無重複資料，儘量使用UNION ALL語句，以提高資料查詢的效率。

5. 7種SQL JOINS的實現

程式碼實現

中圖：內連線 A ∩ B

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

左上圖：左外連線

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

右上圖：右外連線

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

左中圖：A - 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

右中圖：B - A ∩ B

這裡解釋一下WHERE e.department_id IS NULL，首先是由右外連線衍生出來的，減去中間交集的部分，然後交際的部分是包含A和B的，只需要用條件從表A為NULL，即可將在B中有A的部分篩掉。

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

左下圖：滿外連線，左中圖 + 右上圖 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
UNION ALL  #沒有去重操作，效率高
SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;

右下圖：左中圖 + 右中圖 A ∪ B - A ∩ B 或者 ( A - A ∩ B) ∪ ( B - 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
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

6.SQL99語法新特性

自然連線

可以把自然連線理解為 SQL92 中的等值連線。它會幫你自動查詢兩張連線表中所有相同的欄位，然後進行等值連線。

在 SQL92 標準中：

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

在 SQL99 中你可以寫成：

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING連線

當我們進行連線的時候，SQL99 還支援使用 USING 指定資料表裡的同名欄位進行等值連線。但是隻能配合JOIN一起使用。比如：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

你能看出與自然連線 NATURAL JOIN 不同的是，USING 指定了具體的相同的欄位名稱，你需要在 USING的括號 () 中填入要指定的同名欄位。同時使用 JOIN…USING 可以簡化 JOIN ON 的等值連線。它與下面的 SQL 查詢結果是相同的：

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

總結：表連線的約束條件可以有三種方式：WHERE, ON, USING

WHERE：適用於所有關聯查詢
ON ：只能和JOIN一起使用，只能寫關聯條件。雖然關聯條件可以併到WHERE中和其他條件一起寫，但分開寫可讀性更好。建議一個JOIN一個ON的寫法。
USING：只能和JOIN一起使用，而且要求兩個關聯欄位在關聯表中名稱一致，而且只能表示關聯欄位值相等。

注意：

要控制連線表的數量。多表連線就相當於巢狀 for 迴圈一樣，非常消耗資源，會讓 SQL 查詢效能下降得很嚴重，因此不要連線不必要的表。在許多 DBMS 中，也都會有最大連線表的限制。

附錄：常用的 SQL 標準有哪些

SQL 存在不同版本的標準規範，因為不同規範下的表連線操作是有區別的。

SQL 有兩個主要的標準，分別是 SQL92 和 SQL99 。92 和 99 代表了標準提出的時間，SQL92 就是 92 年
提出的標準規範。當然除了 SQL92 和 SQL99 以外，還存在 SQL-86、SQL-89、SQL:2003、SQL:2008、
SQL:2011 和 SQL:2016 等其他的標準。

最重要的 SQL 標準就是 SQL92 和 SQL99。一般來說 SQL92 的形式更簡單，但是寫的 SQL 語句會比較長，可讀性較差。而 SQL99 相比於 SQL92 來說，語法更加複雜，但可讀性更強。

SQL92 和 SQL99 是經典的 SQL 標準，也分別叫做 SQL-2 和 SQL-3 標準。也正是在這兩個標準釋出之後，SQL 影響力越來越大，甚至超越了資料庫領域。現如今 SQL 已經不僅僅是資料庫領域的主流語言，還是資訊領域中資訊處理的主流語言，在圖形檢索、影象檢索以及語音檢索中都能看到 SQL 語言的使用。

練習題

1.顯示所有員工的姓名，部門號和部門名稱

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

2.查詢90號部門員工的job_id和90號部門的location_id

SELECT job_id, location_id
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 90;

順便提一句，寫這道題時ON語句後加了個; 導致查詢總是不對，分析了好久才發現。不得不感嘆，SQL中分號真的不要亂加。

3.選擇所有有獎金的員工的 last_name , department_name , location_id , city

SELECT last_name , department_name , d.location_id , city
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE commission_pct IS NOT NULL;

4.選擇city在Toronto工作的員工的 last_name , job_id , department_id , department_name

SELECT e.last_name , e.job_id , d.department_id , d.department_name
FROM locations l
JOIN departments d
ON l.location_id = d.location_id
JOIN employees e
ON d.department_id = e.department_id
WHERE l.city = 'Toronto';

5．查詢員工所在的部門名稱、部門地址、姓名、工作、工資，其中員工所在部門的部門名稱為’Executive’

SELECT department_name, street_address, last_name, job_id, salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE department_name = 'Executive'

6.選擇指定員工的姓名，員工號，以及他的管理者的姓名和員工號，結果類似於下面的格式

employees Emp# manager Mgr#
kochhar   101  king    100

SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

7.查詢哪些部門沒有員工

SELECT d.department_name
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;

這裡簡單地描述一下解題的思路：首先涉及兩張表，員工表和部門表，然後通過department_id建立聯絡。接下來就要考慮如上圖的兩個部分，交集是有部門且有員工的部分，然後左外連線是各個部門號，去除中間的交集就是部門號但是沒員工的部分。因此類似於左中圖。只需要讓從表的該條件為NULL即可。

此外還有第二種辦法，子查詢：

SELECT department_id
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE e.`department_id` = d.`department_id`
)

8.查詢哪個城市沒有部門

SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL

9.查詢部門名為 Sales 或 IT 的員工資訊

SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');

練習題Part2

儲備：建表操作：
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_dept(deptName,address) VALUES('華山','華山');
INSERT INTO t_dept(deptName,address) VALUES('丐幫','洛陽');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武當','武當山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明頂');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('風清揚',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('嶽不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐沖',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('喬峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('滅絕師太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('張三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('張無忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韋小寶',18,null,100010);

【題目】
#1.所有有門派的人員資訊
（ A、B兩表共有）
#2.列出所有使用者，並顯示其機構資訊
（A的全集）
#3.列出所有門派
（B的全集）
#4.所有不入門派的人員
（A的獨有）
#5.所有沒人入的門派
（B的獨有）
#6.列出所有人員和機構的對照關係
(AB全有)
#MySQL Full Join的實現 因為MySQL不支援FULL JOIN,下面是替代方法
#left join + union(可去除重複資料)+ right join
#7.列出所有沒入派的人員和沒人入的門派
（A的獨有+B的獨有）

1.所有有門派的人員資訊（ A、B兩表共有）

select *
from t_emp a inner join t_dept b
on a.deptId = b.id;

2.列出所有使用者，並顯示其機構資訊（A的全集）

select *
from t_emp a left join t_dept b
on a.deptId = b.id;

3.列出所有門派（B的全集）

select *
from t_dept b;

4.所有不入門派的人員（A的獨有）

select *
from t_emp a left join t_dept b
on a.deptId = b.id
where b.id is null;

5.所有沒人入的門派（B的獨有）

select *
from t_dept b left join t_emp a
on a.deptId = b.id
where a.deptId is null;

6.列出所有人員和機構的對照關係 (AB全有)

#MySQL Full Join的實現 因為MySQL不支援FULL JOIN,下面是替代方法
#left join + union(可去除重複資料)+ right join
SELECT *
FROM t_emp A LEFT JOIN t_dept B
ON A.deptId = B.id
UNION
SELECT *
FROM t_emp A RIGHT JOIN t_dept B
ON A.deptId = B.id

7.列出所有沒入派的人員和沒人入的門派（A的獨有+B的獨有）

SELECT *
FROM t_emp A LEFT JOIN t_dept B
ON A.deptId = B.id
WHERE B.`id` IS NULL
UNION
SELECT *
FROM t_emp A RIGHT JOIN t_dept B
ON A.deptId = B.id
WHERE A.`deptId` IS NULL;