Database Reference
In-Depth Information
Figure 10.17
Oracle Format Left
Outer Join of
ARTIST and
GUESTAPPEARA
NCE Tables.
Refer back to Figure 10.15 to validate artists who do not have guest
appearances on any songs. You will see that these artists (starting with
Sheryl Crow and ending with James Taylor) appear in Figure 10.17 with a
blank space in the SONG_ID and GUESTARTIST_ID. The query could
not match any row in the GUESTAPPEARANCE table with these artists
in the ARTIST table. Oracle Database 10
g
automatically returns a null
value as a placeholder in the results for the unmatched rows.
Look at the last five rows in Figure 10.17. These are the artists who do
make guest appearances. Notice that the ARTIST_ID column and the
GUESTARTIST_ID column contain the same number in every row. This
makes sense because the query equates the values in the two columns. These
rows are finding themselves in the ARTIST table. Any row in the GUE-
STAPPEARANCE table must match a row in the ARTIST table.
The second left outer join query, shown following, is the ANSI version
of the first left outer join query. The result is shown in Figure 10.18. One
difference between the Oracle format join in Figure 10.17 and the ANSI
format join in Figure 10.18 is the sorted order of null values.
SELECT A.NAME, GA.SONG_ID, A.ARTIST_ID, GA.GUESTARTIST_ID
FROM ARTIST A LEFT OUTER JOIN GUESTAPPEARANCE GA
ON (A.ARTIST_ID = GA.GUESTARTIST_ID);
