sql常见的七种join

案例：创键两张表：

员工表与职位表，员工表中的depId字段，外键为职位表的id字段

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`)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

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 CHARACTER SET = 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);

select * from tbl_dept;

select * from tbl_emp;

第一种：A与B的交集 ，采用inner join

select * from tbl_emp a inner join tbl_dept b on a.deptId=b.id;

可以看出并没有查到：deptId为51的数据，只查两表的公共部分。

第二种：查询全A，采用left join

select * from tbl_emp a left join tbl_dept b on a.deptId=b.id;

若A中有数据，B中无数据，B的字段用NULL补齐。

第三种：查询全B，采用right join

select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;

若B中有数据，A中无数据，B的字段用NULL补齐。

第四种：查询A不交于B的部分 ，使用 where b.id is null;

select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null;

第五种：查询B不交于A的部分 ，使用 where a.deptId is null;

select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where a.deptId is null;

第六种：查询A与B的并集，使用 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的非交部分，使用 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.deptId is null;

总结：