1. Tech

Your suggestion is on its way!

An email with a link to:

http://databases.about.com/od/sql/l/aajoins2.htm

was emailed to:

Thanks for sharing About.com with others!

Retrieving Data from Multiple Tables with SQL Joins
Part 2: Inner Joins (Equijoins)
 More of this Feature
• Part 1: Introducing Joins
• Part 3: Outer Joins
 
 Join the Discussion
"Share your questions and  tap the knowledge of hundreds of your peers."
Mike Chapple
 
 Related Resources
• SQL Fundamentals
• Creating Databases and Tables in SQL
• Retrieving Data with SQL Queries 

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
FROM drivers, vehicles
WHERE drivers.location = vehicles.location

And let's take a look at the results: 

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

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, vehicles.class
FROM drivers, vehicles
WHERE drivers.location = vehicles.location
AND drivers.class = vehicles.class

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:

lastname     FirstName      Tag         Class
--------     ---------      ---         -----
Baker        Roland         H122JM      Car
Smythe       Michael        D824HA      Truck
Jacobs       Abraham        J291QR      Car

Notice that the rows pairing Michael Smythe to a car and Abraham Jacobs to a truck have been removed. 

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

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!

Next page > Outer Joins > Page 1, 2, 3 


You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

See More About
Related Video
Learn About Databases
Microsoft Excel Sort Feature

©2014 About.com. All rights reserved.