YashanDB SQL查询篇：内外连接的区别与转化方法

01 前言

在SQL查询中，OUTER JOIN（外连接）与INNER JOIN（内连接）是两种核心的连接方式。OUTER JOIN通过保留左表、右表或全表的全部记录，即使另一侧表中无匹配数据，也能返回结果（缺失部分以NULL填充）。然而，在某些场景下，开发者需要将OUTER JOIN的逻辑转换为INNER JOIN，以优化性能、简化查询逻辑或规避NULL值的处理复杂度。本文将从技术角度探讨这一转换的意义及实现方法。

02 OUTER转INNER的概念与意义

JOIN的基本含义

在关系型数据库里面，每个实体有自己的一张表（table），所有属性都是这张表的字段（field），表与表之间根据关联字段"连接"在一起。所以，表的连接是关系型数据库的核心问题。

常见的JOIN类型主要分为INNER JOIN、LEFT/RIGHT OUTER JOIN、FULL OUTER JOIN、CROSS JOIN、ANTI/SEMI JOIN，本文主要涉及其中OUTER JOIN和INNER JOIN部分，其余具体逻辑这里不展开讨论。

在接下来的所有演示中，我们将基于如下两个表进行（建表语句获取详见文末）：

表1 机构信息表 branch

branch_no	branch_name	area_no	address
0001	深圳
0102	南京	01	City of Nanjing
0101	上海	01	上海市静安区
0501	武汉
0201	成都	02

表 2-区域信息表 area

area_no	area_name	DHQ
01	华东	Shanghai
02	华西	Chengdu
03	华南	Guangzhou

1、INNER JOIN

当我们想查询出机构所在的地区时，很自然地会写下如下SQL：

SELECT b.branch_name, a.area_name
FROM branches b
INNER JOIN area a
ON a.area_no = b.area_no;

两张表基于area_no字段进行关联，查询返回的数据满足area_no不为空，且两表的area_no字段相等。此时SQL语句中的on谓词起到的是过滤作用，将两表关联产生的笛卡儿积的数据量过滤为了其中满足此INNER JOIN条件的数据量。

2、OUTER JOIN

如果我们想保留来自左表的数据的前提下，做相同查询，此时将会使用LEFT OUTER JOIN。

SELECT b.branch_name, a.area_name
FROM branches b
LEFT OUTER JOIN area a
ON a.area_no = b.area_no;

在OUTER JOIN中，ON条件的作用不再是过滤，而是补空。满足此JOIN 条件的数据将返回，不满足的部分会将来自右表的部分补空（即用NULL值填充这个字段）。

OUTER JOIN与INNER JOIN的异同

二者对于结果集中满足JOIN条件的行是一样的，但OUTER JOIN会额外返回不满足条件的行，并将不匹配的部分用NULL补齐。下面对比二者结果集差异：

可以看到OUTER JOIN是在INNER JOIN结果集的基础上，根据补空边增加了相应的补空行。接下来我们将进一步讨论为什么要将OUTER转INNER以及如何将OUTER 转为INNER。

03 OUTER转INNER的优势

OUTER转INNER往往能提升SQL语句的执行性能。主要原因在于以下几点。

INNER JOIN有更灵活的JOIN顺序

INNER JOIN具有交换律和结合律的特性，这意味着优化器可以自由地重新排列JOIN顺序，寻找最优的执行路径。

想象一个有5个表连接的复杂查询，优化器可以评估数十种甚至上百种不同的JOIN顺序组合，从中选择代价最低的一个。然而OUTER JOIN（特别是Left/Right OUTER JOIN）具有严格的顺序依赖性，左表必须在前，右表必须在后，这种刚性结构严重限制了优化器的发挥空间。当我们成功将OUTER JOIN转换为INNER JOIN后，优化器往往能选择出更优的JOIN顺序。

INNER JOIN 拥有更小的性能开销

从执行效率的角度来看，OUTER JOIN本质上比INNER JOIN有着更复杂的执行逻辑。

当我们执行一个LEFT OUTER JOIN时，数据库引擎不仅需要处理匹配成功的行，还需要特别关注左表中那些在右表找不到匹配的行。对于这些不匹配的行，引擎必须进行额外的NULL填充操作，这需要维护额外的状态信息或使用特殊的标记位。这种额外的处理逻辑直接转化为更高的CPU开销和内存占用。相比之下，INNER JOIN只需要关心匹配成功的行，执行路径更加简单直接。在实际测试中，同等数据量下，OUTER JOIN的执行时间往往比INNER JOIN高出20%-30%，在大型表连接时这个差距会更加明显。

INNER JOIN能适用更多的优化规则

要想尽可能提高SQL语句的执行效率，优化器的各类优化规则必不可少。为了严格保证语义的正确性，优化器有一些优化规则无法支持OUTER JOIN的场景，例如一些谓词下推的优化规则，聚合或分组下推的优化规则，都是只在INNER JOIN场景生效。将OUTER转为INNER就可以利用上这些优化规则，更有利于选出最优执行计划。

04 OUTER转INNER的原理与方法

优化器一切改写逻辑的基本要求是保证语义等价性，即转换前后的查询必须产生完全相同的结果集。在OUTER转INNER的场景下，需要确保OUTER JOIN中那些因不匹配而被NULL填充的行，在转换为INNER JOIN后不会影响最终结果。

在前文已经提到过，OUTER JOIN 和INNER JOIN结果集的差别在于那些补出NULL的行，我们可以聚焦这些NULL值的行，探寻能够满足二者等价关系的场景。

下面介绍三种能将OUTER转INNER的场景，以及其背后的原理。

过滤空值

SELECT b.branch_name, a.area_name
FROM branches b
LEFT OUTER JOIN area a
ON a.area_no = b.area_no
WHERE a.area_no is not null;

在这个场景中，利用where谓词将JOIN结果集中的满足a.area_no为空的行全部过滤掉了，回顾第一节中我们比较OUTER与INNER结果的部分，可以发现这个过滤操作将补空操作补出的空值行都过滤掉了。在此场景下，OUTER JOIN与INNER JOIN 结果集一致，我们可以将此SQL语句中的OUTER JOIN改写为INNER JOIN。

在何种场景下我们可以像本例一样，将OUTER JOIN改写为INNER JOIN呢？这需要引入“空值拒绝”概念。

1、空值拒绝

通过where的过滤条件，将结果集中来自补空边被补空的行全部过滤了，从而实现了使INNER JOIN与OUTER JOIN等价的结果。我们将这类能过滤掉空值的条件称为空值拒绝条件。

常见的空值拒绝条件有以下这些：

各类比较操作 =, >, <, >=, <=, !=
直接过滤空值 is not null
模式匹配谓词 like 等

这些谓词的特征在于，null参与的过滤，返回的结果都是false，即这一行不被返回。

然而，谓词对于空值的拒绝作用不能只依据谓词种类来判断，还需要结合参与谓词运算的表达式的性质综合考虑。

将刚刚的例子稍作改写，成为如下形式：

SELECT b.branch_name, a.area_name, nvl(a.area_no,'1') 
FROM branches b
LEFT OUTER JOIN area a
ON a.area_no = b.area_no
WHERE nvl(a.area_no,'1') is not null;

改写后，再次执行结果，会发现此时OUTER JOIN的执行结果不再与INNER JOIN一致，这是因为nvl函数打破了空值拒绝的条件。为描述这类会打破空值拒绝条件的表达式，这里引入“空值敏感”概念。

2、空值敏感

考虑如下表达式expression = f(arg1,arg2,..)当任何一个参数为空时，表达式执行结果可能不为空，则称这个表达式为空值敏感。

空值敏感表达式因需要关注空值，对于OUTER JOIN补空后的空值做了处理，从而导致本可过滤掉空值的空值拒绝条件失效。此时，不能等价地将OUTER JOIN改写为INNER JOIN。因此在实际改写时，应谨慎地判断空值拒绝条件的成立性，确保补空的行全都被过滤掉。

3、空值拒绝的传递性

过滤条件(filter)上的传递性

SELECT b.branch_name, a.area_name
FROM branches b
LEFT OUTER JOIN area a
ON a.area_no = b.area_no
WHERE a.area_no is not null or b.area_no is not null;

上面例子中，由于“or”取的是二者的并集，此时or的右边是对非补空边的过滤条件，并不构成补空边的空值拒绝，因此整个or条件返回的数据不满足空值拒绝。

类似地，如果是and条件的场景下，由于“and”取的是二者的交集，则and两边只要有一边满足空值拒绝，整个and条件就满足空值拒绝。

表达式上的传递性

SELECT b.branch_name, a.area_name
FROM branches b
LEFT OUTER JOIN area a
ON a.area_no = b.area_no
WHERE (a.area_no || b.area_no) + a.area_no is not null;

空值拒绝在表达式上的传递性依赖于前面提过的空值敏感的判断。如果一个表达式是空值敏感的，这个表达式作为任何非空值敏感的表达式的参数时，得到的复合表达式整体仍为空值敏感。

在上面例子中，空值拒绝条件(is not null)用到的表达式的根节点为加法，而加法本来不是一个空值敏感表达式，但是由于加法左边出现了一个空值敏感的字符串连接操作，整个复合表达式就变为了空值敏感表达式，使过滤条件不再具有空值拒绝作用。

查询块之间的传递性

SELECT * 
FROM area 
WHERE a.area_name in
  (SELECT a.area_name
  FROM branches b
  LEFT OUTER JOIN area a
  ON a.area_no = b.area_no);

在IN子查询的过滤条件中，子查询返回的空值不会使IN条件为真，因此可以将这个约束条件带入子查询中，等价与在子查询中执行了一个“a.area_name=a.area_name”的过滤条件，此时空值拒绝从父查询传递到了子查询，子查询产生了一个对于投影列的空值拒绝条件，因此可以将子查询中的OUTER JOIN等价地改写为INNER JOIN。

如果用另一种思想来考虑这个问题，因为IN子查询执行时，子查询中返回的空值永远使IN条件为假，也就是不会返回此行数据。子查询的结果集中是否存在空值不会影响IN条件的执行结果，因此我们可以找到另一种实现OUTER转INNER的思路，即不感知空值。

不感知空值

SELECT count(a.area_no)
FROM branches b
LEFT OUTER JOIN area a
ON a.area_no = b.area_no;

在这个例子中，count()聚合函数对于参数中的空值是不关心的，是否有空值参与运算，又或者有多少个空值参与运算，对结果集是没有影响的。所以即使OUTER JOIN会对a.area_no字段补空，补出的空值并不会影响到count(a.area_no)的执行结果，所以在这个查询中，将OUTER转为INNER是可行的。

常见的不感知空值的场景：

count(), sum(), variance(), avg()等聚合函数，不感知参数中的空值
in (subquery), = (subquery)等子查询场景，不感知投影列中的空值

注意，不感知空值也需要结合空值敏感具体分析，例如如果将 count(a.area_no) 改为 count(nvl(a.area_no),’1’) ，参数中的空值敏感表达式会将本来不关心空值的聚合函数变为需要关注a.area_no中空值的表达式了。

不产生空值

前文我们关于OUTER转INNER的讨论中，一直聚焦空值问题。既然补空的前提是补空边没有满足JOIN条件的记录，那么如果能够保证补空边一定存在满足JOIN条件的记录，是不是就不会产生空值了？即INNER JOIN和OUTER JOIN完全等价了。

回到我们最开始的例子

SELECT b.branch_name, a.area_name
FROM branches b
LEFT OUTER JOIN area a
ON a.area_no = b.area_no;

假设我们建表时给予相关字段一些约束条件，使其满足以下两点：

关联字段a.area_no是area表的主键，b.area_no是branches表的外键，关联到area上的area_no字段。
branches表的area_no字段非空。

当这两个条件同时成立时，我们知道主外键关联，当外键非空时，一定可以在主键中找到，JOIN条件一定成立，补空操作永远不会发生。既然不存在补空场景，OUTER转INNER的前提也自然成立。

当然，这个例子也只是满足不产生空值的一种场景，在实际SQL调优中，需要具体问题具体分析，谨慎判断补空条件的成立性。

下面举一个相似的不能转INNER的例子，在满足上述约束的场景下，改写查询如下：

SELECT b.branch_name, a.area_name
FROM branches b
LEFT OUTER JOIN area a
ON a.area_no = b.area_no and a.area_no < '02';

外键一定能在主键上找到的约束条件被on上的a.area_no <'02'条件打破了，因此关联条件不再一定成立，可能出现补空操作。此时不能将OUTER转为INNER。

05 结语

以下对OUTER JOIN与INNER JOIN的主要区别、转化优势、转化方式进行了简单总结：

OUTER JOIN与INNER JOIN注意要区别在于当不满足JOIN条件时，OUTER JOIN会将补空边补空输出。
进行OUTER转INNER有利于SQL执行性能的提升。
判断OUTER转INNER的主要依据是对于空值的处理。
利用一些过滤条件，过滤空值。
分析查询的语义，判断是否是不感知空值的场景，例如聚合函数、特殊的子查询等。
基于一些已知的约束条件能够判断出补空边一定能满足关联条件，不会出现补空场景。

YashanDB所提供SQL查询优化方式还有很多，更多的信息欢迎到崖山官网文档中心（https://doc.yashandb.com/）进行查阅。

posted @ 2025-06-06 10:33 YashanDB 阅读(20) 评论(0) 收藏举报

刷新页面返回顶部

YashanDB