CODEWITHSUNDEEP
sqloracle11ginner-joinouter-join
Victor
Victor

Reputation: 17107

Oracle outer join not giving expected results (behaving like inner join)

This query:

select *
from table_a
, table_b
where table_a.id = table_b.id(+)
   and table_b.name = 'BENEFICIARY'

returns me no records. table_b has no records with name='BENEFICIARY'. But the outer join should return all records from table_a regardless . No? The below query returns records from table_a as expected:

select *
from table_a
, (select *
   from table_b
   where table_b.name = 'BENEFICIARY') AS table_b1
where table_a.id = table_b1.id(+)

Why is the first query not returning records?

Upvotes: 1

Views: 972

Answers (2)

Justin Cave
Justin Cave

Reputation: 231851

The old-style syntax query

select *
 from table_a
    , table_b
where table_a.id = table_b.id(+)
  and table_b.name = 'BENEFICIARY'

is identical to the ANSI query

select *
 from table_a
      left outer join table_b on (table_a.id = table_b.id)
where table_b.name = 'BENEFICIARY'

In both cases, the predicate table_b.name = 'BENEFICIARY' is applied after the join, not as part of the join, which defeats the purpose of doing an outer join. In order to ensure that the predicate is part of the join operation, using the old syntax, you would need

select *
 from table_a
    , table_b
where table_a.id = table_b.id(+)
  and table_b.name(+) = 'BENEFICIARY'

while with ANSI syntax, you would want

select *
 from table_a
      left outer join table_b on (    table_a.id = table_b.id 
                                  and table_b.name = 'BENEFICIARY')

In general, if you're using the old syntax, any predicate that you want to be part of the join condition rather than a predicate that is applied outside of the join, would need to have the (+) operator applied to it. Similarly, a predicate that you want to be part of the join condition when you are using ANSI syntax would need to be part of the ON clause.

In this case, the ANSI syntax is likely to be more self-explanatory in addition to being more portable so it would generally be preferred.

Upvotes: 4

podiluska
podiluska

Reputation: 51514

In the first query, the filter is being applied to the results, after the join.

Try

select * 
from table_a
     left join table_b 
     on table_a.id=table_b.id
     and table_b.name='BENEFICIARY'

Upvotes: 1

Related Questions