sql - Left outer join difference in various databases -

i in understanding difference in way various databases interpret left outer join.

in scenario there 1:1 relationship between left , right table, imagine count of join operation equal count in left table.

i have observed true ms sql oracle (pl/sql) seems having different interpretation of this. count of left outer join in oracle sum of entries in left table + matching entries in right.

kindly confirm if correct in understanding , best methodif have reconcile data between oracle , ms sql.

the code -

select distinct nvl(l11, nvl(l10, nvl(l9, nvl(l8, nvl(l7, nvl(l6, nvl(l5, nvl(l4, nvl(l3, nvl(l2, nvl(l1,       nvl(l0,'err')))))))))))) enditem, case when l1 = 'raw wafer' l0  when l2 = 'abc' l1  when l3 = 'abc' l2  when l4 = 'abc' l3  when l5 = 'abc' l4  when l6 = 'abc' l5  when l7 = 'abc' l6  when l8 = 'abc' l7  when l9 = 'abc' l8  when l10 = 'abc' l9  when l11 = 'abc' l10 end item, g1.item bl1, g2.item bl2, g3.item bl3, g4.item bl4, g5.item bl5, g6.item     bl6, g7.item bl7, g8.item bl8, g9.item bl9, g10.item bl10, g11.item bl11,  decode(g1.item, null, 0, 1) + decode(g2.item, null, 0, 1)  + decode(g3.item, null, 0, 1) + decode(g4.item,      null, 0, 1) + decode(g5.item, null, 0, 1) + decode(g6.item, null, 0, 1) + decode(g7.item, null, 0, 1) +    decode(g8.item, null, 0, 1) + decode(g9.item, null, 0, 1) + decode(g10.item, null, 0, 1) + decode(g11.item, null, 0, 1) cnt, gg.*  tabl1 gg left outer join tabl2 g1 on g1.item = gg.l1 left outer join tabl2 g2 on g2.item = gg.l2 left outer join tabl2 g3 on g3.item = gg.l3           left outer join tabl2 g4 on g4.item = gg.l4           left outer join tabl2 g5 on g5.item = gg.l5           left outer join tabl2 g6 on g6.item = gg.l6 left outer join tabl2 g7 on g7.item = gg.l7 left outer join tabl2 g8 on g8.item = gg.l8 left outer join tabl2 g9 on g9.item = gg.l9 left outer join tabl2 g10 on g10.item = gg.l10 left outer join tabl2 g11 on g11.item = gg.l11   

when count on ms sql suggests count tabl1 while oracle seems proposing more.

thanks.

this perhaps bit long comment.

first, if comparing queries in different databases, why showing code works in 1 of them? query can written in standard way across databases, quite easily, using case , coalesce().

without seeing results, can think of 2 differences might arise. first running different queries on different data in different databases. let's ignore that, because there possibility.

oracle treats null string values , empty string values same. has sorts of repercussions, particularly when using nvl() (or doing aggregation , getting 1 row instead of two). guess unstandard "feature" of oracle causing different results.

you can phrase query differently same result in databases, using case. replace nvl(val1, val2) with:

(case when val1 null or val1 = '' val2 else val1 end)