Database Reference
In-Depth Information
Figure 10.11
Join Column
Names not
Explicitly
Qualified Cause
Errors.
The query in Figure 10.10 returned a message indicating that no rows
were selected. This is because the NATURAL JOIN clause will attempt to
match the two tables on all column names common to both tables. Exam-
ine the Oracle format equivalent in Figure 10.10. Note how the
COMMENT_TEXT columns are included in the WHERE clause. The
Oracle format equivalent in this case is an exact interpretation of the ANSI
format for the purposes of clear explanation.
Note:
Aliases are not always required for the ANSI format example.
The result of the next query is shown in Figure 10.11:
SELECT GA.SONG_ID, GA.GUESTARTIST_ID, IA.INSTRUMENT_ID
FROM GUESTAPPEARANCE GA NATURAL JOIN INSTRUMENTATION IA;
The error is returned in Figure 10.11 because Oracle attempts the join
using the SONG_ID, GUESTARTIST_ID, and COMMENT_TEXT col-
umns; these three column names are common to both tables. The addition
of the alias to the column names in the previous query effectively changes
the names of the columns. Thus GA.SONG_ID is not the same as
IA.SONG_ID or SONG_ID.
Getting back to the original query: The ANSI format requires some
refinement to remove the unwanted COMMENT_TEXT column from
the join. This is done with the USING clause. The result is shown in Figure
10.12. Note the absence of the NATURAL keyword. This join is no longer
a purely naturally occurring join because column names are specified.
Search WWH ::




Custom Search