|Retrieving Data from Multiple Tables with SQL Joins|
|Part 2: Inner Joins (Equijoins)|
Inner joins (also known as equijoins) are used to contain information from a combination of two or more tables. The join condition determines which records are paired together and is specified in the WHERE clause. For example, let's create a list of driver/vehicle match-ups where both the vehicle and driver are located in the same city. The following SQL query will accomplish this task:
SELECT lastname, firstname, tag
And let's take a look at the results:
Notice that the results are exactly what we sought. It is possible to further refine the query by specifying additional criteria in the WHERE clause. Our vehicle managers took a look at the results of our last query and noticed that the previous query matches drivers to vehicles that they are not authorized to drive (e.g. truck drivers to cars and vice-versa). We can use the following query to resolve this problem:
SELECT lastname, firstname, tag,
Notice that in this example we needed to specify the source table for the class attribute in the SELECT clause. This is due to the fact that class is ambiguous – it appears in both tables and we need to specify which table’s column should be included in the query results. In this case it does not 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:
Notice that the rows pairing Michael Smythe to a car and
Abraham Jacobs to a truck have been removed.
Outer joins allow database users to include additional information in the query results. We'll explore them in the next section of this article. Read on!