The outer Join Syntax
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 extra NULL rows in T2 while joining, to beable to show all T1 rows.
It's a nice notation, so you can quickly enable or disable the outer join, just 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, but by accident I stubled across a special case, where the
(+) notation and the
left outer join causes different result, both in PL/SQL.
Since T-SQL do not understand the "(+)" notation, the case is not relevant there.Prepare some data
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');
Outer join with plus notation
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 | | |
Filtered outer join with plus notation
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
Filters data and nw returns only 5 rows which is also exactly the same as using the "left outer join" notation.
{btable:
Result
cols1=text-align:right;
cols2=text-align:right;
cols3=text-align:right;
cols4=text-align:right;
r&color:red;
F1 F2 F1_1 F2_1
1 A 1 A
1 A 1 B
2 A 2 A
2 A 2 B
null A null null
}
Filtered outer join with plus notation including null rows
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
Filtered outer join with ANSI notation including null rows
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.
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
Filtered outer join with ANSI notation including null rows
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
The big question
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 - 9 - 8 - 7 - 6....