结果集和别的数据存在与否有关

Result Set Predicated on Absence of Data

一个表中的哪些记录和另一个表中的数据不匹配？这种“识别例外”的需求经常出现。人们最常想到的解决方案有两个：not in ()搭配非关联子查询，或者not exists()

搭配关联子查询。一般认为应该使用 not exists。在子查询出现在高效搜索条件之后，使用not exists是对的，因为高效过滤条件已清除大量无关数据，关联子查询当然会很高效。但当子查询恰好是唯一条件时，使用not in比较好。

查找在另一个表无对应数据的记录时，会碰到一些奇特的解决方案。以下为实际例子，显示哪些数据库查询代价最高。注意，问号是占位符（placeholder）或称为绑定变量（bind variable），它们的具体值在后续执行中传递给查询：

    insert into ttmpout(custcode,

                            suistrcod,

                            cempdtcod,

                            bkgareacod,

                            mgtareacod,

                            risktyp,

                            riskflg,

                            usr,

                            seq,

                            country,

                            rating,

                            sigsecsui)

    select distinct custcode,

                       ?,

                       ?,

                       ?,

                       mgtareacod,

                       ?,

                       ?,

                       usr,

                       seq,

                       country,

                       rating,

                       sigsecsui

    from ttmpout a

    where a.seq = ?

      and 0 = (select count(*)

                 from ttmpout b

                 where b.suistrcod = ?

                 and b.cempdtcod = ?

                 and b.bkgareacod = ?

                 and b.risktyp = ?

                 and b.riskflg = ?

                 and b.seq = ?)

此例并非暗示我们无条件地认可临时表的使用。另外，我怀疑这个insert语句会被循环执行，通过消除循环可以适当改善性能。

例子中出现了自参照（self-reference）很不常见的用法：对一个表的插入操作，是以同一个表上的 select 为基础的。当前存在哪些记录？要创建的记录是否不存在？要插入的记录是由上述两个问题决定的。

使用 count(*) 测试某些数据是否存在是个糟糕的主意：为此DBMS 必须搜索并找出所有相符的记录。其实，此时应该使用 exists，它会在遇到第一个相符数据时就停止。当然，如果过滤条件是主键，使用count或exists的差别不大，否则差异极大——无论如何，从语义角度讲，若想表达：

    and not exists (select 1 ...)

不能换成：

    and 0 = (select count(*) ...)

使用count(*)时，优化器“可能”会进行合理的优化——但未必一定如此。记录的数量若通过独立步骤被计入某个变量，优化器肯定不会优化，因为优化器再聪明也无法猜测计数的用途：count()的结果可能是极重要的值，而且必须显示给最终用户！

然而，当我们只想建立一条新记录，且新记录要从已存在于表中的记录推导出来时，正确的做法是使用诸如except（有时称为 minus）这样的集合操作符（set operator）。

    insert into ttmpout(custcode,

                            suistrcod,

                            cempdtcod,

                            bkgareacod,

                            mgtareacod,

                            risktyp,

                            riskflg,

                            usr,

                            seq,

                            country,

                            rating,

                            sigsecsui)

    (select custcode,

              ?,

              ?,

              ?,

              mgtareacod,

              ?,

              ?,

              usr,

              seq,

            country,

            rating,

            sigsecsui

     from ttmpout

     where seq = ?

     except

     select custcode,

            ?,

            ?,

            ?,

            mgtareacod,

            ?,

            ?,

            usr,

            seq,

            country,

            rating,

            sigsecsui

     from ttmpout

     where suistrcod = ?

       and cempdtcod = ?

       and bkgareacod = ?

       and risktyp = ?

       and riskflg = ?

       and seq = ?)

集合操作符的重大优点是彻底打破了“子查询强加的时间限制”，无论子查询是关联子查询还是非关联子查询。打破“时间限制”是什么意思？当存在关联子查询时，就必须执行外层查询，接着对所有通过过滤条件的记录，执行内层查询。外层查询和内层查询相互依赖，因为外层查询会把数据传递给内层查询。

使用非关联子查询时情况要好得多，但也不是完全乐观：必须先完成内层查询之后，外层查询才能介入。即使优化器选择把整个查询作为哈希连接（hash join）执行——这是聪明的方法——也不例外，因为要进行哈希连接，SQL 引擎必须先进行表扫描以建立哈希数组（hash array）。

相比之下，使用集合操作符union、intersect或except时，查询中的这些组成部分不会彼此依赖，从而不同部分的查询可以并行执行。当然，如果有个步骤非常慢，而其他步骤非常快，则并行化意义不大；另外，如果查询的两个部分工作完全相同，并行化就没有好处，因为不同进程的工作是重复的，而不是分工负责。一般而言，在最后步骤之前，让所有部分并行执行会很高效，最后步骤把不完整的结果集组合起来——这就是分而治之。

集合操作符的使用有个额外的问题：各部分查询必须返回兼容的字段 —— 字段的类型和数量都要相同。下例（实际案例，来自账单程序）通常不适合集合操作符：

    select whatever, sum(d.tax)

    from invoice_detail d,

          invoice_extractor e

    where (e.pga_status = 0

             or e.rd_status = 0)

     and suitable_join_condition

     and (d.type_code in (3, 7, 2)

           or (d.type_code = 4

              and d.subtype_code not in

                   (select trans_code

                    from trans_description

                    where trans_category in (6, 7))))

    group by what_is_required

    having sum(d.tax) != 0

最后一个条件有问题（它使我想起了《绿野仙踪》里的黄砖路，甚至使我做起了“负税率”的白日梦）：

    sum(d.tax) != 0

如前所述，换成下列条件更加合理：

    and d.tax > 0

上述的例子中，使用集合操作符会相当笨拙，因为必须访问invoice_detail表好几次——如你所料，那不是个轻量级的表。当然，还要看每个条件的可选择性，如果 type_ code=4很少见，那么它就是个可选择性很高的条件，exists或许会比not in ()更适合。另外，如果trans_description正好是个小型表（或者相对较小），尝试通过单独操作测试存在性，并起不到改善性能的效果。

另一个表达非存在性的方法很有趣——而且通常相当高效——是使用外连接（outer join）。外连接的主要目的是，返回来自一个表的所有信息及连接表中的对应信息。无对应信息的记录也需返回——查找另一个表中无对应信息的数据时，这些记录正好是我们的兴趣所在，可通过检查连接表的字段值是否为null找出它们。

例如：

    select whatever

    from invoice_detail

    where type_code = 4

      and subtype_code not in

                    (select trans_code

                     from trans_description

                     where trans_category in (6, 7))

或重写为：

    select whatever

    from invoice_detail

         outer join trans_description

                 on trans_description.trans_category in (6, 7)

                and trans_description.trans_code = invoice_detail.subtype_code

    where trans_description.trans_code is null

我故意在join子句中加上trans_category的条件。有人认为它应该出现在where 子句中，实际上，在连接之前或在连接之后过滤都不影响结果（当然，根据这个条件和连接条件本身的可选择性不同，会有不同的性能表现）。然而，在使用空值上的条件时，我们别无选择，只有在连接后才能做检查。

外连接有时需要加 distinct。实际上，通过外连接或not in()非关联子查询，来检查数据是否存在的差异很小，因为连接所使用的字段，正好与比较子查询结果集的字段完全相同。不过，众所周知的是，SQL 语言的“查询表达式风格”对“执行模式”影响很大，尽管理论上不是这么说的。这取决于优化器的复杂程度，以及它是否会以类似方法处理这两类查询。换言之，SQL 不是真正的声明性语言（SQL is not a truly declarative language），尽管优化器不断推陈出新改善SQL的可靠性（reliability）。

最后提醒一下，应密切注意null，这个舞会扫兴者（party-poopers）经常出现。虽然在in ()子查询中，null与大量非空值连接不会对外层查询造成影响，但在使用not in ()子查询时，由内层查询返回的null会造成not in()条件不成立。要确保子查询不会返回null并不需要太高的代价，而且这么做可以避免许多灾难。

总结：数据集可以通过各种技巧进行比较，但一般而言，使用外连接和子集合操作符更高效。

---