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)

Erik, I would not be surprised if you are correct and the original developer found a clever way to code "(invoice.begin_date IS NULL OR invoice.begin_date <= trunc(sysdate)", but I do not think it is an obvious. I can see a future developer glancing at the code and thinking those extra (+) are not needed and removing them from the code without realizing the effect related to NULL.

IMHO -- Mark D Powell -- Received on Sat Jan 10 2004 - 18:37:29 CST