join概述和7种join模式应用

join概述：

A JOIN B ON 连接条件；表示A和B通过某一连接条件进行相连

如果此处没有  ON 连接条件 ，系统也不会报错，而是直接用笛卡尔积的方式进行连接，可能会和自己本身连接表的想法不同。

 

 

将以上的集合分为三个区域：

1.AB交集区域:此处表示A和B的连接属性都不为NULL的元组部分。直接用join连接两表即是得到该区域。

2.A的独立区域:A不为NULL,B为NULL的部分。先左连接，即可得到1，2两个区域，然后再用where b.primarykey is null;即可去掉A,B交集的区域

3.B的独立区域:B不为NULL,A为NULL的部分。先右链接，即可得到1，3两个区域，然后再用where a.primarykey is null;即可去掉A,B交集的区域

 

区域的链接：

1.union为去重型连接，即连接时会将重复的区域清除，此方法因为要检测重复区域，所以效率会低一些。

2.union all非去除型连接，即连接不去重，重复部分计算多次，因直接连接，所以效率会高一些。

 

Mysql不支持full join 满连接，所以要用union来实现该功能。

 

实例：

#设A为表t_emp(人员表),B为表t_dept(门派表)

#1.所有有门派的人员信息
#A、B两表共有
select te.* ,td.*
from t_emp te join t_dept td
on te.deptId = td.id;
#2.列出所有用户，并显示其机构信息
#A的全集
select te.*
from t_emp te left join t_dept td  
on td.id = te.deptId ;
#3.列出所有门派
#B的全集
select td.*
from t_emp te right join t_dept td 
on td.id = te.deptId ;
#4.所有不入门派的人员
#A的独有
select te.*
from t_emp te left join t_dept td 
on td.id = te.deptId 
where td.id is null;
#5.所有没人入的门派
#B的独有
select td.*
from t_emp te right join t_dept td 
on td.id = te.deptId 
where te.id is null;
#6.列出所有人员和机构的对照关系
#AB全有
select *
from t_emp te left join t_dept td 
on td.id = te.deptId 
where td.id is null  #A的独有
union all 
select * 
from t_emp te right join t_dept td 
on td.id = te.deptId; #B的独有加公有
#7.列出所有没入派的人员和没人入的门派
#A的独有 + B的独有
select *
from t_emp te left join t_dept td 
on td.id = te.deptId 
where td.id is null
union all 
select *
from t_emp te right join t_dept td 
on td.id = te.deptId 
where te.deptId is null;

 

SQL99加入了新特性：

1.NATURAL JOIN(自然连接)

实例：

SELECT e.employee_id,d.department_name
FROM employees e NATURAL JOIN departments d;

NATURAL JOIN会自动去构建两个表的连接条件(找出所有的公共属性做为连接的连接条件)

 

2.USING：

实例：

SELECT e.employee_id,d.department_name
FROM employees e JOIN departments d
USING (department_id);

要保证两个属性的表的连接条件同名，此处实例的含义就是用department_id进行连接