What is the difference between JOIN and INNER JOIN in SQL?
Tags: database, sql, 100daysofcode, webdev
Published: 2021-11-11 18:52:38

# Introduction 

If you've ever used SQL, you probably know that `JOIN`s 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 qeuvealent, let's start by quickly mentioning what an `INNER JOIN` is. 

![INNER JOIN](https://imgur.com/np7HEeX.png)

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.

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:

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

Rundown of the query:

* `SELECT * FROM users`: This is a standard select we've covered many times in the previous chapters.
* `INNER JOIN posts`: Then, we specify the second table and which table we want to join the result set.
* `ON users.id = posts.user_id`: Finally, we specify how we want the data in these two tables to be merged. The `user.id` is the `id` column of the `user` table, which is also the primary ID, and `posts.user_id` is the foreign key in the email address table referring to the ID column in the users table.

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:

```sql
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](https://github.com/bobbyiliev/introduction-to-sql)

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](https://materialize.com/)!

![Materialize - a streaming database](https://imgur.com/52d9a6h.png)

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.