title: Oracle proprietary joins- joining on multiple conditions
body: <p>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.</p>
  
  <p>1)ANSI Join 1-Works</p>
  
  <pre><code>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
  </code></pre>
  
  <p>1) gives output like this:</p>
  
  <pre><code>COLUMN_A  COUNT(COLUMN_A)
  --------------------------
  A       0
  B       0
  C       1
  D       1
  E       0
  </code></pre>
  
  <p>2)ANSI Join 2-Doesn't work</p>
  
  <pre><code>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
  </code></pre>
  
  <p>3)Oracle's proprietary join-Doesn't work</p>
  
  <pre><code>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
  </code></pre>
  
  <p>2) &amp; 3) gives output like this:</p>
  
  <pre><code>COLUMN_A  COUNT(COLUMN_A)
  --------------------------
  C       1
  D       1
  </code></pre>
  
  <p>I understand (2,ANSI) &amp; (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.</p>