天天看點
傳回

【MySQL】7、 關聯查詢（聯合查詢) 内連接配接、外連接配接、全外連接配接第七章 關聯查詢（聯合查詢）

第七章 關聯查詢（聯合查詢）

關聯查詢 聯合查詢

七種結果：

【MySQL】7、 關聯查詢（聯合查詢) 内連接配接、外連接配接、全外連接配接第七章 關聯查詢（聯合查詢）

（1）A∩B

（2）A

（3）A - A∩B

（4）B

（5）B - A∩B

（6）A ∪ B

（7）A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）

關聯查詢7種結果與關聯查詢的分類

/*
關聯查詢的結果有七種情況：
（1）A∩B
（2）A
（3）A - A ∩ B
（4）B
（5）B - A ∩ B
（6）A ∪ B
（7）A ∪ B - A ∩ B

如何實作這些結果？
1、内連接配接：inner join 
	（1）A∩B  A∩B A交B
2、外連接配接
（1）左外連：left join
	（2）A
	（3）A - A ∩ B
（2）右外連接配接：right join
	（4）B
	（5）B - A ∩ B
（3）全外連接配接：full join
	MySQL但是不直接支援全外連接配接，但是可以使用union（合并）結果來實作以下兩種結果
	（6）A ∪ B		用  （2）A  union（合并） （4）B
	（7）A ∪ B - A ∩ B	用  （3）A - A ∩ B  union（合并）（5）B - A ∩ B
*/

外連接配接查詢将查詢多個表中相關聯的行，

内連接配接時，傳回查詢結果集合中僅是符合查詢條件和連接配接條件的行。有時候需要包含沒有關聯的行中資料，即傳回查詢結果集合中不僅包含符合連接配接條件的行，還包括左表（左外連接配接或左連接配接）、右表（右外連接配接或右連接配接）或兩個邊接表（全外連接配接）中的所有資料行。外連接配接分為左外連接配接或左連接配接和右外連接配接或右連

外連接配接分為左外連接配接或左連接配接和右外連接配接或右連接配接：

LEFT JOIN（左連接配接）：傳回包括左表中的所有記錄和右表中連接配接字段相等的記錄。

RIGHT JOIN（右連接配接）：傳回包括右表中的所有記錄和左表中連接配接字段相等的記錄。

外連接配接包括了三種形式：

左外連接配接：LEFT JOIN 或 LEFT OUTER JOIN

右外連接配接：RIGHT JOIN 或 RIGHT OUTER JOIN

全外連接配接：FULL JOIN 或 FULL OUTER JOIN

需要注意的是 MySQL 不支援全外連接配接，

但是可以使用union（合并）結果來實作以下兩種結果

A ∪ B 用 A union（合并） B

A ∪ B - A ∩ B 用 A - A ∩ B union（合并）B - A ∩ B

（1）内連接配接

（2）外連接配接：左外連接配接、右外連接配接、全外連接配接（mysql使用union代替全外連接配接）

文法先上

1、内連接配接：實作A∩B

/*
内連接配接：A∩B
1、官方寫法
兩張表内連接配接關聯查詢
	select 字段清單
	from A表名 inner join B表名
	on 1個關聯條件
	where 其他條件等

三張表内連接配接關聯查詢
	select 字段清單
	from A表名 inner join B表名 inner join C表名
	on 2個關聯條件
	where 其他條件等

關聯條件的個數 = 表數 - 1
如果少了關聯條件，就會笛卡爾積的
5*3*3 45  A∩B 再交
2、另一種寫法

兩張表内連接配接關聯查詢
	select 字段清單
	from A表名 , B表名
	where 1個關聯條件  and 其他條件等
*/

#查詢所有員工的編号，姓名，部門編号和他所在部門的名稱
/*
select eid,ename, did,dname 
from t_employee inner join t_department;

錯誤
錯誤代碼： 1052
Column 'did' in field list is ambiguous 模糊不确定
*/
/*
select eid,ename,t_employee.did,dname 
from t_employee inner join t_department;

出現了笛卡爾積  A表的數量 * B表的數量
*/
SELECT eid,ename,t_employee.did,dname 
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did;

#給表名取别名
SELECT eid,ename,emp.did,dname 
FROM t_employee AS emp INNER JOIN t_department AS dept
ON emp.did = dept.did;

SELECT eid,ename,emp.did,dname 
FROM t_employee AS emp , t_department AS dept
WHERE emp.did = dept.did;

#查詢員工編号，員工姓名，員工的職位編号，職位名稱，部門編号，部門名稱
SELECT eid,ename,t_employee.`job_id`,job_name,t_employee.`did`,dname
FROM t_employee INNER JOIN t_job INNER JOIN t_department
ON t_employee.`did` = t_department.did AND t_employee.`job_id` = t_job.`job_id`;

#查詢薪資高于15000的男員工編号，員工姓名，員工的職位編号，職位名稱，部門編号，部門名稱
SELECT eid,ename,t_employee.`job_id`,job_name,t_employee.`did`,dname
FROM t_employee INNER JOIN t_job INNER JOIN t_department
ON t_employee.`did` = t_department.did AND t_employee.`job_id` = t_job.`job_id`
WHERE salary > 15000 AND gender = '男'
           
select 字段清單
from A表 inner join B表
on 關聯條件
where 等其他子句;

或

select 字段清單
from A表 , B表
where 關聯條件 and 等其他子句;

代碼示例：

#查詢員工的姓名和他所在的部門的名稱
#員工的姓名在t_employee
#部門的名稱在t_department
SELECT ename "員工的姓名",dname "部門名稱"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did

SELECT ename "員工的姓名",dname "部門名稱"
FROM t_employee , t_department
WHERE t_employee.did = t_department.did


#查詢薪資高于20000的男員工的姓名和他所在的部門的名稱
SELECT ename "員工的姓名",dname "部門名稱"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did
WHERE salary>20000 AND gender = '男'

2、左外連接配接

/*
左連接配接：
（1）A
select 字段清單
from A left join B
on 關聯條件
where 其他條件 等

（2）A -  A∩B

select 字段清單
from A left join B
on 關聯條件
where 關聯字段 is null and 其他條件 等

*/

#查詢員工的編号，姓名，部門編号，部門名稱
#包括那些沒有配置設定部分的員工
SELECT eid,ename,t_employee.did,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did;

#查詢那些沒有配置設定部門的員工
SELECT eid,ename,t_employee.did,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL;

           
#實作查詢結果是A
select 字段清單
from A表 left join B表
on 關聯條件
where 等其他子句;

#實作A -  A∩B
select 字段清單
from A表 left join B表
on 關聯條件
where 從表關聯字段 is null and 等其他子句;

代碼示例：

#查詢所有員工的姓名和他所在的部門的名稱
SELECT ename "員工的姓名",dname "部門名稱"
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did


#查詢所有沒有部門的員工
SELECT ename "員工的姓名",dname "部門名稱"
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

3、右外連接配接

/*
右連接配接：
（1）B
select 字段清單
from A right join B
on 關聯條件
where 其他條件 等

（2）B -  A∩B

select 字段清單
from A right join B
on 關聯條件
where 關聯字段 is null and 其他條件 等

*/

#查詢員工的編号，姓名，部門編号，部門名稱
#包括那些沒有配置設定部分的員工
SELECT eid,ename,t_employee.did,dname
FROM t_department RIGHT JOIN t_employee
ON t_employee.did = t_department.did;

#查詢那些沒有配置設定部門的員工
SELECT eid,ename,t_employee.did,dname
FROM t_department RIGHT JOIN t_employee
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL;

           
#實作查詢結果是B
select 字段清單
from A表 right join B表
on 關聯條件
where 等其他子句;

#實作B -  A∩B
select 字段清單
from A表 right join B表
on 關聯條件
where 從表關聯字段 is null and 等其他子句;

代碼示例：

#查詢所有部門，以及所有部門下的員工資訊
SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did

#查詢那些沒有員工屬于它的部門
SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

4、用union代替全外連接配接 使用union實作全外連接配接的效果

/*
使用UNION來實作全外連接配接的查詢結果：
（1）A ∪ B
（2）A ∪ B - A ∩ B
迂回成
（1）A UNION B
（2）A - A ∩ B UNION B - A ∩ B
*/

#查詢所有員工和所有部門的資訊，包括那些沒有配置設定部門的員工和沒有安排員工的部門
SELECT * 
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.`did`

UNION

SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.`did`

#查詢那些沒有配置設定部門的員工和沒有安排員工的部門
SELECT * 
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.`did`
WHERE t_employee.did IS NULL

UNION

SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.`did`
WHERE t_employee.did IS NULL
           
#實作查詢結果是A∪B
#用左外的A，union 右外的B
select 字段清單
from A表 left join B表
on 關聯條件
where 等其他子句

union 

select 字段清單
from A表 right join B表
on 關聯條件
where 等其他子句;



#實作A∪B -  A∩B  或   (A -  A∩B) ∪ （B - A∩B）
#使用左外的 (A -  A∩B)  union 右外的（B - A∩B）
select 字段清單
from A表 left join B表
on 關聯條件
where 從表關聯字段 is null and 等其他子句

union

select 字段清單
from A表 right join B表
on 關聯條件
where 從表關聯字段 is null and 等其他子句

代碼示例：

#查詢所有員工，所有部門，包括沒有員工的部門，和沒有部門的員工
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did

UNION

SELECT *
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did


#查詢那些沒有部門的員工和所有沒有員工的部門

#沒有部門的員工
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

UNION 

#所有沒有員工的部門
SELECT *
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

5、自連接配接

兩個關聯查詢的表是同一張表，通過取别名的方式來虛拟成兩張表

select 字段清單
from 表名 别名1 inner/left/right join 表名 别名2
on 别名1.關聯字段 = 别名2的關聯字段
where 其他條件

代碼示例：

#查詢員工的編号，姓名，薪資和他上司的編号，姓名，薪資
#這些資料全部在員工表中
#把t_employee表，即當做員工表，又當做上司表
#上司表是虛拟的概念，我們可以通過取别名的方式虛拟
SELECT emp.eid "員工的編号",emp.ename "員工的姓名" ,emp.salary "員工的薪資",
	mgr.eid "上司的編号" ,mgr.ename "上司的姓名",mgr.salary "上司的薪資"
FROM t_employee emp INNER JOIN t_employee mgr
#t_employee emp：如果用emp.，表示的是員工表的
#t_employee mgr：如果用mgr.，表示的是上司表的
ON emp.mid = mgr.eid

#表的别名不要加""，給列取别名，可以用""，列的别名不使用""也可以，但是要避免包含空格等特殊符号。

記錄 - 搞定Java核心技術

資料庫 mysql 聯合查詢 内連接配接 外連接配接
上一篇: mysql内連接配接語句_MySQL語句、内連接配接、外連接配接、子查詢
下一篇: mysql 外連接配接條件查詢_MYSQL内連接配接，外連接配接，複合條件查詢

繼續閱讀