right outer joins

[misuk11]

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

[dharma]

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.



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

[DaPi]

Sorry misuk11, I can't see anything.


dharma, you're right about the placing of conditions, but in this case a better parallel is: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')

[dharma]

Yep, didnt notice that.
Thanks