Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> [Fwd: Re: Quick question re outer joins]

[Fwd: Re: Quick question re outer joins]

From: Nuno Souto <>
Date: Sat, 24 Jul 2004 01:22:11 +1000
Message-ID: <>

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 =
exactly the same as:
select (list of columns),
(select from B where = from A;

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

Nuno Souto
in sunny Sydney, Australia
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Jul 23 2004 - 10:18:40 CDT

Original text of this message