Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: What does this mean: Outer Join (+) on a Value?

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

From: Tony <andrewst_at_onetel.net.uk>
Date: 11 Jan 2004 04:44:47 -0800
Message-ID: <c0e3f26e.0401110444.4254c5f7@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US