MySQL中的Join查询

2022-11-18

字数统计: 2k字 | 阅读时长: 7分

前言

业精于勤，荒于嬉。

闲谈

在整理Join相关的内容时，我提出了几个问题，整理到一个部分记录一下，想看“干货”的读者可以跳过了。

问题一：Join和Key有啥关系？
Key无非主、外、候选、公共之类的内容，一个row的identifier罢了，无非是对内对外，同时它也是一个field。那么我们在规划表结构、塞数据的时候就有了一个方便的方法，把想表达的一个row的数据用一个key概括，需要获取所有数据时通过多表查询即可。总之，笔者认为，Join和Key可以说没关系，Key在任何时候都发挥着identifier的作用。

问题二：MySQL中不用Key也能Join，为什么？
其实这个问题本身有点奇怪（我突然想出来的），首先关系代数中连接（Join）也没有要求一定要用键做连接，其次上面也说了这俩没多大关系。但我一搜吧，还真有个回答，主要是说外键约束有成本，对高并发情况不合适之类的，一时不知道是我有问题还是理解不到位，知道的大神可以告诉我，感谢。

问题三：为什么不能用Where替代Join?
关于这个问题，我简单思考了一下，首先就拿纯WHERE、JOIN和LEFT JOIN来说，我写了以下三个查询：

select *
from node_graph1 left join edge_graph1 on node_graph1.node_list = edge_graph1.node1_name;

select *
from node_graph1, edge_graph1 where node_graph1.node_list = edge_graph1.node1_name;

select *
from node_graph1 join edge_graph1 on node_graph1.node_list = edge_graph1.node1_name;

其中后两个的效果是一致的，而LEFT JOIN会返回node_graph1的所有结果，即使没有match到，这可能是WHERE做不到的一个地方。更多还是要在实践中发掘。

Join查询

SQL中Join用于根据两个或多个表中的列之间的关系，从这些表中查询数据。日常使用中对多表查询有广泛的需求，Join查询自然是必不可少。

用Join联合表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。联合表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间。

那么SQL中的Join都有哪些呢？先上一张总览：

overview

下面开始逐个说一下。

1. 内连接（Inner Join)

INNER JOIN 是 SQL 中最重要、最常用的表连接形式，只有当连接的两个或者多个表中都存在满足条件的记录时，才返回行。任何一条只存在于某一张表中的数据，都不会返回。

2. 左外连接（Left Outer Join)

LEFT OUTER JOIN 以左表为主，即左表中的所有记录都会被返回，具体分为以下三种情况：

如果 TableA 中的某条记录在 TableB 中刚好只有一条记录可以匹配，那么在返回的结果中会生成一个新的行。
如果 TableA 中的某条记录在 TableB 中有 N 条记录可以匹配，那么在返回结果中也会生成 N 个新的行，这些行所包含的 TableA 的字段值是重复的。
如果 TableA 中的某条记录在 TableB 中没有匹配的记录，那么在返回结果中仍然会生成一个新的行，只是该行所包含的 TableB 的字段值都是 NULL。
3. 右外连接（Right Outer Join)
RIGHT OUTER JOIN 以右表为主，即右表中的所有记录都会被返回，具体分为以下三种情况：
如果 TableB 中的某条记录在 TableA 中刚好只有一条记录可以匹配，那么在返回的结果中会生成一个新的行。
如果 TableB 中的某条记录在 TableA 中有 N 条记录可以匹配，那么在返回的结果中也会生成 N 个新的行，这些行所包含的 TableB 的字段值是重复的。
如果 TableB 中的某条记录在 TableA 中没有匹配记录，那么在返回结果中仍然会生成一个新的行，只是该行所包含的 TableA 的字段值都是 NULL。
4. 左外连接 with exclusion（Left Outer Join with exclusion)
在左外连接的基础上，去除TableB可匹配到的部分，只返回B.Key为NULL的记录。
5. 右外连接 with exclusion（Right Outer Join with exclusion)
在右外连接的基础上，去除TableA可匹配到的部分，只返回A.Key为NULL的记录。
6. 全外连接（Full Outer Join）
FULL OUTER JOIN 先执行 LEFT OUTER JOIN 遍历左表，再执行 RIGHT OUTER JOIN 遍历右表，最后将 RIGHT OUTER JOIN 的结果直接追加到 LEFT OUTER JOIN 后面。注意，FULL OUTER JOIN 会返回重复的行，它们会被保留，不会被删除。
7. 全外连接 with exclusion（Full Outer Join with exclusion）
两表的FULL OUTER JOIN去除重合部分，也就是返回左外连接 with exclusion 和右外连接 with exclusion 的 FULL OUTER JOIN 记录。

MySQL支持的Join方式

在聊这个之前，先简单了解一下驱动表和被驱动表的概念。在LEFT OUTER JOIN时，左表为驱动表，右表为被驱动表；在RIGHT OUTER JOIN时，右表为驱动表，左表为被驱动表。关于驱动表和被驱动表的作用，实际上是与MySQL表关联算法和SQL优化有关的，通常来说，用小表驱动大表能够获得更高的效率，这里不详细展开了。

以MySQL8.0.11为例，MySQL提供的JOIN关键字有：JOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、RIGHT OUTER JOIN、CROSS JOIN和STRAIGHT_JOIN。

其中，JOIN和INNER JOIN为内连接，LEFT JOIN与LEFT OUTER JOIN是等价的，都对应着左外连接（右也是一样的道理）。也就是说，上面提到的七种JOIN方式，MySQL关键字只支持前三种，对于4、5可以结合WHERE来实现，但不提供全外连接关键字。

这三种（或六个）关键字的通用Join查询结构如下：

SELECT <row_list> 
  FROM <left_table> 
    <inner|left|right> JOIN <right_table> 
      ON <join_condition> 
        WHERE <where_condition>

可以看到这里有两种条件，分别是join_condition和where_condition，两者执行存在先后顺序。数据库通过JOIN关键字返回记录时会先生成一张临时表，通过临时表返回记录，join_condition是在生成临时表时使用的条件，而where_condition是在临时表生成后再对其进行过滤的条件。以LEFT JOIN为例，在生成临时表时无论join_condition是否为真都会将左表记录加入到临时表中，所以“左表中的所有记录都会被返回”。

那么CROSS JOIN和STRAIGHT_JOIN又是什么呢？

CROSS JOIN子句从连接的表返回行的笛卡儿积，它的通用查询结构如下：

SELECT <row_list> 
  FROM <left_table> 
    CROSS JOIN <right_table> 
        ON <join_condition>
            WHERE <where_condition>

注意，仅当不添加join_condition和where_condition的时候，CROSS JOIN才能返回笛卡尔积，如果添加了这些条件，那么工作方式将和JOIN相同。

至于STRAIGHT_JOIN，其实是提供给用户一种自主决定驱动表与被驱动表关系的方式，它的用法与JOIN相同，只是STRAIGHT_JOIN前面的表一定是驱动表，后面的表一定是被驱动表。而在MySQL中，JOIN会自动选择小表作为驱动表，大表作为被驱动表。用户可以使用STRAIGHT_JOIN来解决MySQL优化器不能解决的部分。

关于全外连接以及其他各种连接方式在MySQL中的实现，我找到了一张图，是由Steve Stedman制作的，供读者参考。

MySQLJoinType