Team Fly

Page 65

Any of the combinations will produce exactly the same results. Let's hold off on the left outer join example until we revisit the outer join idea later in Project 2-4.

ANSI Full Outer Joins A full outer join is possible when using the ANSI syntax without having to write too much code. With a full outer join, you will be able to return both the right outer join and left outer join results from the same query.

The full outer join queries can be written as full outer join or full join and once again, the on, using, or natural joins are all possible. Let's revisit the Outer Joins Project and try the ANSI syntax out.

Project 2-2 Joining Data Using ANSI SQL Joins

Using the temp1 and temp2 tables we created and populated, let's try out the ANSI right, left, and full outer joins.

Step by Step

We've just learned that you can write the ANSI outer joins with or without the outer keyword in each of the ANSI right, left, and full outer joins. We also learned that the ANSI on, using, and natural join syntax is available as well. The following step-by-step instructions use a combination of these for illustration purposes. Feel free to try alternate syntax, but we encourage you to adopt a consistent style to allow your code to be self-documenting and traceable by other developers.

1. Use the ANSI right outer join:

 
     SQL> select id, desc1, desc2
        2  from   temp2 right outer join temp1
        3          using (id);

              ID DESC1 DESC2
      ---------- ----- -----
             456 EFGH  ZZZZ
             123 ABCD

2. Use the ANSI left outer join, shown in the following:

 
     SQL> select id, desc1, desc2
        2  from  temp2 b natural left join temp1 a;

              ID DESC1 DESC2
      ---------- ----- -----
             456 EFGH  ZZZZ
             789       MMMM

3. Use the ANSI full outer join to complete the syntax:

 
    SQL> select a.id, a.desc1, b.id, b.desc2
       2  from   temp1 a full join temp2 b
       3          on a.id = b.id;

Team Fly