sql 左联接 全联接_学习SQL：联接多个表

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:

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

• SQL SELECT statement, and SQL SELECT语句有关的基础知识，以及
• INNER JOIN and LEFT JOIN INNER JOIN和LEFT JOIN

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_text
FROM employee
INNER JOIN call ON call.employee_id = employee.id
INNER JOIN