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:

 SELECT lastname, firstname, tag
 FROM drivers, vehicles
 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
 

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:

 SELECT lastname, firstname, tag, open_weekends
 FROM drivers, vehicles, locations
 WHERE drivers.location = vehicles.location
 AND vehicles.location = locations.location
 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.