Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Piotr.

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

Answered by: Chris Saxon - Last updated: September 22, 2020 - 10:43 am UTC

Category: SQL - Version: 12.1.0.2.0

Viewed 100+ 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 we 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>    

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.