Oracle 左外连接在 JOIN vs WHERE 条件中为空(示例)

Question

我无法理解 Oracle 返回这个奇怪结果的原因。
我觉得代码真的很清楚。

我期待
无条件 =(在 OUTER JOIN cond 中不为 null)+(在 OUTER JOIN cond 中为 null)

因为我将外部连接右表列中的 IS NULL/IS NOT NULL 解释为 EXISTS/NOT EXISTS 条件。

为什么我错了？

DESCRIPTION                          COUNT(1)
---------------------------------- ----------
No condition                             6403
is NOT null in OUTER JOIN cond           6403
is not null in where cond                6401
is null in OUTER JOIN cond               6247
is null in where cond                       2
proof flh_id_messaggio is not null          0
proof flh_stato is not null                 0


  SELECT 'is null in OUTER JOIN cond ' description, count(1)
    FROM    netatemp.TMP_BACKLOG_NOBILLING2013 t
         LEFT OUTER JOIN
            eni_flussi_hub c ON
            c.flh_id_messaggio = t.flh_id_messaggio  
            AND c.flh_stato is null   
   WHERE 1 = 1 
       And t.flh_stato = 'PA'
         AND t.OWNER = 'ETL' 
UNION
  SELECT 'is NOT null in OUTER JOIN cond ' description, count(1)
    FROM    netatemp.TMP_BACKLOG_NOBILLING2013 t
         LEFT OUTER JOIN
            eni_flussi_hub c ON
            c.flh_id_messaggio = t.flh_id_messaggio  
            AND c.flh_stato is not null   
   WHERE 1 = 1 
       And t.flh_stato = 'PA'
         AND t.OWNER = 'ETL'
UNION
  SELECT 'is null in where cond ' description, count(1)
    FROM    netatemp.TMP_BACKLOG_NOBILLING2013 t
         LEFT OUTER JOIN
            eni_flussi_hub c ON
            c.flh_id_messaggio = t.flh_id_messaggio                
   WHERE 1 = 1 
       And t.flh_stato = 'PA'
         AND t.OWNER = 'ETL'
         AND c.flh_stato is null 
UNION
  SELECT 'is not null in where cond ' description, count(1)
    FROM    netatemp.TMP_BACKLOG_NOBILLING2013 t
         LEFT OUTER JOIN
            eni_flussi_hub c ON
            c.flh_id_messaggio = t.flh_id_messaggio                
   WHERE 1 = 1 
       And t.flh_stato = 'PA'
         AND t.OWNER = 'ETL'
         AND c.flh_stato is not null 
UNION
  SELECT 'No condition' description, count(1)
    FROM    netatemp.TMP_BACKLOG_NOBILLING2013 t
         LEFT OUTER JOIN
            eni_flussi_hub c ON
            c.flh_id_messaggio = t.flh_id_messaggio                
   WHERE 1 = 1 
       And t.flh_stato = 'PA'
         AND t.OWNER = 'ETL'
UNION select 'proof flh_stato is not null' description, count(1)
from eni_flussi_hub
where flh_stato is null         
UNION select 'proof flh_id_messaggio is not null' description, count(1)
from eni_flussi_hub
where flh_id_messaggio is null  

Answer 1

EXISTS/NOT EXISTS通过放置 NULL 获得等效查询条件在 WHERE条款，不在 OUTER JOIN 中条款。顺便说一下，这是我们从您的结果中观察到的:

No condition                             6403
is not null in where cond                6401
is null in where cond                       2

主表中的 2 行在连接表中没有相应的 ID。

当您将条件放在 OUTER JOIN 中时条款，你告诉 Oracle OUTER JOIN你的主表来了 行子集 从连接表。

自 c.flh_stato永远不会为空，条件是多余的，我们得到与无条件查询相同的结果:

No condition                             6403
is NOT null in OUTER JOIN cond           6403

有条件 c.flh_stato IS NULL在join子句中，我们将主表连接到一个空的结果集，这样我们就得到了主表每一行的一行(我们在这种情况下扣除了主表有6247行):

is null in OUTER JOIN cond               6247