Database Reference
In-Depth Information
SELECT A.NAME||
DECODE(NVL(
(SELECT TITLE FROM SONG WHERE SONG_ID = GA.SONG_ID)
, NULL), NULL,' is an Artist.'
,' made a guest appearance on '
||NVL((SELECT TITLE FROM SONG
WHERE SONG_ID = GA.SONG_ID),NULL)||'.'
) AS "What they did"
FROM ARTIST A, GUESTAPPEARANCE GA
WHERE A.ARTIST_ID = GA.GUESTARTIST_ID(+)
ORDER BY A.NAME, GA.SONG_ID;
10.3.3.2
Right Outer Join
A right outer join is the converse of a left outer join. A right outer join
returns all rows from the table on the right of the join plus any matching
rows from the table on the left. Rows from the table on the right with no
matching rows in the table on the left will contain null values for the col-
umns from the table on the left side.
Following is an example of an ANSI-formatted right outer join state-
ment. The equivalent Oracle form with an outer join on three tables does
not exist unless a subquery is used (see Chapter 12). It is not possible to
execute an outer join between more than two tables in a single query using
the Oracle format; an error will result (ORA-01417: a table may be outer
joined to at most one other table).
The result of the following query is shown in Figure 10.20. The query in
Figure 10.20 is an ANSI format right outer join between all three ARTIST,
GUESTAPPEARANCE, and SONG tables.
SELECT A.NAME "Artist", S.TITLE "Song"
FROM GUESTAPPEARANCE GA RIGHT OUTER JOIN SONG S
ON (GA.SONG_ID = S.SONG_ID)
RIGHT OUTER JOIN ARTIST A
ON (GA.GUESTARTIST_ID = A.ARTIST_ID)
ORDER BY S.TITLE, A.NAME;
The query first performs a right outer join between the GUESTAP-
PEARANCE and SONG tables. Because the SONG table is on the right,
all songs are retrieved. Next, this result set is right outer joined to the ART-
IST table using the GUESTARTIST_ID. Because not all songs have a guest
appearance, those songs have null values in the GUESTARTIST_ID and
