Could someone help me on how to convert SQL(PL-SQL) from left join to (+)?
The example of left join as below. I wonder how to convert left join to (+) operation instead?
with a as (
select 1 as a1, 1 as a2 from dual
union select 1, 2 from dual
union select 2, 1 from dual
union select 2, 2 from dual
),
b as (
select 1 as b1, 1 as b2, null as b3 from dual
union select 2, 2, 1 from dual
union select 2, 2, null from dual
)
select *
from a
left join b on a.a1 = b.b1
and a.a2 = b.b2
and (b.b3 is not null or b.b3 > 0)
If I do the following, the result set is different
select *
from a, b
where a.a1 = b.b1(+)
and a.a2 = b.b2(+)
and (b.b3 is not null or b.b3 > 0)
I expect to convert left join to (+) to give the same result.
This is what your original query returns:
SQL> with a as (
2 select 1 as a1, 1 as a2 from dual
3 union select 1, 2 from dual
4 union select 2, 1 from dual
5 union select 2, 2 from dual
6 ),
7 b as (
8 select 1 as b1, 1 as b2, null as b3 from dual
9 union select 2, 2, 1 from dual
10 union select 2, 2, null from dual
11 )
12 select *
13 from a
14 left join b on a.a1 = b.b1
15 and a.a2 = b.b2
16 and (b.b3 is not null or b.b3 > 0);
A1 A2 B1 B2 B3
---------- ---------- ---------- ---------- ----------
2 2 2 2 1
1 2
2 1
1 1
SQL>
Result of your attempt to switch to the old Oracle's outer join operator (+):
<snip>
12 select *
13 from a, b
14 where a.a1 = b.b1(+)
15 and a.a2 = b.b2(+)
16 and (b.b3 is not null or b.b3 > 0);
A1 A2 B1 B2 B3
---------- ---------- ---------- ---------- ----------
2 2 2 2 1
SQL>
What to do? All b columns have to have the outer join operator (see line #16):
<snip>
12 select *
13 from a, b
14 where a.a1 = b.b1(+)
15 and a.a2 = b.b2(+)
16 and (b.b3(+) is not null or b.b3(+) > 0);
A1 A2 B1 B2 B3
---------- ---------- ---------- ---------- ----------
2 2 2 2 1
2 1
1 2
1 1
SQL>