MySQL高级知识（二）——join查询

该篇目主要对MySQL中的join语句的七种情况进行总结。

准备

join主要根据两表或者多表之间的关系，从这些表中进行数据的查询。
首先创建两张表，emp（员工表）和dept（部门表），并插入相关测试数据。

1. emp表

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `deptid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_emp_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `emp`(name,deptid) VALUES ('jack', '1');
INSERT INTO `emp`(name,deptid) VALUES ('tom', '1');
INSERT INTO `emp`(name,deptid) VALUES ('tonny', '1');
INSERT INTO `emp`(name,deptid) VALUES ('mary', '2');
INSERT INTO `emp`(name,deptid) VALUES ('rose', '2');
INSERT INTO `emp`(name,deptid) VALUES ('luffy', '3');
INSERT INTO `emp`(name,deptid) VALUES ('outman', '14');

数据库中的数据情况：

2. dept表

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `dept`(deptname) VALUES ('研发');
INSERT INTO `dept`(deptname) VALUES ('测试');
INSERT INTO `dept`(deptname) VALUES ('运维');
INSERT INTO `dept`(deptname) VALUES ('经理');

数据库中的数据情况：

1. inner join

标注：A表示左表，B表示右表，以下同。
inner join : A、B共有，也就是交集。

例如：select * from emp e inner join dept d on e.deptid = d.id

2. left join

left join：A独有+AB共有（交集）

例如：select * from emp e left join dept d on e.deptid = d.id

3. right join

right join：B独有+AB共有（交集）

例如：select * from emp e right join dept d on e.deptid = d.id

4. A独有

例如：select * from emp e left join dept d on e.deptid = d.id where d.id is null

注意：参照left join，A独有 只是将AB交集部分去掉

5. B独有

例如：select * from emp e right join dept d on e.deptid = d.id where e.deptid is null

注：参照right join，B独有只是将AB交集部分去掉。

6. AB全有（并集）

例如：

select * from emp e left join dept d on e.deptid = d.id
union
select * from emp e right join dept d on e.deptid = d.id

由于mysql不支持full outer join，所以这里通过union进行转换。AB并集 = AB交集 + A独有 + B独有。

7. A、B独有并集

A、B独有并集，相当于A、B全有去掉AB的共有（交集）
例如：

select * from emp e left join dept d on e.deptid = d.id where d.id is null
union 
select * from emp e right join dept d on e.deptid = d.id where e.deptid is null