join&Union

id	name	id	name
1	zhangsan	1	hanmeimei
2	lisi	2	wangwu
3	wangwu

 

 

join:select * from A join B on A.name=B.name -----取交集

id	name	id	name
1	wangwu	2	wangwu

full/out join:select * from A full join B on A.name=B.name  ----取并集

id	name	id	name
1	zhangsan	null	null
2	lisi	null	null
3	wangwu	2	wangwu
null	null	1	hanmeimei

 

left join:select * from A left join B on A.name=B.name

id	name	id	name
1	zhangsan	null	null
2	lisi	null	null
3	wangwu	2	wangwu

right join: select * from A right join B on B.name

id	name	id	name
null	null	1	hanmeimei
3	wangwu	2	wangwu

 

Union: 会对两个结果集进行并集操作，不包括重复行

union all:对两个结果集进行并集操作，包括重复行

employee_china表:

 

 

 

employee_usa表:

 

 

union：

select E_ID,E_Name FROM employees_china
union
select E_ID,E_Name FROM employees_usa
去掉:

 

 

 

union all:

select E_ID,E_Name FROM employees_china
union all
select E_ID,E_Name FROM employees_usa
不能去掉:

 

 

  

当存在不相同的字段时: （employees_china 增加一个字段 E_Hello）

 

 必须进行补充

select E_ID,E_Name,E_Hello FROM employees_china
union all
select E_ID,E_Name,' ' as E_Hello FROM employees_usa

 

union vs join

union 要求多个结果集列数相同，列类型相同

join是列的联合