More of this Feature • Part 1: Introducing Joins • Part 2: Inner Joins

Join the Discussion "Share your questions and  tap the knowledge of hundreds of your peers." Mike Chapple

Related Resources • SQL Fundamentals • Creating Databases and Tables in SQL • Retrieving Data with SQL Queries

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