多表查询(inner join、outer join和cross join)

employees表等的创建参考链接：https://www.cnblogs.com/muhai/p/16169598.html

一、内连接inner join

只返回两个表中联结字段相等的行

1、natural join自动联结两个表中字段名和字段类型一致的所有字段，如employees表和departments表的manager_id和department_id，而且使用natural join时选择的字段不能加前缀(不能对表定义别名，再在字段前面加上别名），即namager_id和department_id前面不可以加上限定词。

select last_name,manager_id,department_id,department_name from employees  natural join departments;
//功能等价于select last_name,e.manager_id,e.department_id,department_name from employees e join departments d on e.manager_id=d.manager_id and e.department_id=d.department_id;
//select last_name,manager_id,department_id,department_name from employees join departments using(manager_id,department_id);

根据manager_id和department_id两个字段联结，这里是自动将这两个字段联结

2、using指定连接两个表中字段名一致的字段manager_id，不要求字段类型一致，字段类型在一个类型组即可，这里是using指定，using指定的字段不能加限定符，如下面的manager_id前不能加上e.

select last_name,manager_id,e.department_id,department_name from employees e join departments using(manager_id);  //using里面的字段不能加限定符

根据manager_id字段联结

3、join on（即inner join on...）连接两个表中不同名的字段，不要求字段类型一致，字段类型在一个类型组即可。join on在字段列定义含糊不清时要加前缀

 select last_name,e.department_id,department_name from employees e join departments d on employee_id=d.manager_id;

联结employee_id和manager_id字段，这两个字段名不一致

4、条件筛选在on和where后面的区别

select e.last_name,e.department_id,d.department_name from employees e join departments d on e.department_id=d.department_id and d.department_id = 30 ;  
//在on后面，将两表内联且选择department表中department_id=30的行

select e.last_name,e.department_id,d.department_name from employees e join departments d on e.department_id=d.department_id where d.department_id = 30 ;  
//在where后面，将两表内联后再筛选department中department_id=30

可以发现内连接是筛选条件放在on后面和where后面都是一样的

 

二、outer join

1、left join（即left outer join），左外联结，返回字段关联相等时左边表和右边表组成的行，还有字段关联不相等时左边关联不成功的行，右边表字段为空组成的行

select last_name,e.department_id,d.department_id,department_name  from employees e left join departments d on e.department_id=d.department_id order by e.department_id;
//左外连接 = 内连接 + 左边表中失配的元组=106(内连接匹配106行)+1(左表在右表不匹配的一行，即左边表department_id是空的行)=107行
//等价于 select last_name,e.department_id,d.department_id,department_name from employees e,departments d where e.department_id=d.department_id(+) order by e.department_id;

条件筛选在on和where后面的区别

select last_name,e.department_id,d.department_id,department_name  from employees e left join departments d 
on e.department_id=d.department_id and d.department_id=30 order by d.department_id desc;  
//筛选条件在on后面，先将两表关联，筛选department_id=30，再将左边表department_id不等于30的数据加上去(包括空值)，即6+101=107行

select last_name,e.department_id,d.department_id,department_name  from employees e left join departments d  
on e.department_id=d.department_id where d.department_id=30 order by d.department_id desc;  
//筛选条件在where后面相当于左右两个表先关联形成107条数据的大表，之后再筛选department表中id=30的表，即6行

where筛选相当于内连接

2、right join（即right outer join），右外联结，返回字段关联相等时右边表和左边表组成的行，还有字段关联不相等时右边关联不成功的行，左边表字段为空组成的行

select last_name,e.department_id,d.department_id,department_name from employees e right join departments d on e.department_id=d.department_id;  
//右外连接 = 内连接 + 右边表中失配的元组=106(内连接的106行)+16(右边表在左边表不匹配的16行)=122行
//等价于 select last_name,e.department_id,d.department_id,department_name from employees e,departments d where e.department_id(+)=d.department_id;

条件筛选在on和where后面的区别

select last_name,e.department_id,d.department_id,department_name from employees e right join departments d 
on e.department_id=d.department_id and d.department_id=30 order by d.department_id desc;
//筛选在on之后，先将两表关联，选择department_id=30的行，然后将右表中department_id不等于30的行加上去，即6+26=32行

select last_name,e.department_id,d.department_id,department_name from employees e right join departments d 
on e.department_id=d.department_id where d.department_id=30 order by d.department_id desc;
//筛选在where之后，先将两表关联，然后选择department_id=30的行，即6行

where相当于内连接

3、full join（即full outer join），全联结

select last_name,e.department_id,d.department,department_name from employees e full join departments d on e.department_id=d.department_id;  
//全外联结=内连接+左边表中失配的元组+右边表中失配的元组,即106+1+16=123

select last_name,e.department_id,d.department_id,department_name from employees e full join departments d 
on e.department_id=d.department_id and d.department_id=30;
//筛选在on之后，6+101+26=133行

 select last_name,e.department_id,d.department_id,department_name from employees e full join departments d on e.department_id=d.department_id where d.department_id=30;  
//筛选在where之后，6行

 

三、笛卡尔积(cross join)

左边表每一行与右边表每一行组成新的一行。

select last_name,department_name from employees cross join departments;
//等价于select last_name,department_name from employees,departments;

 

四、非等值连接(Non-equi-joins)和自连接(Self-join)

非等值连接nonequijoins，筛选工资在某一返回之间

 select e.last_name, e.salary from employees e join sal s on e.salary between s.sal_mon and s.sal_fri;

自连接Self-join，查看某一员工的上级名字

select worker.last_name emp,manager.last_name mgr from employees worker join employees manager on worker.manager_id=manager.employee_id;