More of this Feature • Part 1: Introducing Joins • Part 3: Outer 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

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 ambiguous – 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. 

You can also use inner joins to combine data from three or more tables.

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