Accessing Databases with JDBC - Java: How to Program Early Objects - page 1057

thor information for authors who wrote multiple books. Often, it's necessary to merge data
from multiple tables into a single result. Referred to as joining the tables, this is specified by
an INNER JOIN operator, which merges rows from two tables by matching values in columns
that are common to the tables. The basic form of an INNER JOIN is:

SELECT columnName1 , columnName2 , …
FROM table1
INNER JOIN table2
ON table1 . columnName = table2 . columnName

The ON clause of the INNER JOIN specifies the columns from each table that are com-
pared to determine which rows are merged—these fields almost always correspond to the
foreign-key fields in the tables being joined. For example, the following query produces a
list of authors accompanied by the ISBNs for books written by each author:

SELECT FirstName, LastName, ISBN
FROM Authors
INNER JOIN AuthorISBN
ON Authors.AuthorID = AuthorISBN.AuthorID
ORDER BY LastName, FirstName

The query merges the FirstName and LastName columns from table Authors with the
ISBN column from table AuthorISBN , sorting the results in ascending order by LastName
and FirstName . Note the use of the syntax tableName . columnName in the ON clause. This
syntax, called a qualified name , specifies the columns from each table that should be com-
pared to join the tables. The “ tableName . ” syntax is required if the columns have the same
name in both tables. The same syntax can be used in any SQL statement to distinguish
columns in different tables that have the same name. In some systems, table names quali-
fied with the database name can be used to perform cross-database queries. As always, the
query can contain an ORDER BY clause. Figure 24.19 shows the results of the preceding
query, ordered by LastName and FirstName . [ Note: To save space, we split the result of the
query into two columns, each containing the FirstName , LastName and ISBN columns.]

Common Programming Error 24.2
Failure to qualify names for columns that have the same name in two or more tables is an
error. In such cases, the statement must precede those column names with their table names
and a dot (e.g., Authors.AuthorID ).

FirstName LastName ISBN FirstName LastName ISBN

Abbey Deitel 0132121360 Harvey Deitel 0133764036

Abbey Deitel 0133570924 Harvey Deitel 0133378713

Abbey Deitel 0133764036 Harvey Deitel 0136151574

Abbey Deitel 0133406954 Harvey Deitel 0133379337

Abbey Deitel 0132990601 Harvey Deitel 0133406954

Abbey Deitel 0132151006 Harvey Deitel 0132990601

Harvey Deitel 0132121360 Harvey Deitel 013299044X

Harvey Deitel 0133570924 Harvey Deitel 0132575655

Fig. 24.19 | Sampling of authors and ISBNs for the topics they have written in ascending
order by LastName and FirstName . (Part 1 of 2.)