一、outer join介绍

外联接分为左外联接、右外联接，用于两个或多个表关联查询。左外连接（left join）中，结果集返回左表中的所有记录和右表中匹配的记录，其中左表未匹配的行显示为NULL。

外连接（outer join）的用法

• 在外连接中（outer join），建议大家只用left join
• outer join的前提条件是join后数据量不能发生变化，即连接条件为1:n
• 在left join中，当被连接表的条件写到where子句时，外连接的语义就破坏了，此时left join将退化为inner join。

# deptno编号为10的部门是ACCOUNTING，未匹配到的行显示为NULL，且按照e表顺序打印。

mysql> select e.* ,d.dname from emp e 
       left join dept d on e.deptno=d.deptno and d.dname='ACCOUNTING';
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | NULL       |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | NULL       |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | NULL       |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | NULL       |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | NULL       |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | NULL       |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 | NULL       |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | NULL       |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 | NULL       |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | NULL       |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | NULL       |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
14 rows in set (0.00 sec)

a.连接条件必须为1:n

在left join中，最终结果集的行数必须为左表（a表）行。如果被连接表（d表）有重复值，就会影响结果集的总行数，所以在left join之前，必须在被连接表去重。
示例中通过distinct消除left join右边表连接条件（deptno）的重复值，这样就满足了1:n的关系。

select e.*,d.* from emp e
    left join 
       (select distinct d.deptno,d.dname from dept d) d on a.deptno=d.deptno
    and e.ename='JACK';

b.被连接表的条件不能写在where子句

错误写法
被连接表的条件写到where关键字后面时，将会过滤掉了结果集中右表为NULL的行，这就破坏了left join的语义，所以优化器会改写为inner join。在join实现中，被连接表先join，再过滤。

mysql> select e.* ,d.dname from emp e 
       left join dept d on e.deptno=d.deptno
			 where d.dname='ACCOUNTING';
+-------+--------+-----------+------+------------+---------+------+--------+------------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm | deptno | dname      |
+-------+--------+-----------+------+------------+---------+------+--------+------------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 | ACCOUNTING |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 | ACCOUNTING |
+-------+--------+-----------+------+------------+---------+------+--------+------------+
3 rows in set (0.00 sec)

正确写法
被连接表的条件写到on后面时，e.deptno=d.deptno 和 d.dname='ACCOUNTING’都会作为连接条件，结果集不会过滤d.dname='ACCOUNTING’的行。在join实现中，被连接表先过滤，再join。
深刻理解：on关键字是连接条件，where关键字是过滤条件。

mysql> select e.* ,d.dname from emp e 
       left join dept d on e.deptno=d.deptno and d.dname='ACCOUNTING';
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | NULL       |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | NULL       |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | NULL       |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | NULL       |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | NULL       |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | NULL       |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 | NULL       |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | NULL       |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 | NULL       |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | NULL       |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | NULL       |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
14 rows in set (0.00 sec)

深入理解left join

left join后的结果集显示左表所有数据，并携带右表的数据。过滤条件在on后面时（不管过滤条件来自连接表还是被连接表），都会完整显示左表数据，而且会把携带的右表数据中条件不为真的数据显示为NULL。而过滤条件（不管过滤条件来自连接表还是被连接表）在where后面时，都会对join后的结果集进行过滤，所以左边数据不一定能完整显示。

虽然left join 通常将左边表视为驱动表，‌但在实际的数据库查询执行过程中，‌是否左边表作为驱动表还取决于优化器成本估算。‌所以，‌为了保证 left join 的语义正确性，‌应将被连接表的过滤条件放在 on 后面，‌而不是where 后面，‌以避免可能的执行计划变化导致结果不一致。

left join中，on和where条件的区别

本文结论属个人总结，如有错误，欢迎指正。

1. on条件是在生成临时表时使用的条件，不管on中的条件是否为真，它只会对右表进行条件过滤，不会对左表数据产生任何影响，所以它会返回左边表中的所有记录；
2. on后面的条件不论来自驱动表还是被驱动表，都属于连接条件的一部分，不影响左表数据；

# 括号中的均属于连接条件，而非过滤条件。
t1 left t2 on (t1.id=t2.id and t1.name='xx' and t2.name='xab') 

3. where中的被连接表（右表）的条件，是在临时表生成好后，再对临时结果集过滤，所以条件不为真的全部过滤掉，就会导致最终结果集中左表数据不完整（转换为inner join了，类似于等值连接）；
4. where中的连接表（左表）的条件，是先条件过滤，然后拿过滤后的结果集去做连接，产生的结果集行数就是过滤后结果集的行数。

二、outer join的实现 - nested loop详解

在mysql8.0.18之前，join的实现方式只有一种，即 nest loop双循环。

nest join优化思路

• 驱动表要小，或者又良好的过滤条件使得最终数据量变少
• 驱动表的过滤条件上有索引
• 被驱动表的连接条件上要有索引
• 被驱动表如果有很多重复值，需要使用group by或者distinct去掉

nest loop的执行顺序

以下面sql为例，把两个表join过程分为9个步骤，以下是运行原理和优化建议。

select a.c1,...,b.c1,... 
  from a left join b on a.id = b.id and b.c2 = 10;
where 
  and a.c3 = 'xiaomo'   # 有索引
  and a.c4 ......
  limit 10,10;

第1步：驱动表的索引起始位
索引起始位，驱动表在where过滤条件中的列（a.c3= ‘xiaomo’）。

第2步：驱动表查询条件的索引扫描
a.c3= 'xiaomo’的索引，顺序扫描到a.c3= 'xiaomo’的行，拿到对应的pk值。
（innodb二级索引=索引列+pk）

第3步：索引查到对应pk后的回表过程

• 回表原因，select列或者where条件中含有索引列之外的列；
• 索引列是有顺序的，但是对于的pk就没有排序，所以回表产生大量randow access I/O。如果数据量较大，回表相对较慢；
• 执行计划出现MRR时，会减少回表随记I/O，但又会出现排序错乱。

第4步：驱动表查索引后的回表部分过滤
主要是回表后where条件（a.c3 = ‘xiaomo’）和select查询列过滤后的结果集上，进行二次过滤（因为还有其他where条件），主要表现在filter部分，如果filter值太小，可以考虑创建联合索引。

第5步：驱动表与被驱动表的连接部分
被驱动表连接条件需要有索引，因为要通过a.id在被驱动表索引列定位相关行，便于拿到pk值。其实，这里的a.id就是用a.c3= 'xiaomo’计算出来的。

优化思路：如果有group by时，先执行group by拿到这个结果集，再跟另一个表进行连接，减少join次数。就是缩小了这部分的重复值，达到优化效果。另外semi join优化也在这部分。

第6步：被驱动表的索引
关联条件b.id列的索引，拿到对应的pk值。一般ref、eq_ref比较普遍，如果为all，则考虑创建索引。

第7步：被驱动表查索引后的random access I/O

优化思路：
这里使用的技巧有延迟join
回表数据量大时，可以用到bka算法，即pk排序（跟驱动表的MRR是同一个东西）

第8步：被驱动表回表部分过滤
如果fileter过滤不好时，可以考虑创建联合索引（连接条件+过滤条件）。示例中为b.c2 = 10，创建联合索引（b.id + b.c2）

第9步：join后的结果集返回客户端
这里涉及的优化就是limit进行分页，还有select只查询必要的列，减少网络IO。

扩展 - hash join介绍

在mysql8.0.18版本推出了hash join，用于替代嵌套循环。并在mysql8.0.20中做了增强，可以全面支持non-equi-join，Semijoin，Antijoin，Left outer join/Right outer join等。

优化思路：
a. hash join性能一般，不太推荐。尤其在oltp环境中，不建议大表运算；
b. 驱动表的结果集越小越好，hash碰撞成本较低。

三、outer join的实际运用

1、等值连接，连接表先过滤，再join，最后过滤被连接表的条件。

a join b on (a.id=b.id) where a.name='jin' and b.name='hai';   

2、外连接，连接条件写在on后面

a left join b on (a.id=b.id and b.name='hai')

#错误写法：连接表的条件出现在on后面，无法降低驱动表结果集，建议放在where后面。
a left join b on (a.id=b.id and a.name='jin')

3、过滤条件在where后面

a left join b on (a.id=b.id) where a.name='jinhai';

#错误写法：被连接表条件出现在where时，外连接转内连接。因此破坏了语义，属于错误写法，建议放在on后面。'
a left join b on (a.id=b.id) where b.name='jinhai';