mysql 内连接、左连接、右连接和全连接

前言：

        以前涉及 sql 查询都是用很简单的查询语句，然后用代码逻辑进行数据筛选，倒是也没啥问题。后来随着表数量的增加，经常需要好几张表联合一起进行筛选查询，这时候就发现有一些问题：筛选逻辑涉及比较多，用代码进行筛选，代码增量几十行上百行，而且代码维护也比较麻烦。如果用 sql 语句进行筛选，只增加几行代码，后期也好维护。

一、从 from 多表查询开始说起

看下面一条语句，

select * from emp,dept;

from 后面用逗号分隔的两张表查询跟题述要说的连接有什么异同吗？

先说说上述sql语句的情况：

from多表查询结果是笛卡尔积，

笛卡尔积：指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

概念有点抽象，举个例子：

集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}

不是太懂的话，再举个数据表的例子：

上述sql涉及的两张表如下：

 部门表：

CREATE TABLE `dept` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

数据：

雇员表 ：emp

CREATE TABLE `emp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '名称',
  `dept_id` int DEFAULT NULL COMMENT '部门id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

数据：

执行sql语句结果如下：

结果会显示两张数据表所有字段和所有组合，总数据条数=（emp数据条数 * dept数据条数），如果其中一张表为空，结果肯定是空，这个虽然很简单，但是经常被人忽略。from 后面还可以跟where条件语句，条件语句就是在上述的表中再次筛选返回结果。如果三张表就是三张表的笛卡尔积（总数据条数是单个表数据条数相乘）

二、内连接

内连接其实相对好理解，就是把多张表中重合的部分筛选出来，inner 可以省略，例如：

select * from dept inner join emp on emp.dept_id=dept.id;

执行结果：

三、左连接

左连接是以 from 后所有表的最左边表（左表）为核心，先全部显示左表数据，而后根据关联关系补充后续信息，例如：

select * from dept left join emp on emp.dept_id=dept.id;

可以这么理解，先显示左表信息，而后根据左表信息逐条在其它表查询，如果满足条件（on条件）就在后面补充其它表信息（多条满足就显示多行）。

如果是超过两张表，也是以左表为核心，依次从左到右的顺序进行连接。

四、右连接

跟左连接类似，只是以右表为核心（from 后所有表的最右边表），例如：

select * from emp right join dept on emp.dept_id=dept.id;

执行结果：

五、全连接

有些博主说 mysql 没有全连接，然后把左连接和右连接用 union 连接在一起称为全连接，这个也没问题。但是 union 主要不是这么用的，简单说 union 就是做多张表的加法（union all 是纯粹的加法，union 是去重的加法，下面会举例），使用 union 时字段名称可以不同，但是字段数量一定要相同，例如：

select id,dept_id from emp union (select id,name from dept);

执行结果：

另外：union 会把相同的结果进行合并（也就是去重），union all 不会触发合并，例如：

select id from emp union (select id from dept);
select id from emp union all (select id from dept);

两个结果分别：