mysql几种join方式,mysql（7种join方式）-Go语言中文社区

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;