|
|
 |
|
|
Join the Discussion
|
"Share your questions and tap the
knowledge of hundreds of your peers."
Mike
Chapple
|
|
|
|
|
|
|
Inner joins (also known as equijoins) are used to contain
information from a combination of two or more tables. The join condition
determines which records are paired together and is specified in the WHERE
clause. For example, let's create a list of driver/vehicle match-ups where
both the vehicle and driver are located in the same city. The following
SQL query will accomplish this task:
SELECT lastname, firstname, tag
FROM drivers, vehicles
WHERE drivers.location = vehicles.location
And let's take a look at the results:
lastname
firstname
tag
-------- --------- ---
Baker
Roland
H122JM
Smythe Michael
D824HA
Smythe Michael
P091YF
Jacobs Abraham J291QR
Jacobs Abraham L990MT
Notice that the results are exactly what we sought.
It is possible to further refine the query by specifying additional criteria in
the WHERE clause. Our vehicle managers took a look at the results of our
last query and noticed that the previous query matches drivers to vehicles that
they are not authorized to drive (e.g. truck drivers to cars and
vice-versa). We can use the following query to resolve this problem:
SELECT lastname, firstname, tag,
vehicles.class
FROM drivers, vehicles
WHERE drivers.location = vehicles.location
AND drivers.class = vehicles.class
Notice that in this example we needed to specify the source
table for the class attribute in the SELECT clause. This is due to the fact that class is unambiguous –
it appears in both tables and we need to specify which table’s column should
be included in the query results. In
this case it does not make a difference as the columns are identical and they
are joined using an equijoin. However,
if the columns contained different data this distinction would be critical.
Here are the results of this query:
lastname
FirstName Tag
Class
-------- --------- ---
-----
Baker Roland
H122JM Car
Smythe Michael
D824HA Truck
Jacobs Abraham J291QR
Car
Notice that the rows pairing Michael Smythe to a car and
Abraham Jacobs to a truck have been removed.
Outer joins allow database users to include additional
information in the query results. We'll explore them in the next section
of this article. Read on!
Next page >
Outer Joins > Page
1, 2, 3
|
| 