Retrieving Data From Multiple Tables With SQL Inner Joins

Inner joins return information that appears in two or more databases

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:

Here are the results: 

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

man at computer
Peopleimages/E+/Getty Images

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:

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.