Re: What does this mean: Outer Join (+) on a Value?

"Erik" <no_at_spam.com> wrote in message news:btn2j8$hu5$1_at_news.lth.se...
| > I thought the (+) syntax was only used to outer-join two tables, like
| > this:
| >
| > WHERE invoice.customer_num (+) = customer.customer_num
| >
| > Today I came across some code that looks like this:
| >
| > WHERE invoice.customer_num (+) = customer.customer_num
| > AND invoice.begin_date (+) <= trunc(sysdate)
| > AND invoice.end_date (+) >= trunc(sysdate)
| >
| > Aren't the second and third occurances of (+) unnecessary (or even
| > meaningless)? I can find no mention of this usage anywhere. Is this
| > an idiom left-over from earlier versions of Oracle (I'm at 9i)? Or
| > maybe from another database vendor's SQL implementation?
| >
| > (Interestingly, although the rows returned are the same with or
| > without the extra (+)'s, the explain plans are different - having the
| > extra (+)'s makes the query much faster... maybe that's why they did
| > it...)
|
| I don't know if it's a totally accurate response, but I think of it as an
| easier way to write
| (invoice.begin_date IS NULL OR invoice.begin_date <= trunc(sysdate)
|
|

this means:

show me the customers
and show me the customer's invoices, if any (i.e., outer join) but only show me invoices (if any) that have a begin date of midnight (am) today or earlier,
and have an end date of midnight (am) today or later

the outer join operators are necessary on each column of the outer join table referenced in the where clause

it does look like Erik's syntax is an alternative -- but would be needed for both the begin_date and the end_date comparison

• mcs