Oracle proprietary joins- joining on multiple conditions
I've the follwing 2 versions of ANSI compliant SQL(column/table names changed to protect confidential data), of which one of them satisfies my requirement by following the right logic while the other doesn't.
1)ANSI Join 1-Works
SELECT b.COLUMN_A,
COUNT(a.COLUMN_A)
FROM TABLE1 a
RIGHT OUTER JOIN
(SELECT COLUMN_A FROM TABLE2 WHERE COLUMN_X='TEST') b
ON a.COLUMN_A = b.COLUMN_A
AND a.COLUMN_B in (SELECT FROM TABLE3 WHERE COLUMN_Y=5) --WORKS
GROUP BY b.COLUMN_A
1) gives output like this:
COLUMN_A COUNT(COLUMN_A)
--------------------------
A 0
B 0
C 1
D 1
E 0
2)ANSI Join 2-Doesn't work
SELECT b.COLUMN_A,
COUNT(a.COLUMN_A)
FROM TABLE1 a
RIGHT OUTER JOIN
(SELECT COLUMN_A FROM开发者_StackOverflow TABLE2 WHERE COLUMN_X='TEST') b
ON a.COLUMN_A = b.COLUMN_A
WHERE
a.COLUMN_B in (SELECT FROM TABLE3 WHERE COLUMN_Y=5) --DOESN'T WORK
GROUP BY b.COLUMN_A
3)Oracle's proprietary join-Doesn't work
SELECT b.COLUMN_A,
COUNT(a.COLUMN_A)
FROM TABLE1 a,(SELECT COLUMN_A FROM TABLE2 WHERE COLUMN_X='TEST') b
WHERE
a.COLUMN_A(+) = b.COLUMN_A
AND a.COLUMN_B in (SELECT FROM TABLE3 WHERE COLUMN_Y=5) --DOESN'T WORK
GROUP BY b.COLUMN_A
2) & 3) gives output like this:
COLUMN_A COUNT(COLUMN_A)
--------------------------
C 1
D 1
I understand (2,ANSI) & (3,PROPRIETARY) are equivalent. But Is there any equivalent proprietary SQL for (1,ANSI)?. Any help would be most welcome. Thanks. Edit: I've updated the question with the sample output.
You probably want to change this condition:
AND a.COLUMN_B in (SELECT FROM TABLE3 WHERE COLUMN_Y=5)
to either this:
AND a.COLUMN_B (+) in (SELECT FROM TABLE3 WHERE COLUMN_Y=5)
or this:
AND (a.COLUMN_B IS NULL OR a.COLUMN_B in (SELECT FROM TABLE3 WHERE COLUMN_Y=5))
But in general, I would say do outer joins with the ANSI syntax. Even as someone who learned Oracle syntax first and is very comfortable with it, ANSI is much clearer for outer joins.
精彩评论