| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: What does this mean: Outer Join (+) on a Value?
andrewst_at_onetel.net.uk (Tony) wrote in message news:<c0e3f26e.0401110439.335a429a_at_posting.google.com>...
> 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
Tony, what second query? You are correct in that "(outer.dt is null or outer.dt <= trunc(sysdate)" are not exactly equilivent. However the presence of NULL in the start_date or end_date column has an interesting affect on the result set values.
Because the query returns all rows of B and selects only columns from B the date checks are unnecessary. However if columns from A are being returned in the select list the presence of a NULL would affect the value of the other columns in A returned to B.
SQL> desc t1
Name Null? Type ----------------------------------------- -------- ---------------------------- INVOICE NUMBER START_DATE DATE END_DATE DATE OTHER_DATE VARCHAR2(10)
SQL> select * from t1;
INVOICE START_DAT END_DATE OTHER_DATE
---------- --------- --------- ----------
1 03-JAN-04 one
2 08-JAN-04 one
3 08-JAN-04 12-FEB-04 one
4 08-JAN-04 12-FEB-04 one
5 08-JAN-04 12-FEB-04 one
SQL> desc t2
Name Null? Type
----------------------------------------- --------
----------------------------
INVOICE NUMBER
OTHER_DATE VARCHAR2(10)
SQL> select * from t2;
INVOICE OTHER_DATE
---------- ----------
1 one
2 two
3 three
4 four
6 six
INVOICE START_DAT END_DATE OTHER_DATE INVOICE OTHER_DATE ---------- --------- --------- ---------- ---------- ----------
1 one
2 two
3 08-JAN-04 12-FEB-04 one 3 three
4 08-JAN-04 12-FEB-04 one 4 four
6 six
SQL> select a.*, b.*
2 from t1 a, t2 b
3 where a.invoice(+) = b.invoice
4 /
INVOICE START_DAT END_DATE OTHER_DATE INVOICE OTHER_DATE
---------- --------- --------- ---------- ---------- ----------
1 03-JAN-04 one 1 one
2 08-JAN-04 one 2 two
3 08-JAN-04 12-FEB-04 one 3 three
4 08-JAN-04 12-FEB-04 one 4 four
6 six
SQL> select a.*, b.*
2 from t1 a, t2 b
3 where a.invoice(+) = b.invoice 4 and a.start_date <= trunc(sysdate) 5 and a.end_date >= trunc(sysdate)6 /
INVOICE START_DAT END_DATE OTHER_DATE INVOICE OTHER_DATE
---------- --------- --------- ---------- ---------- ----------
3 08-JAN-04 12-FEB-04 one 3 three
4 08-JAN-04 12-FEB-04 one 4 four
In summary, it is my opinion that the two lines involing start_date and end_date should remain with the outer join condition if we want to return data from A when and only when the invoice data falls within a certain date range. Note that the specific rows retuned from B is unaffected. If no data from A is going to be returned in the query select list then the two lines should be removed from the query altogether since they have no practical value. You cannot just remove the (+) without affecting the result set.
IMHO -- Mark D Powell -- Received on Tue Jan 13 2004 - 10:31:33 CST
 |
 |