Oracle proprietary joins- joining on multiple conditions

Question

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 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.

Answer 1

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.