Bobby Iliev

Posted on Nov 11, 2021 • Originally published at devdojo.com

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

#database #sql #100daysofcode #webdev

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 qeuvealent, let's start by quickly mentioning what an INNER JOIN is.

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:

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:

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 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.

Top comments (6)

daviti • Nov 12 '21 • Edited

One interviewer asked about full outer join, I've never heard anything like that before so I couldn't answer. When I get home and googled it turned out that its just a outer join. 'full outer join' = 'outer join' like a 'inner join' = 'join'

hidden_dude • Nov 13 '21 • Edited

Hmm.. your statement is a bit ambiguous.

A FULL JOIN is like a LEFT OUTER JOIN and a RIGHT OUTER JOIN together. What it means is that you can get nulls on the left and the right.

The scenario in which I found it necessary to use this feature was when I had a report that had several lists next to it like:

customer, unfinished tasks, accomplishments, opportunities

For each of these columns you can have zero or more items. But in the report you'd want them to look like lists like so:

customer1   1. task1.       1. acc1.     1. opp1
                        2.task2.                         2. opp2
                                                                3. opp3

(hmm.. excuse formatting problems.. my intent was that the lists appear side by side)

Using row number and full join would allow you to write this report.

So if by outer join you mean LEFT and RIGHT outer join I agree with you. But there's a difference between LEFT, RIGHT, and FULL. And INNER is different as the article states.