Oracle 中完全外连接的奇怪行为 - 如何解释？

我注意到 Oracle 11 中 FULL OUTER JOIN 的奇怪行为。我正在连接 HR 模式中的表，特别是 EMPLOYEES 和 DEPARTMENTS。

例如，以下查询返回 123 行：

    SELECT * FROM employees e
    FULL JOIN departments d ON e.department_id = d.department_id

然而，难以理解的是 - 当我在 select 子句中放入一组特定列时，查询将返回 122 行（缺少的行是针对没有分配部门的员工的 - 该行是通过左连接另外返回的）与内连接相比）：

    SELECT first_name, last_name, department_name FROM employees e
    FULL JOIN departments d on e.department_id = d.department_id

即使当我计算行数时它也会返回 122 (COUNT(*)）！！！到底是怎么回事？有什么区别SELECT * and SELECT COUNT(*)?

解释计划为SELECT * ... :

SELECT STATEMENT                                      122
  VIEW                 VW_FOJ_0                       122
    HASH JOIN                          FULL OUTER     122
      Access Predicates
        E.DEPARTMENT_ID = D.DEPARTMENT_ID
      TABLE ACCESS     DEPARTMENTS     FULL            27
      TABLE ACCESS     EMPLOYEES       FULL           107

and for SELECT COUNT(*) ... :

SELECT STATEMENT                                             1
  SORT                                     AGGREGATE         1
    VIEW               VW_FOJ_0                            122
      HASH JOIN                            FULL OUTER      122
        Access Predicates
          E.DEPARTMENT_ID = D.DEPARTMENT_ID
        INDEX          DEPT_ID_PK          FAST FULL SCAN   27
        INDEX          EMP_DEPARTMENT_IX   FAST FULL SCAN  107

优化器不应选择在第二个查询中使用 EMP.DEPT_ID 上的索引，因为它可能具有 NULL 值。这就是导致它从结果中排除一行的原因。

目前我能想到的唯一非错误解释是，您以某种方式在 DISABLE RELY 模式下创建了约束，以便优化器认为该字段不能包含 NULL。在这种情况下，如果约束中的信息不正确，则使用索引是正确的。然而，似乎 RELY 选项不适用于 NOT NULL 约束，所以我不明白这怎么可能是问题。尽管如此，请仔细查看表格上的所有限制。

除此之外，Oracle 网站上存在数量惊人的关于完全外连接错误结果的错误。你可能会击中其中之一。在相当多的情况下，解决方法是禁用“本机”完全外部联接，您可以使用以下语句对当前会话执行此操作：

alter session set "_optimizer_native_full_outer_join"=off;