Re: outer join question

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Fri, 17 Aug 2007 13:36:44 -0500
Message-ID: <46c5deb0$0$32552$88260bb3@free.teranews.com>

matt lewis wrote:
> ciapecki wrote:
>

>> On 17 Aug., 17:32, ciapecki <ciape..._at_gmail.com> wrote:
>>
>>> On 17 Aug., 16:53, matt lewis <m..._at_lewis.co.uk> wrote:
>>>
>>>
>>>> Hi, I'm studying a book which is full of errors and the errata seems
>>>> sketchy.
>>>
>>>> Is this statement correct?
>>>
>>>> "In outer join statements on tables A and B, you specify the left outer
>>>> join when you want all of table B's rows, even when no corresponding
>>>> record exists in table A"
>>>
>>>> I had this down as correct but the answer in the book has it as wrong.
>>>
>>>> I'm pulling my hair out here!
>>>
>>>> Is it right or wrong please?
>>>
>>> Hi there,
>>> it should be other way, look at the example:
>>>
>>> create table tableA (id number);
>>> create table tableB (id number);
>>> insert into tableA values(1);
>>> insert into tableA values(2);
>>> insert into tableB values(2);
>>> insert into tableB values(3);
>>> commit;
>>>
>>> -- we have in tableA [1,2] in tableB [2,3]
>>> -- when you run
>>> select a.id a_id, b.id b_id
>>> from tableA a left outer join tableB b
>>> on a.id  = b.id
>>>
>>> -- you get:
>>> Query finished, retrieving results...
>>>                 A_ID
>>> B_ID
>>> --------------------------------------
>>> --------------------------------------
>>>
>>> 2                                      2
>>>
>>> 1
>>> 2 row(s) retrieved
>>>
>>> you received all rows from A
>>>
>>> chris
>>
>>
>> the output again (since it was messed up in the message):
>> Query finished, retrieving results...
>>  A_ID, B_ID
>> -------- --------
>> 2         2
>> 1
>>
>> 2 row(s) retrieved
>>
>> chris
>>

>
> I'm confused now, because when I run this:
>
> select a.id a_id, b.id b_id
> from tableA a, tableB b
> where a.id(+) = b.id
>
> returns
>
> SQL> /
>
> A_ID B_ID
> ---------- ----------
> 2 2
> 3
>
> Which I expected.
>
> Which makes the authors question {A left outer join on A and B returns
> all B's rows, correct). And alls well in my head at this point.
>
> And your statement 'from tableA a left outer join tableB b' would be the
> other way around, from tableB b left outer join tableA a? Yet when
> written as a question could be interpreted as either.
>
> Arrrrrrrrrgh! ;-)

In your example above, you have a right outer join. If you want this to be a left outer join, use the following:

  where a.id = b.id (+)

Notice the outer join operator. This is basically indicating which table will not necessarily have matching rows and Oracle will have to supply more information...in the form of NULL values.

IMO, this is where the LEFT|RIGHT|FULL OUTER JOIN ANSI syntax is easier to read than Oracle's old-style (+) operator.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com

Received on Fri Aug 17 2007 - 13:36:44 CDT