Database Reference
In-Depth Information
Figure 10.14
The JOIN Clause
with the ON
Clause.
10.3.3
Outer Join
An outer join selects rows from both tables including rows from one or both
tables without matching rows in the other table. To show how outer joins
work, we will use the ARTIST, SONG, and GUESTAPPEARANCE tables.
The GUESTAPPEARANCE table contains some artists and some songs.
Examine the MUSIC schema entity relationship diagram in Chapter 1
again. An outer join is only useful if at least one side of the relationship can
be zero. In the diagram, the symbols or indicate that zero
rows only on the left side of the symbol may be related to the table on the
right side of the symbol. The many-to-many relationship between the
ARTIST and SONG tables through the GUESTAPPEARANCE table
effectively makes the relationship between the ARTIST and SONG tables a
zero, one- or many-to-zero, or one-to-many relationship. In other words,
one artist may have been a guest appearance artist on no songs, or on one
song, or on many songs. And a single song may have no guest appearances,
or one guest appearance, or many guest appearances. Thus by joining these
three tables, we can demonstrate the use of left, right, and full outer joins.
Before we execute join queries, we need to know which artist and song
rows are present on the GUESTAPPEARANCE table. The result in Figure
10.15 shows artists who do not appear as guests on any particular song.
SELECT NAME FROM ARTIST WHERE ARTIST_ID NOT IN
(SELECT GUESTARTIST_ID FROM GUESTAPPEARANCE);
 
Search WWH ::




Custom Search