【SQL 优化器技术系列】 连接方法（Join Method）

Oracle 2005 年出了一个 30 多页的小册子，《Query Optimization in Oracle Database10g Release 2》，介绍了常见的优化器技术。

我是做 SQL 执行的，优化部分只了解皮毛，从没有系统学习过。本系列逐个学习和介绍，自我提升，也帮助他人。

SEMI JOIN

通常出现在使用了EXISTS 或 IN 的SQL中，所谓 SEMI JOIN 即在两表关联时，当第二个表中存在一个或多个匹配记录时，返回第一个表的记录；

与普通 JOIN 的区别在于选择做 SEMI JOIN时，第一个表里的记录最多只返回一次。

ANTI JOIN

对于SQL 中常见的过滤条件 NOT IN、<> ALL、NOT EXISTS 操作相关子查询， 优化器往往会选择将其改写为 ANTI JOIN 以提升执行效率。

ANTI JOIN 与 SEMI JOIN 相反，当在第二张表没有发现匹配记录时，才会返回第一张表里的记录。

当使用 NOT EXISTS、NOT IN 的时候会用到 ANTI JOIN ，两者在处理NULL 值的时候会有所区别。NOT EXISTS 是 NULL 不敏感的，也就是说，NULL 和 FALSE 在语义上面等价；NOT IN、<> ALL 是 NULL 敏感 的，NULL 和 FALSE 不等价。这导致 NOT IN、<> ALL 没有办法改写为普通的 MERGE ANTI JOIN 或 HASH ANTI JOIN，只能使用 FILTER 的方式进行逐行迭代，很大程度上限制了执行效率。 在 Oracle 11g 中，引入了NULL AWARE ANTI JOIN 算法，改善了执行效率。

为什么 NOT IN 是 NULL 敏感的？因为 NULL 表示不确定。下面的表达式，不能认为是 TRUE 或 FALSE：
10 NOT IN (1, 3, NULL) = NULL，即可能是 TRUE，可能是 FALSE
10 NOT IN (10, NULL) = FALSE，因为无论 NULL 是什么值，10 一定在这个集合里

INNER JOIN

比较简单，就是输出两边匹配的行。如果有 NULL 则都不输出。

OUTER JOIN

分为 LEFT JOIN 和 RIGHT JOIN。以一边为参考进行输出。如果有不匹配，LEFT JOIN 只输出左行，右边填 NULL，RIGHT JOIN 输出右边行，左边填 NULL 输出。