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"
精彩评论