how to change/simplify joins in oracle

Question

I have a join in a oracle query which looks like:

      FROM eiv.table1
           eiv.table2 b
       WHERE a.state_cd =
             b.state_code(+)

what does the (+) towards the end mean?

With this query I have noticed that sometimes I am getting an empty space when records do not match in tables.

Is this a left outer join or right? How can this be simplified.

Answer 1

It means that it's a left outer join... Details always come from a, and only come from b when the condition is met...

FROM eiv.table1 eiv.table2 b WHERE a.state_cd = b.state_code(+)

=

from evi.table1 a left join eiv.tableb b on (a.state_cd = b.state_code)

You might want to give some thought to using the same column name for the state code on both tables, but it may be a little late for that...

Answer 2

SELECT  *
FROM    eiv.table1 a
LEFT JOIN
        eiv.table2 b
ON      b.state_code = a.state_cs

Before 9i, Oracle did not support ANSI join syntax, and (+) clause was used instead.

Answer 3

http://www.adp-gmbh.ch/ora/sql/outer_join.html

"This might be what one want or it might not. Assuming that we want to return all numbers, even if the german translation is missing, we need an outer join. An outer join uses a (+) on the side of the operator (which in this case happens to be the equality operator) where we want to have nulls returned if no value matches:

select l.v "English", r.v "German" from r,l where l.i
= r.i (+) and r.l(+) = 'de';

And this returns a row for each english word, even if there is no german translation:

English German -------------------- -------------------- one two zwei three drei four five"