MySQL多表查詢與7種JOINS的實現(xiàn)舉例
前言
多表查詢，也稱為關聯(lián)查詢，指兩個或更多個表一起完成查詢操作。
前提條件：這些一起查詢的表之間是有關系的（一對一、一對多），它們之間一定是有關聯(lián)字段，這個關聯(liá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ù)學運算。假設我有兩個集合 X 和 Y，那么 X 和 Y 的笛卡爾積就是 X 和 Y 的所有可能組合，也就是第一個對象來自于 X，第二個對象來自于 Y 的所有可能。組合的個數(shù)即為兩個集合中元素個數(shù)的乘積數(shù)。
SQL92中，笛卡爾積也稱為交叉連接，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交叉連接。它的作用就是可以把任意表進行連接，即使這兩張表不相關。
因此上面的代碼可以等價于：
# 錯誤的方式 SELECT employee_id,department_name FROM employees CROSS JOIN departments; # 查詢出2889條記錄
笛卡爾積的錯誤會在下面條件下產(chǎn)生：
- 省略多個表的連接條件（或關聯(liá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 關鍵字。例如：
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)連接: 合并具有同一列的兩個以上的表的行, 結果集中不包含一個表與另一個表不匹配的行
外連接: 兩個表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行 ，這種連接稱為左（或右） 外連接。沒有匹配的行時, 結果表中相應的列為空(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 子句指定額外的連接條件。
- 這個連接條件是與其它條件分開的。
- 關鍵字 JOIN、INNER JOIN、CROSS JOIN 的含義是一樣的，都表示內(nèi)連接
內(nèi)連接(INNER JOIN)的實現(xiàn)
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 關聯(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)
滿外連接的結果 = 左右表匹配的數(shù)據(jù) + 左表沒有匹配到的數(shù)據(jù) + 右表沒有匹配到的數(shù)據(jù)。
- SQL99是支持滿外連接的。使用FULL JOIN 或 FULL OUTER JOIN來實現(xiàn)。
- 但是MySQL不支持FULL JOIN，但是可以用
LEFT JOINUNIONRIGHT JOIN代替。
4. UNION的使用
合并查詢結果
利用UNION關鍵字，可以給出多條SELECT語句，并將它們的結果組合成單個結果集。
- 合并時，兩個表對應的列數(shù)和數(shù)據(jù)類型必須相同，并且相互對應。
- 各個SELECT語句之間使用UNION或UNION ALL關鍵字分隔。
語法格式：
SELECT column,... FROM table1 UNION [ALL] SELECT column,... FROM table2
UNION操作符
UNION 操作符返回兩個查詢的結果集的并集，去除重復記錄。由于需要去除重復，因此它的性能相對低一點。
UNION ALL操作符
UNION ALL操作符返回兩個查詢的結果集的并集。對于兩個結果集的重復部分，不去重。
如果明確知道合并數(shù)據(jù)后的結果數(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 查詢結果是相同的：
SELECT employee_id,last_name,department_name FROM employees e ,departments d WHERE e.department_id = d.department_id;
總結：表連接的約束條件可以有三種方式：WHERE, ON, USING
- WHERE：適用于所有關聯(lián)查詢
- ON ：只能和JOIN一起使用，只能寫關聯(lián)條件。雖然關聯(lián)條件可以并到WHERE中和其他條件一起寫，但分開寫可讀性更好。建議一個JOIN一個ON的寫法。
- USING：只能和JOIN一起使用，而且要求兩個關聯(lián)字段在關聯(liá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ù)庫領域。現(xiàn)如今 SQL 已經(jīng)不僅僅是數(shù)據(jù)庫領域的主流語言，還是信息領域中信息處理的主流語言，在圖形檢索、圖像檢索以及語音檢索中都能看到 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.選擇指定員工的姓名，員工號，以及他的管理者的姓名和員工號，結果類似于下面的格式
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.列出所有用戶，并顯示其機構信息
（A的全集）
#3.列出所有門派
（B的全集）
#4.所有不入門派的人員
（A的獨有）
#5.所有沒人入的門派
（B的獨有）
#6.列出所有人員和機構的對照關系
(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.列出所有用戶，并顯示其機構信息（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的實現(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;
總結
到此這篇關于MySQL多表查詢與7種JOINS實現(xiàn)的文章就介紹到這了,更多相關MySQL多表查詢與JOINS實現(xiàn)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了MySQL 主從復制原理與實踐,結合實例形式分析了MySQL 主從復制基本概念、原理、實現(xiàn)方法與相關注意事項,需要的朋友可以參考下2020-05-05
Mysql 命令行模式訪問操作mysql數(shù)據(jù)庫操作
這篇文章主要介紹了Mysql 命令行模式訪問操作mysql數(shù)據(jù)庫操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-08-08
這篇文章主要給大家介紹了關于Mysql在線回收undo表空間的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用Mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-09-09
Workbench連接不上阿里云服務器Ubuntu的Mysql解決方法(已測)
這兩天為了解決workbench連接不上阿里云服務器的問題，搞得頭大，網(wǎng)上搜到的教程都大同小異，但唯獨到我這就是行不通。不過好在最后終于解決了，記錄一下這個坑爹的過程，另外腳本之家小編特把這些問題整理了一下，看完這一篇文章基本上就解決了2020-02-02
這篇文章主要介紹了MySQL中列子查詢與行子查詢操作的學習教程,子查詢是MySQL入門學習中的基礎知識,需要的朋友可以參考下2015-12-12
大家都知道MySQL中經(jīng)常會需要創(chuàng)建父子表之間的約束,下面這篇文章主要給大家介紹了關于MySQL約束constraint用法的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-05-05
本文主要介紹了Mysql8和mysql5.7的區(qū)別，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-03-03
MySQL性能監(jiān)控軟件Nagios的安裝及配置教程
這篇文章主要介紹了MySQL性能監(jiān)控軟件Nagios的安裝及配置教程,這里以CentOS操作系統(tǒng)為環(huán)境進行演示,需要的朋友可以參考下2015-12-12
Mysql5.7忘記root密碼及mysql5.7修改root密碼的方法
這篇文章主要介紹了Mysql5.7忘記root密碼及mysql5.7修改root密碼的方法的相關資料,需要的朋友可以參考下2016-01-01
文章介紹了通過SSH連接MySQL數(shù)據(jù)庫的方法,強調(diào)了確保MySQL默認端口3306沒有被防火墻阻攔,以及在Ubuntu系統(tǒng)中默認root的SSH是禁止的,應使用其他賬戶登錄2025-02-02