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
INNER JOIN is!
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.
Once we know that the functionality is qeuvealent, let's start by quickly mentioning what an
INNER JOIN is.
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
idcolumn of the
usertable, which is also the primary ID, and
posts.user_idis 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
+----+----------+----+---------+-----------------+ | 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
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.
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:
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.