-
right outer joins
I have a simple query that uses a right outer join, ive used the RIGHT OUTER JOIN method and also =(+) method. Both queries should be identical, but they return different numbers of rows. Am I missing something really obvious here
query 1
select bc.id,bct.id AS "role_id",
bct.description AS "role_name",
bc.contributor_name AS "contributor"
FROM billings_contributions bc RIGHT OUTER JOIN billings_contrib_types bct
ON bct.id = bc.contribution_type_id
WHERE bct.common_flag = 'Y'
ORDER BY bct.app_seq desc
query 2
select bc.id,
bct.id AS "role_id",
bct.description AS "role_name",
bc.contributor_name AS "contributor"
from billings_contributions bc,
billings_contrib_types bct
where bct.id = bc.contribution_type_id(+)
and bct.common_flag = 'Y'
order by bct.app_seq desc
-
In your ANSI syntax for outer join you shoudnt be using the where clause. The queries will show you the difference.
The first one will not return the outer join results and second one
will return what you expect.
PHP Code:
SELECT *
FROM EMP e RIGHT OUTER JOIN DEPT d
ON e.deptno = d.deptno
WHERE e.sal > 1600
SELECT *
FROM EMP e RIGHT OUTER JOIN DEPT d
ON e.deptno = d.deptno AND e.sal > 1600
HTH
-dharma
-
Sorry misuk11, I can't see anything.
dharma, you're right about the placing of conditions, but in this case a better parallel is:
Code:
SELECT *
FROM EMP e RIGHT OUTER JOIN DEPT d
ON e.deptno = d.deptno
WHERE d.dname IN ('OPERATIONS','RESEARCH')
gives the same as:
SELECT *
FROM EMP e, DEPT d
WHERE e.deptno(+) = d.deptno
AND d.dname IN ('OPERATIONS','RESEARCH')
but not:
SELECT *
FROM EMP e RIGHT OUTER JOIN DEPT d
ON e.deptno = d.deptno AND d.dname IN ('OPERATIONS','RESEARCH')
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Yep, didnt notice that.
Thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|