INNER JOIN vs LEFT JOIN, that is the question. Today, we’ll briefly explain how both of these two join types are used and what is the difference. We’ll go through this topic again later when we’ll expand our model and be able to write much more complex queries.

Changes in the data

Before we compare INNER JOIN vs LEFT JOIN, let’s see what we currently know. So far, in this series, we’ve explained database basics – how to create database and tables, how to populate tables with data and check what’s stored in them using simple queries. We’ve even joined two tables in the previous article. Now we’re ready for the next step.

But before we move to it, let’s make just one minor change to our data. We’ll add 2 rows in the country table, using the following INSERT INTO commands:

Now we’ll check the contents of both tables:

You can easily notice that we have 2 new rows in the table country, one for Spain and one for Russia. Their ids are 6 and 7. Also notice, that in the city table there is no country_id with value 6 or 7. This simply means that we don’t have a city from Russia or Spain in our database. We’ll use this fact later.

INNER JOIN

Let’s discuss these two queries:

The result they return is presented on the picture below:

Both queries return exactly the same result. This is not by accident but the result of the fact that this is the same query written in two different ways. Both ways are correct, and you can use any of them.

In the first query, we listed all tables we use in the FROM part of the query (FROM country, city) and then went with the join condition in the WHERE part of the query (WHERE city.country_id = country.id). In case we forgot to write down this join condition, we would have the Cartesian product of both tables.

In the second query, we have only one table in the FROM part of the query (FROM country) and then we have the second table and the JOIN condition in the JOIN part of the query (INNER JOIN city ON city.country_id = country.id).

While both queries are well-written, I would suggest that you always use INNER JOIN instead of listing tables and joining them in the WHERE part of the query. There are a few reasons for that:

• Readability is much better because the table used and related JOIN condition are in the same line. You can easily see if you omitted the JOIN condition or not
• If you want to use other JOINs later (LEFT or RIGHT), you couldn’t do that (easily) unless you’ve used INNER JOIN before that

Now, let’s comment on what queries actually returned:

• All pairs of countries and cities that are related (via foreign key)
• We don’t have 2 countries in the list (Spain and Russia), because they don’t have any related city in the city table

LEFT JOIN

I’ll repeat this – “We don’t have 2 countries on the list (Spain and Russia) because they don’t have any related city in the city table“. This shall prove crucial when comparing INNER JOIN vs LEFT JOIN.

In some cases, we want to have even these records in our results. For example, you simply want to see in the result that these countries don’t have related records in another table. This could be part of some control, or maybe just counting cases, etc. No matter what the motivation behind that desire is, we should be technically able to do that. And we are. In databases, LEFT JOIN does exactly that.

The result of LEFT JOIN shall be the same as the result of INNER JOIN + we’ll have rows, from the “left” table, without a pair in the “right” table. We’ll use the same INNER JOIN query and just replace the word INNER with LEFT. This is the result:

You can easily notice, that we have 2 more rows, compared to the result of the INNER JOIN query. These are rows for Russia and Spain. Since they both don’t have any related city, all city attributes in these two rows have NULL values (are not defined). That is the biggest difference when comparing INNER JOIN vs LEFT JOIN.

RIGHT JOIN

You’ll at least hear about the RIGHT JOIN. It’s rarely used because it returns the same result as the LEFT JOIN. On the other hand, queries which use LEFT JOIN are much easier to read because we simply list tables one after the other.

This is the equivalent of the previous query using the RIGHT JOIN:

You can notice that returned values are the same, only in this case values from the city table are in the first 5 columns, and country-related values come after them.

INNER JOIN vs LEFT JOIN

INNER JOIN vs LEFT JOIN? Actually, that is not the question at all. You’ll use INNER JOIN when you want to return only records having pair on both sides, and you’ll use LEFT JOIN when you need all records from the “left” table, no matter if they have pair in the “right” table or not. If you’ll need all records from both tables, no matter if they have pair, you’ll need to use CROSS JOIN (or simulate it using LEFT JOINs and UNION). More about that in the upcoming articles.

Table of contents

• Author
• Recent Posts

Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer.

His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...

You can find him on LinkedIn

View all posts by Emil Drkusic

Latest posts by Emil Drkusic (see all)

• Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
• Learn SQL: Dynamic SQL - March 3, 2021
• Learn SQL: SQL Injection - November 2, 2020