我无法理解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
1 回答
EXISTS
/NOT EXISTS
等效查询是通过将NULL
条件放在WHERE
子句中而不是在OUTER JOIN
子句中获得的 . 顺便说一下,这是我们从您的结果中观察到的:主表中的2行在连接表中没有对应的id .
当您将条件放在
OUTER JOIN
子句中时,您告诉Oracle将OUTER JOIN
您的主表从连接表中 subset of rows .由于
c.flh_stato
永远不为null,因此条件是多余的,我们得到与无条件查询相同的结果:使用join子句中的条件
c.flh_stato IS NULL
,我们将主表连接到空结果集,因此我们为主表的每一行获取一行(我们推断主表有6247行具有此条件):