Database Reference
In-Depth Information
be a candidate for further Normalization or is a result of a Denormaliza-
tion performance improvement. Some examples of situations in which
self-joins might be useful would be grouping self-joins or hierarchical
(fishhook) self-joins.
Note:
A fishhook is a table with a one-to-many relationship to its own pri-
mary key. Thus the primary key would be both primary key and a unique
foreign key.
10.3.4.1
Grouping Self-Join
A grouping self-join implies that some rows have a one-to-many relation-
ship with other rows in the same table. There is a “Best of ” compilation CD
by Sheryl Crow in the MUSIC schema containing songs on other CDs.
The self-join query following lists SONG_ID values appearing on more
than one CD. Note that the line in the WHERE clause containing the ine-
quality operator will prevent any song from being listed twice. The result is
shown in Figure 10.22.
SELECT B.MUSICCD_ID, B.TRACK_SEQ_NO, A.SONG_ID
FROM CDTRACK A JOIN CDTRACK B ON (A.SONG_ID = B.SONG_ID)
WHERE B.MUSICCD_ID <> A.MUSICCD_ID
ORDER BY MUSICCD_ID, TRACK_SEQ_NO, SONG_ID;
This self-join searches for tracks (songs) that are found on more than
one CD. Picture in your mind's eye two copies of the CDTRACK table
side by side. Each row in the left table (Table A) is matched with one row
(itself ) or more than one row (same song on another CD) in the right table
(Table B). Eliminate the rows where you have matched a track to itself by
comparing the MUSICCD_ID in the two rows. If the SONG_ID values
are the same but the MUSICCD_ID values are different, the song is
selected in the query. The SONG_ID value 1 in Figure 10.22 appears on
two CDs: #1 and #11.
The next query contains all tracks by Sheryl Crow; the inequality opera-
tor is now missing. The result is shown in Figure 10.23.
SET PAGES 80 LINESIZE 132
COLUMN CD FORMAT A24 HEADING "CD"
COLUMN TRACK FORMAT 990 HEADING "Track"
