Retrieving Data From Multiple Tables With SQL Inner Joins

Inner joins return information that appears in two or more databases

man at computer
Peopleimages/E+/Getty Images

Inner joins are the most frequently used joins in SQL. They return only information that exists in two or more database tables. The join condition determines which records are paired together and is specified in the WHERE clause. For example, if you need a list of driver/vehicle matchups in which both the vehicle and driver are located in the same city, the following SQL query accomplishes this task:

SELECT lastname, firstname, tag
FROM drivers, vehicles
WHERE drivers.location = vehicles.location

Here are the results: 

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

Note that the results are exactly what was sought.  It is possible to further refine the query by specifying additional criteria in the WHERE clause. Assume the original query matches drivers to vehicles that they are not authorized to drive (truck drivers to cars and vice versa). You 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

This example specifies the source table for the class attribute in the SELECT clause because the class is ambiguous—it appears in both tables.

The code would usually specify which table’s column should be included in the query results. In this case, it doesn't 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

The missing rows paired Michael Smythe to a car and Abraham Jacobs to a truck, vehicles they were not authorized to drive. 

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