【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核心技术