| outer join [message #21255] |
Fri, 19 July 2002 14:55  |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
Hello All,
Is this outer join is correct.
I expect to see all CODES from table A with DESC from B where there are corresponding CODES and also null DESC for all CODES which don't have corresponding CODES in B
select A.CODE, B.DESC
from TABLE-A A, TABLE-B B
where A.OWNER = 'abcd'
and B.OWNER = 'abcd'
and A.CODE = B.CODE(+)
The problem is the above query does not return that rows from table A which don't have corresponding rows in table B. (no outer joining?) even though the OWNER in both tables are 'abcd'.
If i outer join even the owner then I get the correct rows back
select A.CODE, B.DESC
from TABLE-A A, TABLE-B B
where A.OWNER = 'abcd'
and A.OWNER = B.OWNER(+)
and A.CODE = B.CODE(+)
Oracle 8.1.6 on WinNT
Any clarification is highly appreciated.
Suresh
|
|
|
|
| Re: outer join [message #21257 is a reply to message #21255] |
Fri, 19 July 2002 15:39  |
ctg
Messages: 146
Registered: July 2002
|
Senior Member |
|
|
the first query does not return all codes from table A because you are only returning the codes where owner = 'abcd'. the "a.owner='abcd'" is sometime refered to as a local condition, and that limits the rows from table a.
The second query returns what you wanted, because you joined on both common columns and you are not limiting the rows to one owner.
|
|
|
|
| Re: outer join [message #21259 is a reply to message #21255] |
Fri, 19 July 2002 16:58 |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
Thank you for your reply.
But those records from table A whose owner is 'abcd' but does not have a corresponding CODEs in table B are also not returned.??
Suresh
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
