Re: outer join question

From: matt lewis <matt_at_lewis.co.uk>
Date: Fri, 17 Aug 2007 18:04:06 +0100
Message-ID: <fa4kfn$rud$1@registered.motzarella.org>

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! ;-) Received on Fri Aug 17 2007 - 12:04:06 CDT