Page status
Needs formatting
Both T-SQL and PL/SQL support the common version of "outer joins" and they behave equally when compared in different tests. But PL/SQL also understand an proprietary Oracle notation with a "(+)".
Using PL/SQL you can write:
select * from T1, T2
where T1.F1 = T2.F1 (+)
which is an outer join, where the interpreter create needed NULL rows in T2 while joining.
It's a nice notation that you can quickly edit in and out by adding a few characters. It is adopted by most PL/SQL people, some of which never write the "left outer join" sentences.
I use both and by accident, I stubled across a special case, where the two notations causes different result on PL/SQL alone. Since T-SQL do not understand the "(+)" notation, the case is not relevant there.
On Oracle:
Lets assume the following code to create needed testdata in two tables.
create table T1 (f1 int, f2 char);
create table T2 (f1 int, f2 char);
insert into T1 (f1, f2) values(null, 'A');
insert into T1 (f1, f2) values(1, 'A');
insert into T1 (f1, f2) values(2, 'A');
insert into T1 (f1, f2) values(null, 'B');
insert into T1 (f1, f2) values(4, 'B');
insert into T1 (f1, f2) values(8, 'B');
insert into T1 (f1, f2) values(null, 'C');
insert into T1 (f1, f2) values(null, 'C');
insert into T2 (f1, f2) values (1, 'A');
insert into T2 (f1, f2) values (2, 'A');
insert into T2 (f1, f2) values (4, 'A');
insert into T2 (f1, f2) values (1, 'B');
insert into T2 (f1, f2) values (2, 'B');
insert into T2 (f1, f2) values (4, 'B');
You may see all the data with this PL/SQL:
select * from T1, T2
where T1.F1 = T2.F1 (+)
F1 F2 F1_1 F2_1
1 A 1 A
2 A 2 A
4 B 4 A
1 A 1 B
2 A 2 B
4 B 4 B
C
C
B
A
8 B
This PL/SQL
select * from T1, T2
where T1.F1 = T2.F1 (+)
and T1.F2 = 'A'
order by T1.F1, T1.F2, T2.F1, T2.F2
returns 5 rows which is also exactly the same as using the "left outer join" notation.
F1 F2 F1_1 F2_1
1 A 1 A
1 A 1 B
2 A 2 A
2 A 2 B
A
This PL/SQL
select * from T1, T2
where T1.F1 = T2.F1 (+)
and (T1.F2 = 'A' or T2.F1 is null)
order by T1.F1, T1.F2, T2.F1, T2.F2
returns 9 rows which is quite correct.
{|
! F1 !! F2 !! F1_1 !! F2_1
|-
| 1 || A || 1 || A
|-
| 1 || A || 2 || B
|-
| 2 || A || 2 || A
|-
| 2 || A || 2 || B
|-
| 8 || B || ||
|-
| || A || ||
|-
| || B || ||
|-
| || C || ||
|-
| || C || ||
|}
When constructing this using the "left outer join" the result is the same using this SQL. A funny thing in this SQL is that we tell that we also want the empty rows from T2, shown with bold.
select *
from T1 left outer join T2
on T1.F1 = T2.F1
where T1.F2 = 'A' or T2.F1 is null
order by T1.F1, T1.F2, T2.F1, T2.F2
This PL/SQL which is created with the "left outer join"
select *
from T1 left outer join T2
on T1.F1 = T2.F1 and T1.F2 = 'A'
order by T1.F1, T1.F2, T2.F1, T2.F2
returns 10 rows which is also quite correct. This is correct since the and T1.F2 = 'A' is a continuation of the join filter and not a part of the resultset filter (where)
F1 F2 F1_1 F2_1
1 A 1 A
1 A 1 B
2 A 2 A
2 A 2 B
4 B
8 B
A
B
C
C
But how do you do this with the "(+)" notation.
My task to you, should you choose to accept, is to find a way to create the 10 rows using the "(+)" notation.
Your PC will selfdestruct in 10 seconds - 1 - 2 - 3 - 4....