Brian Peasland wrote:
> 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
>
>
Ok Brian, I'm fine with the (+) operator and understand it's placement
but I don't understand the ANSI equivalent 'right' or 'left' keywords.
The book is very ambiguous. If I see the operator (+) next to the
table.column what is the way or rule to decide whether it's right or left?
Many thanks everyone.
Received on Sat Aug 18 2007 - 07:07:54 CDT
