MySQL(四)：联接与集合操作，互联网大厂高频重点面试题

1. INNER JOIN所有过滤条件都可以写在ON中，但OUTER JOIN却不可以这样，会得到不正确的结果，因为处理完ON之后是会进行添加外部行的，添加完外部行才执行WHERE（如果把WHERE的过滤写在ON子句中，那么外部行将不会被进行过滤），不像INNER JOIN，不需要添加外部行。

2. 与INNER JOIN还有一处不同，OUTER JOIN是必须规定ON子句的，INNER JOIN如果没有ON子句，那么就会变成CROSS JOIN，OUTER JOIN不会变成CROSS JOIN

NATURAL JOIN

MySQL还支持NATURAL JOIN（自然联接），NATURAL JOIN等同于INNER JOIN与USING的组合，它隐含的作用是将两个表中具有相同名称的列进行匹配，同样的，NATURAL LEFT(RIGHT) JOIN等同于LEFT(RIGHT) OUTER JOIN与USING的组合。

举个栗子

//一般内联接

SELECT a.emp_no,first_name,last_name

FROM employees a

INNER JOIN dept_manager b

ON a.emp_no = b.emp_no;

//使用USING省略

SELECT a.emp_no,first_name,last_name

FROM employees a

INNER JOIN dept_manager b

USING(emp_no);

//使用NATURAL JOIN替代

SELECT a.emp_no,first_name,last_name

FROM employees a

NATURAL JOIN dept_manager b;

STRAIGHT_JOIN

STRAIGHT_JOIN其实并不是新的联接类型，而是用户对SQL优化器的控制，等同于JOIN。通过STRAIGHT_JOIN，MySQL数据库会强制先读取左边的表，我们可以对比STRAIGHT_JOIN的SQL语句的执行计划，其SQL语句如下

SELECT a.emp_no,first_name,last_name

FROM employees a

INNER JOIN dept_manager b

ON a.emp_no = b.emp_no;

这条语句的执行过程是会先选择b表（右表），也就是dept_manager表，进行读取，然后进行匹配，如果使用STRAIGHT_JOIN会强制使用左表进行读取再匹配。

选择不同的表匹配有什么不同，这里假如b表24行，而a表有几万行，如果使用b表进行匹配，那么只需要匹配24次即可，但如果使用a表进行匹配，则需要进行几万次匹配，因为匹配此时主根据优先读取的表。

其他联接分类

SELF JOIN

SELF JOIN是同一个表的两个实例之间的JOIN操作，只是没有显示的归类而已，凡是跟同一个表的联接就是SELEF JOIN。

NONEQUI JOIN

前面的介绍的联接都是EQUAL JOIN（等值联接），即联接条件是基于"等于"运算符的联接操作，NONEQUI JOIN的联接条件是除了“等于”运算符之外的其他所有运算符。

SELECT a.emp_no,b.emp_no FROM dept_manager a

INNER JOIN dept_manager b

ON a.emp_no < b.emp_no;

SEMI JOIN 和ANTI SEMI JOIN

SEMI JOIN是根据一个表中存在的相关记录找到另一个表中相关数据的联接。如果从左表返回记录，该联接称为左半联接；如果从右表返回记录，该联接被称为右半联接。

SELECT DISTINCT c.customer_id,city

FROM customers AS c

JOIN orders AS o

ON c.customer_id = o.customer_id //使用c表存在的customer_id来找，普通JOIN,优先右表

WHERE c.city = ‘HangZhou’;

与SEMI JOIN相反的是ANTI SEMI JOIN，它是根据一个表中不存在的记录而从另一个表中返回记录，比如用OUTER JOIN并用WHERE过滤外部行。

SELECT c.customer_id,c.city

FROM customers AS c

LEFT OUTER JOIN orders AS o ON c.customer_id = o.customer_id

WHERE c.city = ‘HangZhou’ AND o.customer_id IS NULL; //根据o.customer_id不存在的记录返回

联接算法

联接算法是MySQL数据库用于处理联接的物理策略。最为通用的是Nested-Loops Join算法

但联接的表上有索引时，Nested-Loops Join是非常高效的算法，根据B+树的特性，其联接的时间复杂度为 O ( N ) O(N) O(N),若没有索引，可以视为最坏的情况，时间复杂度为 O ( N 2 ) O(N^2) O(N2)，MySQL数据库根据不同的使用场合，支持两种的Nested-Loops Join算法，一种是Simple Nested-Loops Join算法，另一种是Block Nested-Loops Join算法。

Simple Nested-Loops Join算法

Simple Nested-Loops Join从第一张表中每次读取一条记录，然后将记录与嵌套表中的所有记录进行比较，其算法如下。

For each row r in R do //遍历第一张表

For each row s in S do //遍历第二张表

IF<满足联接条件>

Then output the tuple <r,a>

Next s

Next r

R为外部表，S为内部表，现在假设两张表进行联接的索引都不含有索引，算法扫描的次数为 R n ∗ S n R_n*S_n Rn​∗Sn​， R n 和 S n R_n和S_n Rn​和Sn​分别代表R表和S表的记录数，所以时间复杂度为 O ( R n ∗ S n ) O(R_n*S_n) O(Rn​∗Sn​)

如果是多张表，比如三张，那么就是三个嵌套For循环了。

但是如果当内部表所联接的列中包含索引时，Simple Nested-Loops Join算法可以利用索引的特性来进行快速匹配，此时的算法会进行下列调整。

For each row r in R do //遍历外部表

lookup r in S index //在内部表的索引中找是否存在r来匹配

If found S == r //如果找到

Then output the tuple <r,a> //输出

Next r

对于联接的列含有索引的情况，外部表的每条记录不再需要扫描整张内部表，只需要扫描内部表的索引即可得到联接的判断结果，假设内部表联接列的索引高度为 S B H S_{BH} SBH​，那么执行的复杂度为 S B H ∗ R n S_{BH}*R_n SBH​∗Rn​，时间复杂度为 O ( S B H ∗ R n ) O(S_{BH}*R_n) O(SBH​∗Rn​)，而索引是由B+树来实现的，而B+树的高度大概为3~4层，因此在有索引的情况下，Simple Nested-Loops Join算法的执行速度是比较快的。

在INNER JOIN中，两张联接表的顺序是可以变换的，即下面的两条SQL是一样的

R INNER JOIN S ON

S INNER JOIN R ON

优化器一般情况下总是选择联接列内含有索引的表来作内部表（不可以做外部表，外部表的话，就要遍历内部表看是否在索引中了，不符合逻辑），如果两张表的联接列都有索引，那么优化器会选择将记录数最少的表作为外部表，这是因为内部表的扫描次数总是索引的高度，与记录的数量无关，所以选记录数少的表可以减少执行次数（其实在这里索引的高度也是有影响的，只不过索引的高度影响比不上记录数量的影响）。

Block Nested-Loops Join算法

上面说过Simple Nested-Loops Join对于没有索引的情况是比较不理想的，而Block Nested-Loops Join就是解决这个问题的，简单来说就是针对没有索引的联接情况设计的，其使用Join Buffer(联接缓冲，这里就先理解他是一个缓冲区吧)来减少内部循环读取表的次数。

例如，Block Nested-Loops Join算法先把对Outer Loop表（外部表）每次读取的10行记录（准确地说是10行需要进行联接的列）放入Join Buffer中，然后在Inner Loop表（内部表）中直接匹配这10行数据，因此，对Inner Loop表的扫描会减少十分之一，对于没有索引的表来说，Block Nested-Loops Join算法可以极大地提高联接的速度。

集合操作

通常来说，将联接操作看成是表之间的水平操作，因为该操作生成的虚拟表包含两个表中的列（相当于两个表水平拼在一起），而集合操作，一般将其视为垂直操作，MySQL数据库支持两种集合操作，一种是UNION ALL，另一种是UNION DISTINCT。

与联接操作一样，集合操作也是对两个输入进行操作，并且生成一个虚拟表，在联接操作中，一般把输入表称为左输入和右输入，或者第一个输入和第二个输入，集合操作的两个输入（即进行集合操作的两个表）必须拥有相同的列数，若数据类型不同，MySQL数据库会自动将进行隐式转化，同时，结果列的名称由第一个输入（也就是左表的列来决定）。

CREATE TABLE x(

a CHAR(1)

)ENGINE=INNODB,CHARSET=utf8;

INSERT INTO X VALUES(‘a’),(‘c’);

SELECT a AS m FROM X

UNION SELECT 1 AS n FROM DUAL UNION SELECT ‘abc’ AS o FROM DUAL UNION SELECT NOW() AS p FROM DUAL;

关于隐式转化

MySQL数据库会自动对其进行判断，并选出一种类型进行隐式转换。另一方面，在上面栗子中对每个选取操作都进行了对列名取别名，但最后的列名采用的是最左表的列别名。

集合操作中SELECT语句和一般的SELECT语句差不多，只不过有以下的两点区别

1. 只有最后一个SELECT可以应用INTO OUTFILE，但是整个集合的操作将被输出到文件中（虽然INTO OUTFILE虽然在最后与一个SELECT中，但导出的结果是整个集合操作的结果）。

2. 不能在SELECT语句中使用HIGH_PRIORITY关键字

还有一点要注意的是，若SELECT语句中使用了包含LIMIT和ORDER BY子句的，且集合操作是关于多条SELECT语句的（一般SELECT中的子查询都是多个SELECT的，不可以直接 t1 UNION t2，这是不正确的语法），最好的做法是将参与集合操作的各SELECT语句添加括号，否则执行集合查询会得到错误提示。

错误示范

LIMIT来添加括号

SELECT a FROM x ORDER BY a LIMIT 1 UNION SELECT a FROM y;

只对第一个数据添加括号

(SELECT a FROM x ORDER BY a LIMIT 1) UNION SELECT a FROM y;

正确写法

两边都加括号

(SELECT a FROM x ORDER BY a LIMIT 1) UNION (SELECT a FROM y);

UNION DISTINCT和UNION ALL

UNION DISTINCT是组合两个输入，并且应用DISTINCT过滤重复项，一般省略DISTINCT关键字，直接用UNION

举个例子

CREATE TABLE t7(

a CHAR(1)

)ENGINE=INNODB,CHARSET=utf8;

CREATE TABLE t8(

a CHAR(1)

)ENGINE=INNODB,CHARSET=utf8;

INSERT INTO t7 SELECT ‘a’;

INSERT INTO t7 SELECT ‘b’;

INSERT INTO t7 SELECT ‘c’;

INSERT INTO t8 SELECT ‘d’;

INSERT INTO t8 SELECT ‘e’;

INSERT INTO t8 SELECT ‘a’;

INSERT INTO t8 SELECT ‘b’;

<两条SQL执行起来是一样的>

SELECT a AS m FROM t7 UNION DISTINCT SELECT a AS b FROM t8;

SELECT a AS m FROM t7 UNION SELECT a AS b FROM t8;

UNION DISTINCT(UNION)的过程

1. 创建一张临时表，即虚拟表

2. 对这张临时表的列添加唯一索引进行去重（Unique Index）

3. 将输入的数据插入临时表

4. 返回虚拟表

因为添加了唯一索引，所以可以过滤掉集合中重复的项。

向临时表中添加了唯一索引，所以会影响插入速度（虽然一般不会去对临时表进行插入），所以如果确认了两张表是没有重复数据的，使用UNION ALL

UNION ALL组合两个输入中所有项的结果集，并包含重复的选项

SELECT a AS m FROM t7 UNION ALL SELECT a AS b FROM t8;

UNION DISTINCT可以使用UNION ALL然后加DISTINCT对列进行去重来代替吗？答案是不可的，因为执行的顺序不一样，集合操作可以理解成，是先将两个表生成的临时表进行合并的

举个栗子

SELECT DISTINCT a AS m FROM t7 UNION ALL SELECT a AS b FROM t8;

可以很清楚的看到并没有进行去重，这是因为执行顺序是这样的，先执行第一句SELECT，然后执行第二句SELECT，然后进行UNION ALL操作，也就是DISTINCT并不是对最后产生的临时表进行去重，而只是对所在的SELECT进行去重，所以要进行去重还是要用UNION DISTINCT

EXCEPT

EXCEPT集合操作允许用户找出位于第一个输入中但不位于第二个输入中的行数据，跟UNION一样，EXCEPT可以分为EXCEPT DISTINCT和EXCEPT ALL

EXCEPT DISTINCT返回位于第一个输入中但不位于第二个输入中的不重复行，对于执行这种需求常用的是LEFT JOIN或者NOT EXCEPT

//使用left join联接两张表，联接条件为两者输入中都有的数据、

//因为左表是保留表，所以右表为NULL的话，代表左表有，而右表没有

SELECT t7.a FROM t7 LEFT JOIN t8 ON t7.a = t8.a WHERE t8.a IS NULL;

//这里使用了关联子查询，首先关联子查询找到t8和t7表共有的数据

//然后使用EXISTS来从t7表中过滤关联子查询查出的共有数据

SELECT t7.a FROM t7 WHERE NOT EXISTS(SELECT t8.a FROM t8 WHERE t7.a = t8.a);

上面两种方式都可以查询成功，而且结果也一致。看似没有问题，但如果输入项中包含NULL值，情况就不是这么简单的了

举个栗子

这是t9表

这是t10表

那么如果进行上述的去重，结果应该是得到(‘e’,‘f’)，因为这个数据存在于t9表中，但不在t10表中

现在进行执行SQL

//使用左联结找到两表共同数据

//但由于左表是保留表，那么右表为NULL的话，代表左表有而右表无

SELECT a.a,a.b FROM t9 AS a LEFT JOIN t10 AS b ON a.a = b.a AND a.b = b.b

WHERE b.a IS NULL AND b.b IS NULL;

SELECT a.a,a.b FROM t9 AS a WHERE NOT EXISTS(SELECT b.a,b.b FROM t10 AS b WHERE b.b = a.b AND b.a = a.a );

结果显然不符合我们的预期

这是因为，在对

《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》

【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 完整内容开源分享

NULL进行比较的时候，返回的是NULL，即UNKNOWN。

那么怎么才能实现想要的功能呢

首先将两张表进行不排除重复进行合并（但两张表是要进行去重的，并且加一个标识列来表明是哪张表），然后进行分组，如果某一个分组的数据数量大于1，那么就肯定是两边都有的，如果为1，那就可能是左表有右表无，或者右表有左表无，此时再根据标识列是哪张表就可以了。

SELECT * FROM (