| Team Fly | |
SQL> select a.id, a.desc1, b.id, b.desc2
2 from temp1 a, temp2 b
3 where a.id = b.id(+);
ID DESC1 ID DESC2
---------- ----- ---------- -----
123 ABCDE
456 FGHIJ 456 ZZZZZ
2. Now, use the left outer join example from Project 2.1:
SQL> select a.id, a.desc1, b.id, b.desc2
2 from temp1 a, temp2 b
3 where a.id(+) = b.id;
ID DESC1 ID DESC2
---------- ----- ---------- -----
456 FGHIJ 456 ZZZZZ
789 MMMMM
3. Now, let's put the two together with a full outer join using union. ANSI SQL outer join syntax provided us with a full outer join option that wasn't available with Oracle's standard SQL. With the union set function in our Oracle tool belt, we have another way to solve this problem. So now, let's take the two queries from the recalled examples and union them together:
SQL> select a.id, a.desc1, b.id, b.desc2
2 from temp1 a, temp2 b
3 where a.id = b.id(+)
4 union
5 select a.id, a.desc1, b.id, b.desc2
6 from temp1 a, temp2 b
7 where a.id(+) = b.id;
ID DESC1 ID DESC2
---------- ----- ---------- -----
123 ABCDE
456 FGHIJ 456 ZZZZZ
789 MMMMM
In this project, by combining the right and left outer join Oracle statements together with a union set operator, we were able to mimic the ANSI SQL full outer join functionality.
| Team Fly | |