Database Reference
In-Depth Information
The ANSI format would allow a natural join by matching the
ARTIST_ID column names on the ARTIST and SONG tables. Use cau-
tion with the ANSI format of the natural join. The NATURAL JOIN
clause matches all columns with identical column names in the two tables.
If columns have identical names but are not actually appropriate for join-
ing, such as the COMMENT_TEXT columns in the GUESTAPPEAR-
ANCE and INSTRUMENTATION tables, the ANSI format would match
the tables incorrectly. Similarly, if there are no matching column names, the
ANSI NATURAL JOIN format results in a Cartesian product. Because of
the potential problems with a natural join, we have to be able to define the
columns to be used in a join. This leads us to the ANSI join format USING
and ON clauses.
10.3.2.1
The USING clause
The USING clause can be added to the ANSI join format where columns
with identical names should be omitted from the join. The Oracle propri-
etary join format does not have the USING clause because you always
name the columns to be joined in the WHERE clause. The result of the fol-
lowing query is shown in Figure 10.10:
SELECT SONG_ID, GUESTARTIST_ID, INSTRUMENT_ID
FROM GUESTAPPEARANCE NATURAL JOIN INSTRUMENTATION;
Figure 10.10
A Natural Join
without the
USING Clause.
 
Search WWH ::




Custom Search