Database Reference
In-Depth Information
Figure 10.12
The JOIN Clause
and the USING
Clause.
SELECT GA.COMMENT_TEXT ||' and '||IA.COMMENT_TEXT
FROM GUESTAPPEARANCE GA JOIN INSTRUMENTATION IA
USING (SONG_ID, GUESTARTIST_ID);
Note:
||' and '|| concatenates the fields as trimmed strings. This is an alter-
native to preformatting of columns using the COLUMN clause. See Chap-
ter 8 for more information on formatting.
The previous example in Figure 10.12 demonstrates how to exclude
unwanted columns from a join. The USING clause was used to prevent
joining on the COMMENT_TEXT column between the INSTRUMEN-
TATION and GUESTAPPEARANCE tables. In some cases, the opposite
is required. The ON clause is used to join tables on column names where
the column names required in the join are not the same names in separate
tables. This therefore leads us to an analysis of the ANSI format ON clause.
10.3.2.2
The ON clause
The result of the following query is shown in Figure 10.13:
SELECT A.NAME, GA.COMMENT_TEXT FROM GUESTAPPEARANCE GA
NATURAL JOIN ARTIST A;
