An Illustrated Guide to the SQL INNER JOIN

What is an SQL INNER JOIN, and how does it work? Let's find out!

In my last article, I discussed the CROSS JOIN operation in SQL. Today, we'll look at INNER JOIN and how to use it. Is it the same as a JOIN? How many tables can you link with an INNER JOIN?

These are all good questions. Let's look at the answers!

What is an INNER JOIN?

INNER JOIN combines data from multiple tables by joining them based on a matching record. This kind of join requires a joining condition, which we will explain in a moment.

To illustrate how INNER JOIN works, we will use some simple tables. Two of them, color and shoes are shown below:

color

shoes

The color table stores an ID number and a name for each color. The shoes table stores an ID number for each pair of shoes, the shoe size, and an ID number that refers to a color in the color table.

How INNER JOIN Works

In this example, INNER JOIN joins records from the shoes table with records from the color table through the color_id column from the shoes table. The values in this column are the same as those in the id column of the color table, so it produces the intended result set. Have a look at the following query:

SELECT *  FROM  shoes
INNER JOIN color  ON color.id = shoes.color_id;

The SELECT statement takes all records from the table listed after the FROM clause – in this case, the shoes table. Then there is an INNER JOIN with the name of the table we want to match records with (i.e. the color table). The ON predicate states the matching condition, which records from both tables must have. Here the condition is that the id field from the color table and the color_id field from the shoes table must have matching values. If a record doesn't have a match, it will be left out of the results.

Take a look at the result set from this query:

Now we can see the color of each pair of shoes, thanks to the INNER JOIN. But notice that the shoes record with NULL in the color_id column is not shown: it has not matched any of the records in the color table.

JOIN or INNER JOIN?

You can omit the INNER part of INNER JOIN; JOIN works the same way. Look at the statement below.

SELECT *  FROM  shoes
JOIN color  ON color.id = shoes.color_id;

Joining with WHERE

You can also use a WHERE clause like an INNER JOIN. (This is an older version of SQL syntax (ANSI-89); the JOIN commands use ANSI-92.) Here is how using a WHERE works:

SELECT *  FROM  shoes, color  
WHERE color.id = shoes.color_id ;

The result is the same:

Using an INNER JOIN on Multiple Tables

INNER JOIN can be used on more than two tables. Look at the three tables below:

color

material

shoes

Let's use an INNER JOIN to combine information in all three tables. The query below retrieves records for all shoes that have color and material information:

SELECT *  FROM  shoes
JOIN color  ON color.id = shoes.color_id
JOIN material  ON material.id = shoes.material_id ;

Notice that only shoes with non-NULL records in the color_id and material_id columns are shown in the result set.

INNER JOIN vs. CROSS JOIN

As we have seen, INNER JOIN combines information from two or more records that have matching fields. What happens when you use CROSS JOIN to join the shoes and color tables? CROSS JOIN doesn't take any ON conditions, which means it returns a Cartesian product. Have a look at the query and result set shown below:

SELECT *  FROM  shoes
CROSS JOIN  color ;

Result?

All records from the shoes table have been joined with all records from the color table. This creates some errors. For example, some shoes aren't listed with the correct color: size-eight shoes are only available in green (their color_id value is 2), but in this join instance they are matched with other colors. The NULL records in the shoes table are matched with colors as well, even though they do not have a comparable value in the color_id field.

Learn More About INNER JOINs

INNER JOINs are quite common in SQL. Our goal in this article was to introduce the concepts behind INNER JOIN, but there is a lot more to learn. Why not check out LearnSQL's SQL Basics course to find out more?