多表查詢
案列說明
笛卡爾積的理解
|
1
2
3
4
5
|
select id,department_namefrom employees,departments;#錯的select id,department_id,department_namefrom employees CROSS JOIN departments;#錯的 |
每個員工和每個部門匹配了一遍（查出的條目數=id數*department數）
錯誤原因：缺少連接條件
笛卡爾積的解決
編寫連接條件: 表1.列 = 表二.列（若多個表連接，至少要用n-1個連接條件）
|
1
2
3
|
select id,employees.name,department_name from employees,departmentsWHERE employees.name = departments.name; |
注：如果要顯示的列在要查詢的表中名字一樣，則要表明，是出自哪個表， eg: employees.name
? 建議在多表查詢時，標明顯示的是哪個表的信息 （優化）
優化：可以在FROM后使用表的別名，但是一旦使用別名，后續就一定要都用別名
多表查詢的分類
等值連接和非等值連接
- 等值連接：上述的帶有=的
- 非等值連接：沒有=的
|
1
2
3
|
select t1.id,t1.name,t2.gradefrom employees t1,departments t2WHERE ti.salary BETWEEN t2.lowest_salary AND t2.highest_salary ;#非等值 |
自連接和非自連接
- 非自鏈接：表1和表2連接
- 自鏈接：表1和自己連接
|
1
2
3
4
|
#顯示員工（t1）和其管理者（t2）的基本信息select t1.id,t1.name,t2.id，t2.namefrom employees t1,employees t2#一個表看作兩個表WHERE t1.manage_id = t2.id ;#自連接 |
內連接和外連接
- 內連接：合并含有同一列的表，結果不包括一個表與另一個表不匹配打的行
- 外連接：合并含有同一列的表，結果除了內連接的結果還查詢不匹配的行
外連接的分類：左外連接（左表多，補右邊），右外連接（右表多，補左邊），滿外連接
SQL92:使用（+）創建連接
內連接：見上
外連接：左表有數據不匹配，在右表加（+）；反之，在左表加（+），但是MySQL不支持
|
1
|
WHERE t1.department_id = t2.department_id(+)#左連接 |
SQL99：使用JOIN...ON的方式
內連接
|
1
2
3
4
5
|
select t1.id,t1.name,t2.department_name,t3.environmentfrom employees t1 JOIN departments t2ON t1.department_id = t2.department_idJOIN locations t3#加入第二個人表ON t2.department_location = t3.department_location; |
外連接
使用OUTER JOIN...ON...
- 左外連接：LEFT OUTER JOIN
- 右外連接：RIGHT OUTER JOIN
- 滿外連接：FULL OUTER JOIN（MySQL不支持）
|
1
2
3
|
select t1.name,t2.department_name#左外連接from employees t1 LEFT OUTER（可省略） JOIN departments t2ON t1.department_id = t2.department_id； |
UMION的使用
合并查詢結果
|
1
2
3
|
SELECT colum... FROM table1UNION (ALL)SELECT colum... FROM table2 |
- ? UNION操作符
兩個查詢結果的并集，去重（效率低）
- UNION ALL操作符（推薦）
? 兩個查詢結果的并集，不去重（效率高）
7種SQL JOINS的實現
中圖（內連接）：
|
1
2
3
|
select t1.name,t2.department_namefrom employees t1 JOIN departments t2ON t1.department_id = t2.department_id； |
左上圖(左外連接)：
|
1
2
3
|
select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_id； |
右上圖(右外連接)：
|
1
2
3
|
select t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_id； |
左中圖：
|
1
2
3
4
|
select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULL; |
右中圖：
|
1
2
3
4
|
select t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL; |
左下圖(滿外連接)：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
#方式一：左上圖 UNION ALL 右中圖select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idUNION ALLselect t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL;#方式二：左中圖 UNION ALL 右上圖select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULLUNION ALLselect t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_id； |
右下圖：
|
1
2
3
4
5
6
7
8
9
10
|
#左中圖 UNION ALL 右中圖select t1.name,t2.department_namefrom employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULLUNION ALLselect t1.name,t2.department_namefrom employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL; |
SQL語法新特性
自然連接
使用關鍵字：NATURAL JOIN（不靈活），自動查詢表中所有相同字段，然后進行等值連接
USING連接(不適用于自連接)
使用關鍵字：USING（同名字段），將表中相同名字的字段自動等值連接
|
1
2
3
4
5
6
7
|
select t1.name,t2.department_namefrom employees t1 JOIN departments t2ON t1.department_id = t2.department_id;等價于select t1.name,t2.department_namefrom employees t1 JOIN departments t2USING(department_id); |
到此這篇關于MySQL多表查詢的文章就介紹到這了,更多相關MySQL多表查詢內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://www.cnblogs.com/wht-de-bk/archive/2022/03/04/15966054.html
