1、建表Sql
CREATE TABLE`tbl_dept`(
`id`INT(11) NOT NULL AUTO_INCREMENT,
`deptName`VARCHAR(30) DEFAULT NULL,
`locAdd`VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;
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`idx_dept_id`(`deptId`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd)VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd)VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd)VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd)VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd)VALUES('FD',15);
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);
2、以上面的建表为例,测试七种JOIN
1、select * from tbl_emp a inner join tbl_dept b on a.deptId =b.id;
2、select * from tbl_emp a left join tbl_dept b on a.deptId =b.id;
3、select * from tbl_emp a right join tbl_dept b on a.deptId =b.id;
4、select * from tbl_emp a left join tbl_dept b on a.deptId =b.id where b.id is null;
5、select * from tbl_emp a right join tbl_dept b on a.deptId =b.id where a.deptId is null;
6、MySQL不支持full outer join 这样的语法 ,但是在oracle可以适用。
select * from tbl_emp a full outer 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
union
select * from tbl_emp a right join tbl_dept b on a.deptId =b.id;
a,b表的公有+a表的独有+b表的独有
7、查询a,b表各自的独有
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.deptId is null;