7种join的sql编写

关于join的八张图

图片来自：https://www.runoob.com/sql/sql-join.html

建表
部门表

CREATE TABLE `tbl_dept`(
`id` INT(11) not NULL auto_increment,
`deptName` VARCHAR(30) DEFAULT NULL,
`idAdd` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)

) ENGINE=INNODB auto_increment=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,idAdd) VALUES ('RD',11);
INSERT INTO tbl_dept(deptName,idAdd) VALUES ('HR',12);
INSERT INTO tbl_dept(deptName,idAdd) VALUES ('MK',13);
INSERT INTO tbl_dept(deptName,idAdd) VALUES ('MIS',14);
INSERT INTO tbl_dept(deptName,idAdd) VALUES ('FD',15);

id	deptName	idAdd
1	RD	11
2	HR	12
3	MK	13
4	MIS	14
5	FD	15

员工表

CREATE TABLE `tbl_emp`(
`id` INT(11) not NULL auto_increment,
`name` VARCHAR(20) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` foreign key (`deptId`) REFERENCES `tbl.dept` (id)
) ENGINE=INNODB auto_increment=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_emp(NAME,deptId) VALUES ('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES ('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES ('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES ('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES ('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES ('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES ('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES ('s9',51);

inner join
AB共有
取两表的共有部分，员工s9和部门id为51的数据剔除了。

SELECT * from tbl_emp a INNER JOIN tbl_dept b on a.deptid = b.id
-------------------------------------
id	name	deptId	id(1)	deptName	idAdd
1	z3		1		1		RD			11
2	z4		1		1		RD			11
3	z5		1		1		RD			11
4	w5		2		2		HR			12
5	w6		2		2		HR			12
6	s7		3		3		MK			13
7	s8		4		4		MIS			14

left join
左表+两表共有
以员工表为基础，部门表中有相应部门信息的加进来，没有的话，相应字段为空

SELECT * from tbl_emp a left JOIN tbl_dept b on a.deptid = b.id
id	name	deptId	id(1)	deptName	idAdd
1	z3		1		1			RD		11
2	z4		1		1			RD		11
3	z5		1		1			RD		11
4	w5		2		2			HR		12
5	w6		2		2			HR		12
6	s7		3		3			MK		13
7	s8		4		4			MIS		14
8	s9		51		Null		Null	Null	

right join
右表+两表共有
以部门表为基础，员工表中有相应员工信息的加进来，没有的话，相应字段为空

SELECT * from tbl_emp a right JOIN tbl_dept b on a.deptid = b.id
id	name	deptId	id(1)	deptName	idAdd
1	z3		   1	1		RD			11
2	z4		   1	1		RD			11
3	z5		   1	1		RD			11
4	w5		   2	2		HR			12
5	w6		   2	2		HR			12
6	s7		   3	3		MK			13
7	s8		   4	4		MIS			14
Null Null	  Null	5		FD			15

left join + B is null

去掉两表共有的，保留左表独有的

SELECT * from tbl_emp a left JOIN tbl_dept b on a.deptid = b.id where b.id is null
id	name	deptId	id(1)	deptName	idAdd
 8	 s9		 51		 Null	  Null		 Null

right join + A is null
去掉两表共有的，保留右表独有的

SELECT * from tbl_emp a right JOIN tbl_dept b on a.deptid = b.id where a.id is null
id	name	deptId	id(1)	deptName	idAdd
			   		  5	       FD		  15

full outer join–mysql用union实现
AB全有

SELECT * from tbl_emp a INNER JOIN tbl_dept b on a.deptid = b.id
union
SELECT * from tbl_emp a left JOIN tbl_dept b on a.deptid = b.id where b.id is null
union
SELECT * from tbl_emp a right JOIN tbl_dept b on a.deptid = b.id where a.id is null

A的独有+B的独有