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

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0401101637.2920a086_at_posting.google.com>...
> "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 --

The 2 conditions:
1) invoice.begin_date (+) <= trunc(sysdate) 2) "(invoice.begin_date IS NULL OR invoice.begin_date <= trunc(sysdate)" are NOT equivalent. Specifically, the 2nd query is no longer an outer join.

Here is a simple example:

SQL> create table inner( id int);

Table created.

SQL> create table outer ( id int, dt date);

Table created.

SQL> insert into inner values (1);

1 row created.

SQL> insert into outer values (1, trunc(sysdate+1));

1 row created.

SQL> select * from inner, outer
  2 where outer.id (+) = inner.id
  3 and outer.dt (+) <= trunc(sysdate);

        ID ID DT
---------- ---------- ---------

         1

SQL> select * from inner, outer
  2 where outer.id (+) = inner.id
  3* and (outer.dt is null or outer.dt <= trunc(sysdate) );

no rows selected Received on Sun Jan 11 2004 - 06:39:57 CST