sql 左联接 全联接

If you want to get something meaningful out of data, you’ll almost always need to join multiple tables. In this article, we’ll show how to do that using different types of joins. To achieve that, we’ll combine INNER JOINs and LEFT JOINs. So, let’s start.

如果要从数据中获取有意义的信息，几乎总是需要连接多个表。 在本文中，我们将展示如何使用不同类型的联接来做到这一点。 为此，我们将结合内部联接和左联接。 所以，让我们开始吧。

该模型 (The Model)

In the picture below you can see out existing model. It consists of 6 tables and we’ve already, more or less, described it in the previous articles.

在下面的图片中，您可以看到现有模型。 它由6个表组成，我们已经或多或少地在前面的文章中对其进行了描述。

Still, even without describing, if the database is modeled and presented in a good manner (choosing names wisely, using naming convention, following the same rules throughout the whole model, lines/relations in schema do not overlap more than needed), you should be able to conclude where you can find the data you need. This is crucial because before you join multiple tables, you need to identify these tables first.

尽管如此，即使没有描述，如果数据库是以良好的方式建模和表示的（使用命名约定，在整个模型中遵循相同的规则明智地选择名称，架构中的行/关系不会重叠超过所需的数量），您应该能够得出可以在哪里找到所需数据的结论。 这很关键，因为在连接多个表之前，您需要首先标识这些表。

We’ll talk about naming convention and the advice on how to think when you’re writing SQL queries, later in this series. So far, let’s live with the fact that this model is pretty simple and we can do it fairly easily.

在本系列的后面，我们将讨论命名约定以及在编写SQL查询时如何思考的建议。 到目前为止，让我们忍受这个模型非常简单的事实，并且我们可以很容易地做到这一点。

到目前为止我们知道什么？ (What do we know so far?)

In this series, we’ve covered:

在本系列中，我们介绍了：

• , and 有关的基础知识，以及

We’ll use the knowledge from both these articles and combine these to write more complex SELECT statements that will join multiple tables.

我们将利用这两篇文章中的知识，并将它们结合起来以编写将连接多个表的更复杂的SELECT语句。

使用INNER JOIN联接多个表 (Join multiple tables using INNER JOIN)

The first example we’ll analyze is how to retrieve data from multiple tables using only INNER JOINs. For each example, we’ll go with the definition of the problem we must solve and the query that does the job. So, let’s start with the first problem.

我们将分析的第一个示例是如何仅使用INNER JOIN从多个表中检索数据。 对于每个示例，我们将定义必须解决的问题以及执行此任务的查询。 因此，让我们从第一个问题开始。

#1 We need to list all calls with their start time and end time. For each call, we want to display what was the outcome as well the first and the last name of the employee who made that call. We’ll sort our calls by start time ascending.

＃1我们需要列出所有呼叫及其开始时间和结束时间。 对于每个电话，我们要显示结果以及发出该电话的员工的名字和姓氏。 我们将按开始时间升序对通话进行排序。

Before we write the query, we’ll identify the tables we need to use. To do that, we need to determine which tables contain the data we need and include them. Also, we should include all tables along the way between these tables – tables that don’t contain data needed but serve as a relation between tables that do (that is not the case here).

在编写查询之前，我们将确定需要使用的表。 为此，我们需要确定哪些表包含我们需要的数据并将其包括在内。 另外，我们应该在这些表之间包括所有表-这些表不包含所需数据，但可以作为有关系的表之间的关系（此处不是这种情况）。

The query that does the job is given below:

下面给出了执行该工作的查询：

SELECT employee.first_name, employee.last_name, call.start_time, call.end_time, call_outcome.outcome_textFROM employeeINNER JOIN call ON call.employee_id = employee.idINNER JOIN call_outcome ON call.call_outcome_id = call_outcome.idORDER BY call.start_time ASC;

The query result is given below:

查询结果如下：

There are a few things I would like to point out here:

我想在这里指出几点：

• The tables we’ve joined are here because the data we need is located in these 3 tables
  我们联接的表在这里，因为我们需要的数据位于这3个表中
• employee.first_name). While that’s not needed, it’s a good practice, because sometimes 2 or more tables in the same query could use the same attribute names and that would lead to an error employee.first_name ）。 尽管这不是必需的，但这是一个好习惯，因为有时同一查询中的2个或更多表可能使用相同的属性名称，这将导致错误
• We’ve used INNER JOIN 2 times in order to join 3 tables. This will result in returning only rows having pairs in another table
  我们已使用INNER JOIN 2次以联接3个表。 这将导致仅返回另一个表中具有对的行
• When you’re using only INNER JOINs to join multiple tables, the order of these tables in joins is not important. The only important thing is that you use appropriate join conditions after the “ON” (join using foreign keys)
  当仅使用INNER JOIN联接多个表时，联接中这些表的顺序并不重要。 唯一重要的是在“ ON”（使用外键进行连接）之后使用适当的连接条件

Since all calls had related employee and call outcome, we would get the same result if we’ve used LEFT JOIN instead of the INNER JOIN.

由于所有呼叫都与员工和呼叫结果相关，因此，如果使用LEFT JOIN而不是INNER JOIN，我们将得到相同的结果。

使用LEFT JOIN联接多个表 (Join multiple tables using LEFT JOIN )

Writing queries that use LEFT JOINs doesn’t differ a lot when compared to writing queries using INNER JOINs. The result would, of course, be different (at least in cases when some records don’t have a pair in other tables).

与使用INNER JOIN编写查询相比，使用LEFT JOIN编写查询没有太大区别。 结果当然会有所不同（至少在某些记录在其他表中没有对的情况下）。

This is the problem we want to solve.

这是我们要解决的问题。

#2 List all counties and customers related to these countries. For each country display its name in English, the name of the city customer is located in as well as the name of that customer. Return even countries without related cities and customers.

＃2列出与这些国家有关的所有县和客户。 对于每个国家/地区，用英语显示其名称，都会找到城市客户的名称以及该客户的名称。 返回没有相关城市和客户的国家。

The tables containing data we need are in the picture below:

包含我们所需数据的表如下图：

First, let’s quickly check what is the contents of these 3 tables.

首先，让我们快速检查这3个表的内容。

We can notice two important things:

我们可以注意到两个重要的事情：

• city has a related 城市都有一个相关country, not all countries have related cities (Spain & Russia don’t have them) 国家/地区 ，但并非所有国家/地区都具有相关城市（西班牙和俄罗斯没有）
• customer has the 客户都有定义的city_id value defined, but only 3 cities are being used (Berlin, Zagreb & New York) city_id值，但仅使用了3个城市（柏林，萨格勒布和纽约）

Let’s first write down the query using INNER JOIN:

我们首先使用INNER JOIN写下查询：

SELECT country.country_name_eng, city.city_name, customer.customer_nameFROM countryINNER JOIN city ON city.country_id = country.idINNER JOIN customer ON customer.city_id = city.id;

The query result is shown in the picture below:

查询结果如下图所示：

We have 7 counties and 6 cities in our database, but our query returns only 4 rows. That is the result of the fact we have only 4 customers in our database. Each of these 4 is related to its city and the city is related to the country. So, INNER JOIN eliminated all these countries and cities without customers. But how to include these in the result too?

我们的数据库中有7个县和6个城市，但是我们的查询仅返回4行。 这是因为我们的数据库中只有4个客户。 这4个都与城市有关，而城市与国家有关。 因此，INNER JOIN消除了所有没有客户的国家和城市。 但是如何将这些也包括在结果中呢？

To do that, we’ll use LEFT JOIN. We’ll simply replace all “INNER” with “LEFT” so our query is as follows:

为此，我们将使用LEFT JOIN。 我们将所有“ INNER”替换为“ LEFT”，因此我们的查询如下：

SELECT country.country_name_eng, city.city_name, customer.customer_nameFROM countryLEFT JOIN city ON city.country_id = country.idLEFT JOIN customer ON customer.city_id = city.id;

The result is shown in the picture below:

结果如下图所示：

You can easily notice that now we have all the countries, even those without any related city (Russia & Spain), as well all cities, even those without customers (Warsaw, Belgrade & Los Angeles). The remaining 4 rows are the same as in the query using INNER JOIN.

您可以很容易地注意到，现在我们拥有所有国家，甚至没有任何相关城市的国家（俄罗斯和西班牙），以及所有城市，甚至没有客户的城市（华沙，贝尔格莱德和洛杉矶）。 其余4行与使用INNER JOIN进行的查询相同。

左联接–表顺序很重要 (LEFT JOIN – Tables order matters)

While the order of JOINs in INNER JOIN isn’t important, the same doesn’t stand for the LEFT JOIN. When we use LEFT JOIN in order to join multiple tables, it’s important to remember that this join will include all rows from the table on the LEFT side of the JOIN. Let’s rearrange the previous query:

尽管INNER JOIN中JOIN的顺序并不重要，但LEFT JOIN并不相同。 当我们使用LEFT JOIN来联接多个表时，重要的是要记住，此联接将包括JOIN的LEFT端的表中的所有行。 让我们重新排列上一个查询：

SELECT country.country_name_eng, city.city_name, customer.customer_nameFROM customerLEFT JOIN city ON customer.city_id = city.idLEFT JOIN country ON city.country_id = country.id;

At first, you could easily say, that this query and the previous one are the same (this is true when using INNER JOIN). We’ve used the same tables, LEFT JOINs, and the same join conditions. Let’s take a look at the output first:

首先，您可以轻松地说出，该查询与上一个查询是相同的（使用INNER JOIN时确实如此）。 我们使用了相同的表，LEFT JOIN和相同的联接条件。 让我们先看一下输出：

So, what happened here? Why do we have 4 rows (same 4 we had when we’ve used INNER JOIN)?

那么，这里发生了什么？ 为什么我们有4行（使用INNER JOIN时有4行）？

The answer is simple and it’s related to how LEFT JOIN works. It takes the first table (customer) and joins all its rows (4 of them) to the next table (city). The result of this is 4 rows because the customer could belong to only 1 city. Then we join these 4 rows to the next table (country), and again we have 4 rows because the city could belong to only 1 country.

答案很简单，并且与LEFT JOIN的工作方式有关。 它获取第一个表（ 客户 ），并将其所有行（其中4行）连接到下一个表（ city ）。 结果是4行，因为客户可能只属于1个城市。 然后，我们将这4行连接到下一个表（ 国家/地区 ），又又有了4行，因为城市只能属于一个国家/地区。

The reason why we wouldn’t join these 3 tables in this way is given by the text of the example #2. The query is written in such manner it returns 4 rows would be the answer to the following: Return names of all customers as well as cities and countries they are located in. Return even customers without related cities and countries.

示例2的文字说明了为什么我们不以这种方式联接这3个表。 查询以这种方式编写，它返回4行将是以下内容的答案：返回所有客户以及他们所在的城市和国家的名称。甚至返回没有相关城市和国家的客户。

• Note: When you’re using LEFT JOIN, the order of tables in that statement is important and the query will return a different result if you change this order. The order actually depends on what you want to return as a result.注意：使用LEFT JOIN时，该语句中表的顺序很重要，如果更改此顺序，查询将返回不同的结果。 订单实际上取决于您要返回的结果。

同时使用多个表联接– INNER JOIN和LEFT JOIN (Join multiple tables using both – INNER JOIN & LEFT JOIN)

This is also possible. Let’s again go with an example.

这也是可能的。 让我们再来看一个例子。

#3 Return the list of all countries and cities that have pair (exclude countries which are not referenced by any city). For such pairs return all customers. Return even pairs not having a single customer.

＃3返回具有配对的所有国家和城市的列表（不包括任何城市未引用的国家）。 对于这样的对，返回所有客户。 甚至返回没有一个客户的货币对。

The query that does the job is:

执行该工作的查询是：

SELECT country.country_name_eng, city.city_name, customer.customer_nameFROM countryINNER JOIN city ON city.country_id = country.idLEFT JOIN customer ON customer.city_id = city.id; 

The result of the query is given in the picture below:

查询结果如下图所示：

You can easily notice that we don’t have countries without any related city (these were Spain & Russia). The INNER JOIN eliminated these rows. Still, we do have cites without any customers (Belgrade, Los Angeles & Warsaw). This is the result of the fact we used LEFT JOIN between tables city and customer.

您可以很容易地注意到，我们没有没有任何相关城市的国家（这些国家是西班牙和俄罗斯）。 INNER JOIN消除了这些行。 不过，我们确实有没有任何客户的举动（贝尔格莱德，洛杉矶和华沙）。 这是由于我们在table city和customer之间使用了LEFT JOIN的结果。

结论 (Conclusion)

When you need to join multiple tables, you have INNER & LEFT JOIN on your disposal (RIGHT JOIN is rarely used and can be easily replaced by LEFT JOIN). Which join you’ll use depends directly on the task you need to solve and you’ll get the feeling along the way. In upcoming articles, we’ll discuss how to think and organize yourself when you need to write more complex queries.

当需要连接多个表时，可以使用INNER＆LEFT JOIN（很少使用RIGHT JOIN，并且可以用LEFT JOIN轻松替换）。 您将使用哪种联接直接取决于您需要解决的任务，您会逐渐感觉到。 在接下来的文章中，我们将讨论当您需要编写更复杂的查询时如何思考和组织自己。

目录 (Table of contents)

Learn SQL: Join multiple tables

学习SQL：联接多个表

翻译自:

sql 左联接 全联接