MySQL多表查詢與7種JOINS的實現(xiàn)舉例

更新時間：2023年02月01日 15:09:56 作者：timerring

最近學習了多表查詢,對此做一些筆記的整理,下面這篇文章主要給大家介紹了關(guān)于MySQL多表查詢與7種JOINS實現(xiàn)的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下

前言

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

前提條件：這些一起查詢的表之間是有關(guān)系的（一對一、一對多），它們之間一定是有關(guān)聯(lián)字段，這個關(guān)聯(lián)字段可能建立了外鍵，也可能沒有建立外鍵。比如：員工表和部門表，這兩個表依靠“部門編號”進行關(guān)聯(lián)。

1.案例多表連接

案例說明

從多個表中獲取數(shù)據(jù)：

# 錯誤的實現(xiàn)方式：每個員工都與每個部門匹配了一遍。
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;

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

笛卡爾積（或交叉連接）

笛卡爾乘積是一個數(shù)學運算。假設(shè)我有兩個集合 X 和 Y，那么 X 和 Y 的笛卡爾積就是 X 和 Y 的所有可能組合，也就是第一個對象來自于 X，第二個對象來自于 Y 的所有可能。組合的個數(shù)即為兩個集合中元素個數(shù)的乘積數(shù)。

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

因此上面的代碼可以等價于：

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

笛卡爾積的錯誤會在下面條件下產(chǎn)生：

省略多個表的連接條件（或關(guān)聯(lián)條件）
連接條件（或關(guān)聯(lián)條件）無效
所有表中的所有行互相連接

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

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

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

建議：從sql優(yōu)化的角度，建議多表查詢時，每個字段前都指明其所在的表。

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

多個連接條件的拼接需要使用 AND 關(guān)鍵字。例如：

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 個表實現(xiàn)多表的查詢，則需要至少 n-1 個連接條件。

2. 多表查詢分類講解

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

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

SELECT *
FROM job_grades;

這里通過員工表與其相應的區(qū)間等級做一個匹配：

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本質(zhì)上是同一張表，只是用取別名的方式虛擬成兩張表以代表不同的意義。然后兩個表再進行內(nèi)連接，外連接等查詢。

連接的條件是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：內(nèi)連接與外連接

內(nèi)連接: 合并具有同一列的兩個以上的表的行, 結(jié)果集中不包含一個表與另一個表不匹配的行

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

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

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

SQL92：使用(+)創(chuàng)建連接

在 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語法實現(xiàn)多表查詢

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

MySQL是支持此種方式的。

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

內(nèi)連接(INNER JOIN)的實現(xiàn)

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 關(guān)聯(lián)條件
WHERE 等其他子句;

外連接(OUTER JOIN)的實現(xiàn)

左外連接(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)

滿外連接的結(jié)果 = 左右表匹配的數(shù)據(jù) + 左表沒有匹配到的數(shù)據(jù) + 右表沒有匹配到的數(shù)據(jù)。

SQL99是支持滿外連接的。使用FULL JOIN 或 FULL OUTER JOIN來實現(xiàn)。
但是MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN代替。

4. UNION的使用

合并查詢結(jié)果

利用UNION關(guān)鍵字，可以給出多條SELECT語句，并將它們的結(jié)果組合成單個結(jié)果集。

合并時，兩個表對應的列數(shù)和數(shù)據(jù)類型必須相同，并且相互對應。
各個SELECT語句之間使用UNION或UNION ALL關(guān)鍵字分隔。

語法格式：

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

UNION操作符

UNION 操作符返回兩個查詢的結(jié)果集的并集，去除重復記錄。由于需要去除重復，因此它的性能相對低一點。

UNION ALL操作符

UNION ALL操作符返回兩個查詢的結(jié)果集的并集。對于兩個結(jié)果集的重復部分，不去重。

如果明確知道合并數(shù)據(jù)后的結(jié)果數(shù)據(jù)無重復數(shù)據(jù)，盡量使用UNION ALL語句，以提高數(shù)據(jù)查詢的效率。

5. 7種SQL JOINS的實現(xiàn)

代碼實現(xiàn)

中圖：內(nèi)連接 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 指定數(shù)據(jù)表里的同名字段進行等值連接。但是只能配合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 查詢結(jié)果是相同的：

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

總結(jié)：表連接的約束條件可以有三種方式：WHERE, ON, USING

WHERE：適用于所有關(guān)聯(lián)查詢
ON ：只能和JOIN一起使用，只能寫關(guān)聯(lián)條件。雖然關(guān)聯(lián)條件可以并到WHERE中和其他條件一起寫，但分開寫可讀性更好。建議一個JOIN一個ON的寫法。
USING：只能和JOIN一起使用，而且要求兩個關(guān)聯(lián)字段在關(guān)聯(lián)表中名稱一致，而且只能表示關(guān)聯(lián)字段值相等。

注意：

要控制連接表的數(shù)量。多表連接就相當于嵌套 for 循環(huán)一樣，非常消耗資源，會讓 SQL 查詢性能下降得很嚴重，因此不要連接不必要的表。在許多 DBMS 中，也都會有最大連接表的限制。

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

SQL 存在不同版本的標準規(guī)范，因為不同規(guī)范下的表連接操作是有區(qū)別的。

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

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

SQL92 和 SQL99 是經(jīng)典的 SQL 標準，也分別叫做 SQL-2 和 SQL-3 標準。也正是在這兩個標準發(fā)布之后，SQL 影響力越來越大，甚至超越了數(shù)據(jù)庫領(lǐng)域?，F(xiàn)如今 SQL 已經(jīng)不僅僅是數(shù)據(jù)庫領(lǐng)域的主流語言，還是信息領(lǐng)域中信息處理的主流語言，在圖形檢索、圖像檢索以及語音檢索中都能看到 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語句后加了個; 導致查詢總是不對，分析了好久才發(fā)現(xiàn)。不得不感嘆，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.選擇指定員工的姓名，員工號，以及他的管理者的姓名和員工號，結(jié)果類似于下面的格式

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建立聯(lián)系。接下來就要考慮如上圖的兩個部分，交集是有部門且有員工的部分，然后左外連接是各個部門號，去除中間的交集就是部門號但是沒員工的部分。因此類似于左中圖。只需要讓從表的該條件為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.列出所有用戶，并顯示其機構(gòu)信息
（A的全集）
#3.列出所有門派
（B的全集）
#4.所有不入門派的人員
（A的獨有）
#5.所有沒人入的門派
（B的獨有）
#6.列出所有人員和機構(gòu)的對照關(guān)系
(AB全有)
#MySQL Full Join的實現(xiàn) 因為MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重復數(shù)據(jù))+ right join
#7.列出所有沒入派的人員和沒人入的門派
（A的獨有+B的獨有）

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

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

2.列出所有用戶，并顯示其機構(gòu)信息（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.列出所有人員和機構(gòu)的對照關(guān)系 (AB全有)

#MySQL Full Join的實現(xiàn) 因為MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重復數(shù)據(jù))+ 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;