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.
内部联接vs左联接，这就是问题所在。 今天，我们将简要说明这两种联接类型是如何使用的，并且有什么区别。 我们稍后将扩展模型并能够编写更复杂的查询时，将再次讨论该主题。
数据变化 (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.
在比较INNER JOIN和LEFT JOIN之前，让我们先了解一下我们目前所知道的。 到目前为止，在本系列中，我们已经解释了数据库基础知识–如何创建数据库和表，如何用数据填充表以及使用简单查询来检查存储在表中的内容。 在上一篇文章中，我们甚至加入了两个表。 现在，我们准备好进行下一步了。
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命令在国家/地区表中添加2行：
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.
您可以很容易地注意到，我们在表格国家/地区中新增了2行，其中一列用于西班牙，一列用于俄罗斯。 它们的ID是6和7。还请注意，在城市表中没有值6或7的country_id 。这仅表示我们的数据库中没有来自俄罗斯或西班牙的城市。 稍后我们将使用这个事实。
内部联接 (INNER JOIN)
Let’s discuss these two queries:
让我们讨论以下两个查询：
SELECT *
FROM country, city
WHERE city.country_id = country.id;
SELECT *
FROM country
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.
在第一个查询中，我们列出了查询的FROM部分（FROM国家/地区，城市）中使用的所有表，然后在查询的WHERE部分中使用了连接条件（WHERE city.country_id = country.id）。 如果我们忘记写下这个连接条件，我们将得到两个表的笛卡尔积。
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).
在第二个查询中，查询的FROM部分中只有一个表（FROM国家），然后在查询的JOIN部分中具有第二个表和JOIN条件（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:
虽然两个查询都写得很好，但我建议您始终使用INNER JOIN而不是列出表并将它们联接到查询的WHERE部分。 有以下几个原因：
- 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 可读性要好得多，因为使用的表和相关的JOIN条件在同一行中。 您可以轻松查看是否省略了JOIN条件
- 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 如果您以后想使用其他JOIN（向左或向右），除非您之前使用过INNER JOIN，否则无法（轻松）进行操作
Now, let’s comment on what queries actually returned:
现在，让我们评论一下实际返回的查询：
- All pairs of countries and cities that are related (via foreign key) 相关的所有国家和城市对（通过外键）
- 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.
我将重复一遍–“我们在清单中没有2个国家（西班牙和俄罗斯），因为它们在城市表中没有任何相关的城市 ”。 在比较INNER JOIN和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.
在某些情况下，我们甚至希望在结果中包含这些记录。 例如，您只想在结果中看到这些国家在另一个表中没有相关记录。 这可能是某些控制的一部分，或者可能只是对案件进行计数，等等。无论这种愿望背后的动机是什么，我们在技术上都应该能够做到这一点。 我们是。 在数据库中，LEFT JOIN正是这样做的。
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:
LEFT JOIN的结果应与INNER JOIN的结果相同+我们将在“左”表中有行，而在“右”表中没有对。 我们将使用相同的INNER JOIN查询，只是将单词INNER替换为LEFT。 结果如下：
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.
您可以很容易地注意到，与INNER JOIN查询的结果相比，我们还有2行。 这些是俄罗斯和西班牙的行。 由于它们都没有任何相关的城市，因此这两行中的所有城市属性都具有NULL值（未定义）。 比较INNER JOIN和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.
您至少会听到有关“正确加入”的信息。 很少使用它，因为它返回与LEFT JOIN相同的结果。 另一方面，使用LEFT JOIN的查询更容易阅读，因为我们只是一个接一个地列出表。
This is the equivalent of the previous query using the RIGHT JOIN:
这等效于使用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.
您会注意到返回的值是相同的，仅在这种情况下，城市表中的值位于前5列中，而与国家/地区相关的值紧随其后。
内联接vs左联接 (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.
内联接vs左联接？ 实际上，这根本不是问题。 当您只想返回两边都成对的记录时，将使用INNER JOIN；当您需要“左”表中的所有记录时，无论它们在“右”表中是否具有对，您都将使用LEFT JOIN。或不。 如果您需要两个表中的所有记录，无论它们是否有对，都将需要使用CROSS JOIN（或使用LEFT JOIN和UNION对其进行模拟）。 有关更多信息，请参见后续文章。
目录 (Table of contents)
翻译自: https://www.sqlshack.com/learn-sql-inner-join-vs-left-join/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
