Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Piotr.

Asked: September 21, 2020 - 4:38 pm UTC

Last updated: September 22, 2020 - 10:43 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

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
;

and Chris said...

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>    


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.