Project 2-1 Joining Data Using Inner and Outer Joins

Team Fly

Page 62

   CUST_ID CUST_LAST_NAME                        PROD_ID QUANTITY_SOLD
---------- ---------------------------------- ---------- -------------
         1 Kessel
        80 Carpenter                                 127             1
        80 Carpenter                                  36             1

The outer join allows us to display the CUSTOMERS columns alongside the nulls for the non-matched rows SALES records. A simple join would have only returned the two records for cust_id 80.

Project 2-1 Joining Data Using Inner and Outer Joins

With the sample tables Oracle has provided, there are no outer join examples. When we learn about referential integrity and constraints later in this chapter, this will become a little clearer. Suffice it to say that the customers, products, and promotions in the sales table all exist in their respective tables. In this project, we're going to create our own simple tables where we can better demonstrate outer joins. Once we discuss the ANSI version of joins, we'll revisit this project and introduce a new concept available only with the ANSI syntax.

Step by Step

Let's start by creating and populating two very simple tables that will join on a common column. Open up a SQL*Plus session and issue the following commands:

1. create table temp1 (id number(3), desc1 char(5));

2. create table temp2 (id number(3), desc2 char(5));

3. insert into temp1 values (123, 'ABCDE');

4. insert into temp1 values (456, 'FGHIJ');

5. insert into temp2 values (456, 'ZZZZZ');

6. insert into temp2 values (789, 'MMMMM');

Both table temp1 and temp2 each have two records. The two tables join with each other on the ''ID" column, and they have one ID in common: 456. Let's continue now by displaying all the records from temp1 and temp 2 followed by writing an inner, right outer, and left outer join. In SQL*Plus, enter the code from the following code listings and check that you get the same output.

1. Display the records from temp1 (remember to use select * when doing so):

              ID DESC1
      ---------- -----
             123 ABCDE
             456 FGHIJ

Team Fly