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
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,
shoes are shown below:
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
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;
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
JOIN or INNER JOIN?
You can omit the
INNER part of
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
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:
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
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
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 ;
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
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 Vertabelo Academy’s SQL Basics course to find out more?