Java Reference
In-Depth Information
Figure 9-1: Primary and Foreign keys are used to define intersecting data sets.
Inner Joins
The Inner Join of these two sets is the intersection of the sets. For example, to retrieve all the
information required to invoice a client, you would require the name and address information from table
A, joined on the order information from table B. The intersection of these sets is the set of primary keys
in the Customers table and the set of foreign keys in the order information table that match the required
Customer_ID. The Inner Join of the two tables is the subset of the rows in the name and address table,
which has the required Customer_ID, and the subset of the rows in the order information table, which
references that Customer_ID. This is shown as the shaded portion of Figure 9-1 .
Outer Joins
There are three kinds of Outer Joins:
 
Full Outer Joins , which, in Set Theory terms, are Unions of the sets. A Full Outer Join includes
all of both joined sets. This would correspond to the entire area of both circles in Figure 9-1 .
 
Left Outer Joins, which are the entire set on the left, plus the contents of the intersection. This
would correspond to the entire area of the left circle A, plus the shaded area in Figure 9-1 .
 
Right Outer Joins, which are the entire set on the left, plus the contents of the intersection. This
would correspond to the entire area of the right circle B, plus the shaded area in Figure 9-1 .
It is important to note that it is really the keys that form the members of the sets, since only the keys are
alike. The row data itself, being different from one table to another, can't intersect with row data from
another table. This observation underscores the importance of keys in linking tables, which is reviewed
in the next section .
Note
Although this discussion of Joins in terms of sets was illustrated using only two sets, the
concept applies to any number of tables or sets.
Since the use of JOINS is heavily dependent on using keys, the next section reviews what primary and
foreign keys are, and how they are used.
Keys
First, it is important to understand keys . In each of the four tables in the example, there is an identifier
such as customer number or item number. These identifiers are called keys and are used primarily to
provide a unique reference to a given record. Database management systems use two kinds of keys:
 
Primary keys
 
Foreign keys
Search WWH ::




Custom Search