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:
This query produced the following results:
SELECT lastname, firstname, tag FROM drivers, vehicles WHERE drivers.location = vehicles.location
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:
lastname firstname tag -------- --------- --- Baker Roland H122JM Smythe Michael D824HA Smythe Michael P091YF Jacobs Abraham J291QR Jacobs Abraham L990MT
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'
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.
lastname firstname tag open_weekends -------- --------- --- ------------- Baker Roland H122JM yes Jacobs Abraham J291QR yes Jacobs Abraham L990MT yes