MySQL笔记5——内连接/外连接、多表连接

一、内连接

1、等值连接

* 特点：条件为等量关系

# 语法
select 
	别名1.字段，别名2.字段
from
	表1 别名1
join
	表2 别名2
on
	连接条件
where
	其他条件

案例：查询每个员工的员工名字和部门名字

案例：查询每个员工的员工名字、员工编号和部门名字、工作地点

select
	e.empno,e.ename,p.pname,p.loc
from 
	emp e
join
	dept d
on 
	e.deptno=d.deptno;#两张表之间的连接关系就是他们的deptno字段相等，等值连接

2、非等值连接

• 特点：连接条件中的关系为非等量关系
  示例：现有如下两张表，找出每个员工的薪资等级，要求显示员工名、工资、工资等级
  

select
	e.ename,e.sal,s.grade
from emp e
join salgroud s
on
	e.sal between s.losal and s.hisal;
	# 条件为：e表中的sal(工资)字段介于s表中losal字段和hisal字段之间
	# 条件为非等量关系，因此成非等值连接

3、自连接

• 特点：一张表看做两张表，自己连接自己
  示例：现有下表，找出每个员工的上级领导，要求显示员工名和领导名
  
  员工的领导编号=领导的员工编号。因为领导也是员工，在员工表中。
  因此连接条件为：员工表的领导编号=领导表的员工编号

select
	a.ename as '员工',b.ename as '领导'
from
	emp a	#emp表起别名a看成员工表
join
	emp b	#emp表起别名b看成领导表
on
	a.mgr=b.empno;	
	#条件：员工表的领导编号=领导表的员工编号
#这种把一张表看成两张表的连接方式成为自连接

上面的查询由于KING员工是大BOSS上面已经没领导了，KING的MGR字段为NULL。因此上述语句匹配时没有匹配到，丢失了KING的数据。下面介绍使用外连接查询时如何全部查出。

二、外连接

• 内连接：假设A表和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录都查询出来，这就是外连接。A、B两张表无主副之分，两张表是平等的。
• 外连接：假设A表和B表进行连接，使用外连接的话，AB两张表中有一张是主表，一张表是副表。在查询时主要查询主表中的数据，附带着查询副表，当副表中的数据没有和主表中的数据匹配上时，副表自动模拟出NULL与之匹配

外连接最重要的特点是：主表中的数据能完整的全部查出，不会导致数据的丢失

①左外连接

任何一个左连接都有一个右连接的写法，同样任何一个右连接都有一个左连接的写法

左边的表是主表

#语法，只要在join关键字前加left，代表join左边的表是主表，右边的表是副表
select 
	别名1.字段，别名2.字段
from
	表1 别名1
left join
	表2 别名2
on
	连接条件
where
	其他条件

示例：还是上面的示例，查询所有员工的领导。在使用内连接的时候，由于KING的领导为NULL，因此匹配条件不满足，导致匹配结果中没有KING。下面看使用左外连接如何查找出全部员工的领导

select
	a.ename,b.ename
from emp a
left join emp b
on a.mgr=b.empno;	
# 语法和使用内连接的时候基本一样，只是在join前面加了left
# 加了left后表示a表是主表，b表是副表。在查找时要对a表中
# 要查询的字段全部显示出来，即使匹配条件可能不满足，但是a表中
# 要查询的字段也要显示，而b表中不满足匹配条件的模拟出NULL与之匹配

KIING在a表中的MGR(字段)在执行匹配条件时找不到b表中与之对应的EMPNO字段。因此副表b会模拟出一个NULL值与a表中KING的MGR字段匹配，最终满足条件显示

②右外链接

右边的表是主表
还是上面的例子，使用右外连接

select
	a.ename,b.ename
from emp b
right join emp a
on a.mgr=b.empno;
# 右连接时，join右边的表为主编，即a表变成主表，b表为副表。KIING在a表中的
# MGR(字段)在执行匹配条件时找不到b表中与之对应的EMPNO字段。因此副表b会模拟
# 出一个NULL值与a表中KING的MGR字段匹配，最终满足条件显示

示例：查询那个部门下没有员工

通过上图可知40部门下没员工，可是怎么查询出呢？

select 
	a.*,b.*
from dept a
left join emp b
on a.mgr=b.empno;
查询结果如下图

在加过滤条件即可筛选出数据了

select a.*,b.*
from dept a
left join emp b
on a.mgr=b.empno;
where b.ename(或者empno/job/mgr等字段) is null;

在把emp表中的字段不显示即可

最终查询代码
select a.*
from dept a
left join emp b
on a.mgr=b.empno;
where b.ename(或者empno/job/mgr等字段) is null;

③全连接

不用，用的少

三、多张表连接

select
	字段
from
	A表
join 
	B表
on
	A表和B表之间的连接关系
join
	C表
on
	A表、B表连接之后新表和C表之间的连接关系
join
	……
on
	……

示例：找出每个员工的部门名称和工资等级

select 
	e.ename '姓名',d.dname '部门名称',s.grade '工资等级' 
from emp e 
join dept d 
on 
	e.deptno=d.deptno 
join salgrade s 
on 
	e.sal between s.losal and s.hisal;

示例：找出每个员工所在的部门名称、薪资等级、上级领导

select
    e.ename,d.dname,s.grade,e1.ename 
from 
    emp e 
join 
    dept d 
on  
    e.deptno=d.deptno 
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisal 
left join   #e表是主表，e1是副表，主表中的mgr字段不等于副表的empno字段时，副表自动模拟出一个NULL值与之匹配   
    emp e1 
on  
    e.mgr=e1.empn