[Fwd: Re: Quick question re outer joins]
- From: Nuno Souto <dbvision@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 24 Jul 2004 01:22:11 +1000
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
