Database Reference
In-Depth Information
Figure 10.21
A Full Outer Join
Between ARTIST,
GUESTAPPEARA
NCE, and SONG
Tables.
COLUMN NAME FORMAT A32 HEADING "Artist"
COLUMN TITLE FORMAT A32 HEADING "Song"
SELECT A.NAME AS NAME, S.TITLE AS TITLE
FROM ARTIST A FULL OUTER JOIN GUESTAPPEARANCE GA
ON (A.ARTIST_ID = GA.GUESTARTIST_ID)
FULL OUTER JOIN SONG S ON (GA.SONG_ID = S.SONG_ID)
ORDER BY NAME, TITLE;
The query lists all artists and all songs, matching songs and artists
together if the artist makes a guest appearance on the related song. If an art-
ist does not make a guest appearance, the song title is NULL (outer join
between artists and guest appearances). If a song has no guest appearances,
the artist name is NULL (outer join between songs and guest appearances).
Figure 10.21 shows only part of the results, illustrating how either the
title or the name can be NULL. There are 130 rows returned in the query.
10.3.4
Self-Join
A self-join joins a table to itself. Table aliases must be used to distinguish
between two different copies of the same table. A table such as this would
