A Guide to Using Inner Joins in SQL to Group Data From Multiple Tables

Use SQL Inner Joins to combine data from two or more tables

Closeup of a pencil and a database diagram

slungu/Getty Images

Relational databases are a stable of many businesses. They are created with a computer language called Structured Query Language (SQL). If you work with relational databases, you'll occasionally examine or gather data that is located in more than one table of the database.

What is an SQL JOIN Statement?

An SQL JOIN statement makes it possible to join two or more tables, usually based on a related column, so that the data is treated as though it is located in one table. The tables themselves are not altered by the join.

SQL JOIN is flexible and functional. Although there are several types of joins, the inner join is one of the easiest to understand and use. Take a look at the following SQL statements that illustrate how to combine results from three different tables using an inner join.

Inner Join Example

For example, take tables that contain drivers in one table and vehicle matchups in the second. The inner join occurs where both the vehicle and driver are located in the same city. The inner join selects all the rows from both tables that contain a match between location columns.

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:

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

This query produces the following results:

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

Now, extend this example to include a third table. To include only drivers and vehicles present at locations that are open on the weekend, bring a third table into the query by extending the JOIN statement as follows:

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 query produces the following results:

lastname firstname tag open_weekends
-------- --------- --- -------------
Baker Roland H122JM yes
Jacobs Abraham J291QR yes
Jacobs Abraham L990MY yes

This powerful extension to the basic SQL JOIN statement combines data in a complex manner. In addition to combining tables with an inner join, this technique combines multiple tables with other types of joins.

Other Types of Joins

When the tables have a matching record, inner joins are the way to go, but sometimes one table does not have a related record for the data the join is built on, so the query fails. This case calls for an outer join, which includes results that exist in one table but do not have a corresponding match in the joined table.

Additionally, you may chose to use a different type of join, depending on the circumstances. These other types of joins are:

  • Left outer join (left join): Contains every record from the left table even if the right table does not have a matching record.
  • Right outer join (right join): Returns all the relevant information from the right table even if the left table does not have a match.
  • Full join: Selects all the records from two tables whether or not they have a matching join condition.