INNER JOIN vs LEFT JOIN? Actually, that is not the question at all. You can notice that returned values are the same, only in this case values from the city table are in the first 5Ĭolumns, and country-related values come after them. This is the equivalent of the previous query using the RIGHT JOIN: The other hand, queries which use LEFT JOIN are much easier to read because we simply list tables one after the It’s rarely used because it returns the same result as the LEFT JOIN. You’ll at least hear about the RIGHT JOIN. That is the biggest difference when comparing INNER JOIN vs LEFT JOIN. Since they both don’t have any related city, all city attributes in these two rows have NULL You can easily notice, that we have 2 more rows, compared to the result of the INNER JOIN query. We’ll use the same INNER JOIN query and just replace the word INNER with LEFT. The result of LEFT JOIN shall be the same as the result of INNER JOIN + we’ll have rows, from the “left” table, In databases, LEFT JOIN does exactly that. No matter what the motivation behind that desire is, we should be technically able to do that.Īnd we are.

This could be part of some control, or maybe just counting cases, etc. For example, you simply want to see in the result that these countries don’t have related records in another table. In some cases, we want to have even these records in our results. This shall prove crucial when comparing INNER JOIN vs 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“. We don’t have 2 countries in the list (Spain and Russia), because they don’t have any related city in the Now, let’s comment on what queries actually returned:Īll pairs of countries and cities that are related (via foreign key) If you want to use other JOINs later (LEFT or RIGHT), you couldn’t do that (easily) unless you’ve used INNER You can easily see if you omitted the JOIN condition or not Readability is much better because the table used and related JOIN condition are in the same line. 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. 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 untry_id = country.id). In case we forgot to write down this join condition, we would have the Cartesian product of both tables. 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 untry_id = country.id).

Both ways are correct, and you can use any of them. This is not by accident but the result of the fact that this is the same query written in two different ways. The result they return is presented on the picture below:īoth queries return exactly the same result.

Sqlite inner join vs left join