Database Reference
In-Depth Information
FROM ARTIST A JOIN SONG S JOIN CDTRACK C
JOIN MUSICCD M
ORDER BY 1, 2, 3, 4;
The previous query will return the error message ORA-00905:
missing keyword.
Second Example
. Add the USING clause to each JOIN clause. This
query will succeed and return 125 rows (one for each song in each
CD).
SELECT M.TITLE CD, C.TRACK_SEQ_NO, S.TITLE, A.NAME
FROM ARTIST A JOIN SONG S USING (ARTIST_ID)
JOIN CDTRACK C USING (SONG_ID)
JOIN MUSICCD M USING (MUSICCD_ID)
ORDER BY 1, 2, 3, 4;
Third Example
. Here, the USING clause is replaced by the ON
clause. The result of this query is identical to the second (previous)
example where 125 rows will be returned.
SELECT M.TITLE, C.TRACK_SEQ_NO, S.TITLE, A.NAME
FROM ARTIST A JOIN SONG S ON (A.ARTIST_ID = S.ARTIST_ID)
JOIN CDTRACK C ON (S.SONG_ID = C.SONG_ID)
JOIN MUSICCD M ON (C.MUSICCD_ID =M.MUSICCD_ID)
ORDER BY 1, 2, 3, 4;
This chapter has exposed you to a wide variety of methods and syntax
types for joining tables. Joins can get much more complicated than those
contained within this chapter. However, some highly complex mutable
joins can be simplified with the use of subqueries. Chapter 12 examines
subqueries.
The next chapter shows you how to summarize data using aggregate
functions with the GROUP BY clause.
10.4
Endnotes
1.
Oracle Performance Tuning for 9
i
and 10
g
(ISBN: 1-55558-305-9)
