mysql（7种join方式）

7种join方式

不会画图，不会画图，不会画图
图片窃取自
https://blog.csdn.net/u012240455/article/details/83861776
若有不恰之处，请各位道友指正~

列一下基础数据啦

-- B
create table t_dept(
	id int(11) not null auto_increment,
	department varchar(20) DEFAULT null,
	iocAdd varchar(20) DEFAULT null,
	primary key(id)
)

-- A
create table t_emp(
id int(11) not null auto_increment,
`name` varchar(20) DEFAULT null,
deptId int(11) DEFAULT null,
primary key(id)
 )

-- insert into t_dept(department,iocAdd) VALUES ("RD","11");
-- insert into t_dept(department,iocAdd) VALUES ("HR","12");
-- insert into t_dept(department,iocAdd) VALUES ("MIS","13");
-- insert into t_dept(department,iocAdd) VALUES ("MK","14");
-- insert into t_dept(department,iocAdd) VALUES ("FD","15");
 
-- insert into t_emp(name,deptId) values('ze',1);
-- insert into t_emp(name,deptId) values('z3',2);
-- insert into t_emp(name,deptId) values('z4',3);
-- insert into t_emp(name,deptId) values('w9',9);

左连接

select * from t_emp emp left join t_dept dept on emp.deptId = dept.id; 

右连接

select * from t_emp emp right join t_dept dept on emp.deptId = dept.id; 

内连接

select * from t_emp emp inner join t_dept dept on emp.deptId = dept.id;

只有A

select * from t_emp emp left join t_dept dept on emp.deptId = dept.id
where dept.id is null;

只有B

where emp.id is null;

A 和 B 的全集

select * from t_emp emp left join t_dept dept on emp.deptId = dept.id
union 
select * from t_emp emp right join t_dept dept on emp.deptId = dept.id;

A和B去除公共部分

select * from t_emp emp left join t_dept dept on emp.deptId = dept.id
where dept.id is null
union
select * from t_emp emp right join t_dept dept on emp.deptId = dept.id
where emp.id is null;