sql - Oracle proprietary joins- joining on multiple conditions -

i've follwing 2 versions of ansi compliant sql(column/table names changed protect confidential data), of 1 of them satisfies requirement following right logic while other doesn't.

1)ansi join 1-works

select b.column_a,   count(a.column_a) table1 right outer join   (select column_a table2 column_x='test') b on  a.column_a = b.column_a , a.column_b in (select table3 column_y=5)  --works group b.column_a 

1) gives output this:

column_a  count(column_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) table1 right outer join   (select column_a table2 column_x='test') b on  a.column_a = b.column_a a.column_b in (select table3 column_y=5)      --doesn't work group b.column_a 

3)oracle's proprietary join-doesn't work

select b.column_a,   count(a.column_a) table1 a,(select column_a table2 column_x='test') b a.column_a(+) = b.column_a , a.column_b in (select table3 column_y=5) --doesn't work group b.column_a 

2) & 3) gives output this:

column_a  count(column_a) -------------------------- c       1 d       1 

i understand (2,ansi) & (3,proprietary) equivalent. there equivalent proprietary sql (1,ansi)?. welcome. thanks. edit: i've updated question sample output.

you want change condition:

and a.column_b in (select table3 column_y=5) 

to either this:

and a.column_b (+) in (select table3 column_y=5) 

or this:

and (a.column_b null or a.column_b in (select table3 column_y=5)) 

but in general, outer joins ansi syntax. learned oracle syntax first , comfortable it, ansi clearer outer joins.