Я не могу понять, почему Oracle возвращает эти странные результаты. Я думаю, что код действительно очень ясен.
Я ожидал, что нет условия = (НЕ является нулевым в OUTER JOIN cond) + (нуль в OUTER JOIN cond)
Поскольку я интерпретирую 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