SQL基于列值过滤整组数据时如何避免低效子查询提升性能

现有一张存储采购订单数据的表，包含3个字段：

• Order_ID：订单ID
• LINE：订单行号
• CODE：行编码

样例数据如下：

需求为过滤掉所有至少存在一行CODE值为'xxxx'的订单，仅保留无该编码的订单全量行数据，预期返回A0001、A0003的全部记录。
最初编写的SQL存在两个明显问题：一是语法不严谨，NOT IN子查询使用SELECT *会返回多列，不符合单列匹配的语法要求；二是大数据量下NOT IN写法会全量物化子查询结果，IO和计算开销极高，导致SQL运行耗时过长。

以下两种写法都可以避免大子查询的全量扫描开销，数据库优化器可以基于关联逻辑生成更高效的执行计划，配合索引能实现极高的查询效率。

方案1：使用NOT EXISTS反连接（优先推荐） #

这是绝大多数关系型数据库对反过滤场景优化最好的写法，执行时不会全量拉取子查询的所有结果，只要匹配到符合条件的记录就会终止当前订单的匹配判断，额外开销极低。

SELECT t1.*
FROM MyTable t1
WHERE NOT EXISTS (
    SELECT 1
    FROM MyTable t2
    WHERE t2.Order_ID = t1.Order_ID
      AND t2.CODE = 'xxxx'
)

方案2：使用LEFT JOIN加空值判断 #

如果使用的是较老版本的数据库，对NOT EXISTS的优化支持不佳，可以用左连接的写法，性能表现同样稳定：

SELECT t1.*
FROM MyTable t1
LEFT JOIN MyTable t2
  ON t1.Order_ID = t2.Order_ID
  AND t2.CODE = 'xxxx'
WHERE t2.Order_ID IS NULL

实现逻辑是通过左连接关联同订单下CODE为xxxx的记录，关联结果为空的订单，就是完全不存在xxxx编码的目标订单。

• 为表创建(Order_ID, CODE)或者(CODE, Order_ID)的联合索引，上述两个SQL都可以直接通过索引完成过滤判断，不需要回表扫描全量数据，千万级数据量下也能快速返回结果。
• 避免使用NOT IN (SELECT 子查询)的写法处理大数据量场景：这类写法多数情况下会把子查询的结果全量物化成临时表再做逐行匹配，内存、磁盘IO开销都很高，一旦子查询返回结果中存在NULL值，还会导致整个NOT IN的判断逻辑失效，返回不符合预期的结果。

内容的提问来源于stack exchange，提问作者Scot j.