Database Reference
In-Depth Information
Because the relationship was omitted from the query, Oracle assumes that
each row in the first table is related to every row in the second table: a Car-
tesian Product or multiplication of both tables.
10.3.2
Natural or Inner Join
The objective of joins is to allow the retrieval of rows from separate tables,
using an existing relationship. Thus, when selecting data from two tables, it
is best to link the rows in the tables together based on common values.
When selecting from the SONG and ARTIST tables, without linking the
songs to their respective artists, a meaningless result is returned. The entity
relationship diagram in Chapter 1 shows the SONG and ARTIST tables
linked by a common column called ARTIST_ID. Figure 10.9 shows the
result of the following query:
SELECT A.NAME, S.TITLE FROM ARTIST A, SONG S
WHERE A.ARTIST_ID = S.ARTIST_ID;
The result in Figure 10.9 shows a result of 118 rows. This is the correct
value because the SONG table has a total of 118 rows and the ARTIST
table has 15 rows. There is a one-to-many relationship between the ART-
IST and SONG tables. The maximum number of selectable rows using an
inner join between these two tables is the number of rows on the many side
of the relationship.
Figure 10.9
A Natural Join
Between the
ARTIST and
SONG Tables.
