|
|
 |
|
|
Join the Discussion
|
"Share your questions and tap the
knowledge of hundreds of your peers."
Mike
Chapple
|
|
|
|
|
|
Take a moment and review the database tables located on the
first page of this article.
Notice that we have a driver -- Jack Ryan -- who is located in a city where
there are no vehicles. Our vehicle managers would like this information to
be included in their query results to ensure that drivers do not sit idly by
waiting for a vehicle to arrive. We can use outer joins to include
records from one table that have no corresponding record in the joined
table. Let's create a list of driver/vehicle pairings that includes
records for drivers with no vehicles in their city. We can use the
following query:
SELECT lastname, firstname, driver.city, tag
FROM drivers, vehicles
WHERE drivers.location = vehicles.location (+)
Notice that the outer join operator "(+)" is
included in this query. This operator is placed in the join condition next
to the table that is allowed to have NULL values. This query would produce
the following results:
lastname
firstname city
tag
-------- --------- ---- ---
Baker
Roland New
York H122JM
Smythe Michael Miami
D824HA
Smythe Michael
Miami P091YF
Jacobs Abraham Seattle J291QR
Jacobs Abraham Seattle L990MT
Ryan
Patrick Annapolis
This time our results include the stranded Patrick Ryan and
our vehicle management department can now dispatch a vehicle to pick him up.
Note that there are other possible ways to accomplish the
results seen in this article and syntax may vary
slightly from DBMS to DBMS. These
examples were designed to work with Oracle databases, so your mileage may vary.
Furthermore, as you advance in your knowledge of SQL you’ll discover
that there is often more than one way to accomplish a desired result and
oftentimes one way is just as good as another.
Case in point, it is also possible to specify a join condition in the
FROM clause rather than the WHERE clause. For
example, we used the following SELECT statement earlier in this article:
SELECT lastname, firstname, tag
FROM drivers, vehicles
WHERE drivers.location = vehicles.location
AND drivers.class = vehicles.class
The same query could be rewritten as:
SELECT lastname, firstname, tag
FROM drivers INNER JOIN vehicles ON drivers.location =
vehicles.location
WHERE drivers.class = vehicles.class
That's it for this week! Be sure to check back next week for a new
exciting article on databases. If you'd like a reminder in your Inbox,
subscribe to the About
Databases newsletter.
Next page > > Page 1, 2,
3
|
| 