MySQL高级学习之七种JOIN

七种JOIN

先准备两张表

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);

1. 获取A、B共有部分

SELECT * FROM tbl_emp a INNER JOIN tbl_dept b ON a.`deptId`=b.`id`;

我们可以看到没有deptId为51的员工以及id为5的部门.

2. A、B共有以及A的私有

SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.`deptId`=b.`id` ;

通过左外连接，A表中所有的数据都被查询了出来.

3. A、B共有以及B私有

SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.`deptId`=b.`id` ;

通过右外连接，B表中所有的数据都被查询了出来.

4. A私有

SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.`deptId`=b.`id` WHERE b.`id` IS NULL;

5. B私有

 ![](https://img2020.cnblogs.com/blog/1525547/202009/1525547-20200914180228832-233101859.png)

SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.`deptId`=b.`id` WHERE a.`id` IS NULL;

6. AB全有

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` ;

补充:因为MySQL不支持全查询，所以我们无法通过full join的方式去查询，但是可以通过UNION关键字来进行查询，

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

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.`id` IS NULL;