Spam_at_DefinitiveSolutions.com (Larry Leonard) wrote in message news:<27270a53.0401091011.1399932e_at_posting.google.com>...
> 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...)
Regarding what it MEANS to do this - well, exactly the same as it
means when used elsewhere: when joining to invoice, consider only
records where invoice.begin_date <= trunc(sysdate) and
invoice.end_date >= trunc(sysdate). If no such record exists, return
NULLS.
Received on Sun Jan 11 2004 - 06:44:47 CST
