[Fwd: Re: Quick question re outer joins]

Powell, Mark D apparently said,on my timestamp of 24/07/2004 12:32 AM:

> Looking back at the thread I think several of the posts are actually in
> agreement but what is/was meant by "extra rows" is actually the problem.

Bingo. Thanks for explaining it better than I could.

> One of, if not the first post on the thread, asked if I read it correctly if
> the row set returned by the outer join should match the count returned in A.
> The answer is not always as the join to B could cause additional rows to
> appear in the result set when there are multiple rows present in B for the
> join condition.

That would normally not be the case if B is a reference/lookup table. It would presumably not contain multiples of the join condition. In the example I gave later on, JOBS would not have duplicate job rows. If it does, then yes: more rows.

> An outer join will give you back the inner join plus those
> rows in A that do not have a maching row in B where A is the table where we
> always want to return a row from in the result set.

Which one is this one? Left or Right? Can never figure this out...

While I'm here:
Assuming no duplicates on join condition, isn't an outer join between A and B
where a.id = b.id(+)
exactly the same as:
select (list of columns),
(select b.name from B where b.id = a.id) B.name from A;

Ie, using a subquery in the column list we obtain precisely the same result?

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_optusnet.com.au
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------