What is the difference between JOIN and INNER JOIN in SQL?

Created November 11, 2021

Introduction

If you've ever used SQL, you probably know that JOINs can be very confusing. In this quick post we are going to learn what the difference between JOIN and INNER JOIN is!

Difference between JOIN and INNER JOIN

Actually, INNER JOIN AND JOIN are functionally equivalent.

You can think of this as:

INNER JOIN == JOIN

What you need to remember is that INNER JOIN is the default if you don't specify the type when you use the word JOIN.

However you need to keep in mind that INNER JOIN can be a bit clearer to read. Especially in cases that you have a query containing other join types.

Also, keep in mind that some database management system like Microsoft Access doesn't allow just join. It requires you to specify INNER as the join type.

What is an INNER JOIN

Once we know that the functionality is equivalent, let's start by quickly mentioning what an INNER JOIN is.

INNER JOIN

The INNER join is used to join two tables. However, unlike the CROSS join, by convention, it is based on a condition. By using an INNER join, you can match the first table to the second one.

Checkout our latest product - the ultimate tailwindcss page creator 🚀

As we have a one-to-many relationship, a best practice would be to use a primary key for the posts id column and a foreign key for the user_id; that way, we can 'link' or relate the users table to the posts table. However, this is beyond the scope of this SQL basics eBook, though I might extend it in the future and add more chapters.

As an example and to make things a bit clearer, let's say that you wanted to get all of your users and the posts associated with each user. The query that we would use will look like this:

SELECT *
FROM users
INNER JOIN posts
ON users.id = posts.user_id;

Rundown of the query:

The output will be the following, associating each user with their post based on the user_id column:

+----+----------+----+---------+-----------------+
| id | username | id | user_id | title           |
+----+----------+----+---------+-----------------+
|  1 | bobby    |  1 |       1 | Hello World!    |
|  2 | devdojo  |  2 |       2 | Getting started |
|  3 | tony     |  3 |       3 | SQL is awesome  |
|  2 | devdojo  |  4 |       2 | MySQL is up!    |
|  1 | bobby    |  5 |       1 | SQL             |
+----+----------+----+---------+-----------------+

Note that the INNER JOIN could (in MySQL) equivalently be written merely as JOIN, but that can vary for other SQL dialects:

SELECT *
FROM users
JOIN posts
ON users.id = posts.user_id;

The main things that you need to keep in mind here are the INNER JOIN and ON clauses.

With the inner join, the NULL values are discarded. For example, if you have a user who does not have a post associated with it, the user with NULL posts will not be displayed when running the above INNER join query.

To get the null values as well, you would need to use an outer join.

Conclusion

This is pretty much it! Now you know what the difference between a JOIN and an INNER JOIN is!

In case that you are just getting started with SQL, I would suggest making sure to check out this free eBook here:

💡 Introduction to SQL eBook

In case that you are already using SQL on daily basis, and are looking for a way to drastically reduce the latency of your data analytics, make sure to out Materialize!

Materialize - a streaming database

Materialize is a Streaming Database for Real-time Analytics. Materialize is a reactive database that delivers incremental view updates and it helps developers easily build with streaming data using standard SQL.