I always tell new people that an easy way to remember the old-style, Oracle outer-join syntax is that
the (+) sign is on opposite side of where you think it should be.
Left join:
select * from foo, bar
where foo.id = bar.id(+)
Right join:
select * from foo, bar
where foo.id(+) = bar.id
I'm sure I learned this in college, but what is the purpose of having the (+) sign on either side? Does it simply indicate "even those rows on the other side that don't match on this side"? but that would seem too complicated. What was the purpose of choosing "(+)" and putting it on the side where no match was made?
The (+)
identifies the table that is being outer joined to. The way I was taught, the (+)
indicated the table that would have missing rows for which new NULL
rows had to be added.
If you look at the alternate left outer join syntaxes that various databases supported before LEFT OUTER JOIN
became part of the ANSI standard, the proprietary operator was generally applied to the table that was "missing" rows. DB2 also supports the (+)
operator for outer joins in the same way that Oracle does.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With