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:
INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('España', 'Spain', 'ESP');
INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Rossiya', 'Russia', 'RUS');
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.
Let’s discuss these two queries:
FROM country, city
WHERE city.country_id = country.id;
INNER JOIN city ON city.country_id = country.id;
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
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.
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.