Database Reference
In-Depth Information
Figure 10.16
All Songs without
Guest Appearances
Including the Word
“Me” in the Song
Title.
the left with no matching rows in the table on the right will contain null
values for the columns from the table on the right side.
Three queries are used to explain left outer joins, each becoming pro-
gressively more complex. The result of the first query is shown in Figure
10.17 with a left outer join of the ARTIST and GUESTAPPEARANCE
tables. SONG_ID and GUESTARTIST_ID columns contain null values
for ARTIST rows that do not exist as guest appearances.
SELECT A.NAME, GA.SONG_ID, A.ARTIST_ID, GA.GUESTARTIST_ID
FROM ARTIST A, GUESTAPPEARANCE GA
WHERE A.ARTIST_ID = GA.GUESTARTIST_ID(+);
Note:
The rule of thumb for the (+) operator is it appears on the side of the
join “deficient in information.” In plain English: The table “deficient in
information” is the table that cannot match every single row in the other
table. A null valued “row” is added to the deficient table, using the plus
sign, taking the place of the missing rows in the resulting join query. Put the
(+) symbol on the “deficient” table's column found in the WHERE clause.
Remember, without the (+) symbol, only rows that match in BOTH tables
are selected.
