Java Reference
In-Depth Information
Chapter 9:
Joins and Compound Queries
In This Chapter
One of the most powerful features of SQL is its ability to combine data from several tables into a single
result set. When tables are combined in this way, the operation performed is called a JOIN. There are
two primary types of JOIN, and a number of different ways in which they can be performed.
Another way to combine data from different tables into a single result set is to use the UNION operator.
This chapter discusses the different types of JOINS, and the use of the UNION operator.
Joining Tables
Chapter 2
explained how an efficient and reliable database design frequently requires the information
in a practical database will be distributed across several tables, each of which contains sets of logically
related data. A typical example might be a database containing these four tables:
Customers,
containing customer number, name, shipping address, and billing information
Inventory,
containing item number, name, description, cost, and quantity on hand
Orders,
containing order number, customer number, order date, and ship date
Ordered_Items,
containing order number, item number, and quantity
When a customer places an order, an entry is made in the Orders Table, assigning an order number
and containing the customer number and the order date. Then entries are added to the Ordered_Items
table, recording order number, item number, and quantity. To fill a customer order, you need to
combine the necessary information from each of these tables.
Using
JOIN
, you are able to combine data from these different tables to produce a detailed invoice.
This invoice will show the customer name, shipping address, and billing information from the
Customers table, combined with a detailed list of the items ordered from the Ordered_Items table,
supported by detailed description, quantity, and unit price information from the inventory table.
Cross-Reference
Primary and Foreign Keys are also discussed in
Chapter 1
,
which provides
a more theoretical overview of Relational Database Management Systems.
Types of Joins
There are two major types of Joins: Inner Joins and Outer Joins. The difference between these two
types of Joins goes back to the basic Set Theory underlying relational databases. You can imagine the
keys of two database tables, A and B as intersecting sets, as shown in
Figure 9-1
.
