Database Reference
In-Depth Information
Figure 10.18
ANSI Format Left
Outer Join of the
ARTIST and
GUESTAPPEARA
NCE Tables.
The third and last left outer join query is a more complex variation of
the first two using the ANSI format and the DECODE function.
Note:
The DECODE function is an embedded case statement (see
Chapter 9).
The following query lists all of the artists in the ARTIST table. It
returns one of two phrases, depending on whether the artist makes a guest
appearance on a song or not. If not, the phrase “ is an Artist.” follows the
artist's name. If otherwise, the phrase “ made a guest appearance on …” fol-
lows the artist's name, including the appropriate song title. The result as
shown in Figure 10.19 is a left outer join between all three ARTIST, GUE-
STAPPEARANCE, and SONG tables.
SELECT A.NAME||
DECODE (S.TITLE, NULL,' is an Artist.'
,' made a guest appearance on '||S.TITLE||'.'
) as "What they did"
FROM ARTIST A LEFT OUTER JOIN GUESTAPPEARANCE GA
ON (A.ARTIST_ID = GA.GUESTARTIST_ID)
LEFT OUTER JOIN SONG S ON (S.SONG_ID = GA.SONG_ID)
ORDER BY A.NAME, S.TITLE;
