Hi Tom,
Could you please clarify why the following simple sql is returning the result?
select
a.dummy,
b.dummy,
decode ( a.dummy, 'X', null, b.dummy )
from
dual a,
dual b
where
decode ( a.dummy, 'X', null, b.dummy (+) ) = a.dummy
;
At first it looks like satisfied null = 'X' condition. Is it true that using (+) after default value in decode is an equivalent of left outer joining on entire decode? Is the following query the equivalent of the former one?
select
a.dummy,
b.dummy,
decode ( a.dummy, 'X', null, b.dummy )
from
dual a left outer join dual b on decode ( a.dummy, 'X', null, b.dummy ) = a.dummy
;
Yes, those two statements are equivalent.
Remember that an outer join always returns all the rows from the outer table. It returns rows from the inner table that satisfy the join condition.
In those queries A is the outer table, so you'll get a row from this table in the output.
You'll then get rows from B where:
decode ( a.dummy, 'X', null, b.dummy (+) )
is true. This maps X to null, so the condition is:
null = dummy
Which is unknown. So you get no rows from B.
This matches the results you get:
select
a.dummy a_dum,
b.dummy b_dum,
decode ( a.dummy, 'X', null, b.dummy )
from
dual a,
dual b
where decode ( a.dummy, 'X', null, b.dummy (+) ) = a.dummy;
A_DUM B_DUM DECODE(A.DUMMY,'X',NULL,B.DUMMY)
X <null> <null>