开发者

how to change/simplify joins in oracle

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

      FROM eiv.tabl开发者_如何学编程e1
           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.


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.


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...


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"

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜