Wednesday, July 8, 2009

Joining Multiple Tables with SQL Inner Join Statements

You can use SQL JOIN statements to combine data from three or more tables. In an earlier article, we took a look at using inner joins and outer joins to combine data from two different tables. In many cases, you’ll want to take this a step further and combine data from three or more tables. Let's take a look at the SQL statements that allow you to accomplish this goal for an inner join.
You may recall from our basic inner join example that the SQL statement below combines data from the Drivers and Vehicles tables in cases where the driver and vehicle are located in the same city:

   1:  SELECT lastname, firstname, tag
   2:  FROM drivers, vehicles
   3:  WHERE drivers.location = vehicles.location

This query produced the following results:

lastname firstname tag
-------- --------- ---
Baker Roland H122JM
Smythe Michael D824HA
Smythe Michael P091YF
Jacobs Abraham J291QR
Jacobs Abraham L990MT

Ads by Nielsen Field

Now, let’s extend this example to include a third table. Imagine that you wanted to include only drivers and vehicles present at locations that are open on the weekend. You could bring a third table into your query by extending the JOIN statement as follows:

   1:  SELECT lastname, firstname, tag, open_weekends
   2:  FROM drivers, vehicles, locations
   3:  WHERE drivers.location = vehicles.location
   4:  AND vehicles.location = locations.location
   5:  AND locations.open_weekends = 'Yes'


lastname firstname tag open_weekends
-------- --------- --- -------------
Baker Roland H122JM yes
Jacobs Abraham J291QR yes
Jacobs Abraham L990MT yes


This powerful extension to the basic SQL JOIN statement allows you to combine data in a complex manner. In addition to combining tables with an inner join, you can also use this technique to combine multiple tables using an outer join. As you may recall, outer joins include results that exist in one table but do not have a corresponding match in the joined table.

Suggested Reading

Introducing Joins

Inner Joins

Outer Joins


Original post can be found here