Database Reference
In-Depth Information
Figure 10.15
Artists not Making
Guest Appearances
on Any Song.
The result of the next query is shown in Figure 10.16, showing all songs
without guest appearances and including the word “Me” in the song title.
There is no longer a need to verify the health of join statements with row
counts. Filtering and sorting can now be implemented using the WHERE
and ORDER BY clauses, respectively. See Chapter 5 for filtering and Chap-
ter 6 for sorting.
SELECT TITLE FROM SONG WHERE SONG_ID NOT IN
(SELECT SONG_ID FROM GUESTAPPEARANCE)
AND (TITLE LIKE '%Me%' OR TITLE LIKE '%Me');
When creating outer joins, the examples used here contain three tables
in order to retrieve both artist names and song titles in the same query. Any
join made up of more than two tables is called a mutable join. Mutable
joins are explained at the end of this chapter. One format version of each
type of outer join will be used, along with each Oracle proprietary or ANSI
standard example, in order to explain both join formats with more clarity.
10.3.3.1
Left Outer Join
A left outer join will return all rows from the table on the left of the join
plus any matching rows from the table on the right. Rows from the table on
