Database Reference
In-Depth Information
Figure 10.13
The JOIN Clause
without the ON
Clause.
The ARTIST table has 15 rows and the GUESTAPPEARANCE table
has 5 rows. When you execute the join, you get 75 rows: 15 multiplied by 5
equals 75 rows. This is another unwanted Cartesian product. The two
tables are not being joined correctly because there is no common column
name for the JOIN clause to utilize. There is a column in both tables hav-
ing related values, but the column name is different in each table. In the
ARTIST table it is called ARTIST_ID, and in the GUESTAPPEARANCE
table it is called GUESTARTIST_ID.
Another row count can now be verified.
SELECT COUNT(*) FROM GUESTAPPEARANCE;
ARTIST has 15 rows.
GUESTAPPEARANCE has 5 rows.
This Cartesian product in Figure 10.13 caused by lack of common col-
umn names can be resolved by utilizing the ON clause. The result of the
next query is shown in Figure 10.14:
SELECT A.NAME, GA.COMMENT_TEXT FROM GUESTAPPEARANCE GA
JOIN ARTIST A ON (GA.GUESTARTIST_ID = A.ARTIST_ID);
