|
|
 |
 |
|
Join the Discussion
|
"Share your questions and tap the
knowledge of hundreds of your peers."
Mike
Chapple
|
|
 |
 |
|
|
 |
In several recent articles we explored the fundamental
concepts of SQL, the process of creating
databases and database tables and the art of retrieving
data from a database using simple queries. This article expands on
these topics and looks at using join techniques to retrieve data from multiple
tables.
By way of example, let's return to our fictitious XYZ
Corporation. XYZ utilizes an Oracle database to track the movements of
their vehicle fleet and drivers between their facilities. Some employees are assigned to drive trucks while others are
assigned to drive cars. Take a moment to examine the following two tables
from their vehicle management database:
drivers
|
licensenum |
lastname |
firstname |
location |
class |
|
13232 |
Baker |
Roland |
New York |
Car |
|
18431 |
Smythe |
Michael |
Miami |
Truck |
|
41948 |
Jacobs |
Abraham |
Seattle |
Car |
|
81231 |
Ryan |
Jack |
Annapolis |
Car |
vehicles
|
tag |
location |
class |
|
D824HA |
Miami |
Truck |
|
H122JM |
New York |
Car |
|
J291QR |
Seattle |
Car |
|
L990MT |
Seattle |
Truck |
|
P091YF |
Miami |
Car |
In the previous article, we looked at methods used to
retrieve data from single tables. For example, we could use simple SELECT
statements to answer questions such as:
-
Which drivers are located in New York?
-
How many cars are in each city?
-
Which drivers assigned to drive trucks are located in
Miami?
Practical applications often require the combination of
data from multiple tables. Our vehicle managers might make requests like
the following:
Granted, it would be possible to create complex SELECT
statements using subqueries to fulfill these requests. However, there's a
much simpler method -- the use of inner and outer joins. We'll explore
each of these concepts in the next two sections of this article. Read
on!
Next page > Inner
Joins > Page 1, 2,
3
|