all mysql union 多表 mysql join多个表

0.准备

join主要是根据两张表或多张表字段之间的关联关系，根据这些表字段之间的关联关系进行查询，从而得到我们想要的不同组合。首先创建两张表： tb_emp(员工表)和tb_dept(部门表)，并插入相关测试数据。
1.tb_emp表

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

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

2.tb_dept表

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

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

从上面插入的数据我们可以知道员工outman是没有对应部门的，以及经理部门是没有员工的。

1.inner join

注：A表表示员工表，B表表示部门表，下同。

inner join：也就是A,B共有，也就是交集。

这也是很常见的内连接，我们可以写出以下sql语句

select * from tb_emp A inner join tb_dept B on A.deptid=B.id;

执行结果如下图：

我们可以看到，执行结果并没有outman这个人，以及经理这个部门，因为这两个数据对应的都是各自表独有的部分。

2.left join

left join：A表独有+AB表共有

sql语句如下：

select * from tb_emp A left join tb_dept B on A.deptid=B.id;

执行结果：

3.right join

right join：AB表共有+B表独有

sql语句如下：

select * from tb_emp A right join tb_dept B on A.deptid=B.id;

执行结果：

4.A独有

A独有：参考left join（然后将AB共有部分给排除掉）

sql如下：

select * from tb_emp A left join tb_dept B on A.deptid=B.id where B.id is null;

执行结果如下：

5.B独有

B独有：参考right join（然后将AB共有部分给排除掉）

sql如下：

select * from tb_emp A right join tb_dept B on A.deptid=B.id where A.deptid is null;

执行结果如下：

6.AB全有并集

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

sql如下：

select * from tb_emp A left join tb_dept B on A.deptid=B.id
UNION
select * from tb_emp A right join tb_dept B on A.deptid=B.id;

执行结果如下：

7.AB独有

AB独有：A独有+B独有

sql如下：

select * from tb_emp A left join tb_dept B on A.deptid=B.id where B.id is null
UNION
select * from tb_emp A right join tb_dept B on A.deptid=B.id where A.deptid is null;

执行结果如下：

8.总结

以上就是mysql的其中join，因为mysql不支持full outer
join，所以需要用union对其进行转换变形，最终达到效果。