[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@xxxxxxxxxxxxxxx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: