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
id | name |
---|---|
1 | yellow |
2 | green |
3 | pink |
shoes
id | size | color_id |
---|---|---|
1 | seven | 2 |
2 | eight | 2 |
3 | nine | 2 |
4 | seven | 1 |
5 | nine | 1 |
6 | seven | 3 |
7 | ten | NULL |
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:
id | size | color_id | id | name |
---|---|---|---|---|
1 | seven | 2 | 2 | green |
2 | eight | 2 | 2 | green |
3 | nine | 2 | 2 | green |
4 | seven | 1 | 1 | yellow |
5 | nine | 1 | 1 | yellow |
6 | seven | 3 | 3 | pink |
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:
id | size | color_id | id | name |
---|---|---|---|---|
1 | seven | 2 | 2 | green |
2 | eight | 2 | 2 | green |
3 | nine | 2 | 2 | green |
4 | seven | 1 | 1 | yellow |
5 | nine | 1 | 1 | yellow |
6 | seven | 3 | 3 | pink |
Using an INNER JOIN on Multiple Tables
INNER JOIN
can be used on more than two tables. Look at the three tables below:
color
id | name |
---|---|
1 | yellow |
2 | green |
3 | pink |
material
id | name |
---|---|
1 | leather |
2 | cotton |
3 | NULL |
shoes
id | size | color_id | material_id |
---|---|---|---|
1 | seven | 2 | 1 |
4 | seven | 1 | 2 |
5 | nine | 1 | 1 |
6 | seven | 3 | NULL |
7 | ten | NULL | 1 |
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.
id | size | color_id | material_id | id | name | id | name |
---|---|---|---|---|---|---|---|
1 | seven | 2 | 1 | 2 | green | 1 | leather |
4 | seven | 1 | 2 | 1 | yellow | 2 | cotton |
5 | nine | 1 | 1 | 1 | yellow | 1 | leather |
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?
id | size | color_id | id | name |
---|---|---|---|---|
1 | seven | 2 | 1 | yellow |
2 | eight | 2 | 1 | yellow |
3 | nine | 2 | 1 | yellow |
4 | seven | 1 | 1 | yellow |
5 | nine | 1 | 1 | yellow |
6 | seven | 3 | 1 | yellow |
7 | ten | NULL | 1 | yellow |
1 | seven | 2 | 2 | green |
2 | eight | 2 | 2 | green |
3 | nine | 2 | 2 | green |
4 | seven | 1 | 2 | green |
5 | nine | 1 | 2 | green |
6 | seven | 3 | 2 | green |
7 | ten | NULL | 2 | green |
1 | seven | 2 | 3 | pink |
2 | eight | 2 | 3 | pink |
3 | nine | 2 | 3 | pink |
4 | seven | 1 | 3 | pink |
5 | nine | 1 | 3 | pink |
6 | seven | 3 | 3 | pink |
7 | ten | NULL | 3 | pink |
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 JOIN
s 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?