SQL Join简介和概述

A SQL Join clause is put within a Select statement and at the end, it’s given a join condition, which tells the database how to fetch your data. The column specified within the join condition must be preceded by a table name if the column name is the same in both tables. When a column is preceded with a table name, it’s known as a qualified column.

将SQL Join子句放在Select语句中，最后给它一个联接条件，该条件告诉数据库如何获取数据。 如果两个表中的列名相同，则在连接条件内指定的列必须在表名之前。 当列前面带有表名时，称为合格列。

In other words, joining is the process of taking data from multiple tables and putting it into one generated result set. This article will be pretty basic and hopefully easy to follow because the goal here is to fully grasp the idea of SQL joins.

换句话说，联接是从多个表中获取数据并将其放入一个生成的结果集中的过程。 本文将是非常基础的，并且希望易于阅读，因为这里的目标是完全掌握SQL连接的思想。

Joins are a complex subject, and some find them confusing and tricky. Because of that, let’s see the concept of retrieving data from multiple tables rather than just diving into all different types of joins. Some might not even know what a join is or maybe you’ve been writing queries involving a single table. But now you need information from more than one table. Either way, you’re in the right place. So, thank you for joining us (see what we did there) and let’s begin.

联接是一个复杂的主题，有些人感到困惑和棘手。 因此，让我们看一下从多个表中检索数据的概念，而不仅仅是研究所有不同类型的联接。 有些人甚至可能不知道联接是什么，或者您可能一直在编写涉及单个表的查询。 但是现在您需要来自多个表的信息。 无论哪种方式，您都来对地方了。 因此，感谢您加入我们（看看我们在那里做了什么），让我们开始吧。

Data can be structured in a database in a confusing no user-friendly way and we basically use SQL joins to present it in the opposite. Now, this is done in a way, so we can store our data in a database that is the best structured to where we protect our data integrity and it’s normalized, separated across tables with the proper relationships, data types, keys, indexes, etc. When we actually want to output that data, e.g. on a web page, or within an application, or to another person we need the results to be organized and structured, so it makes sense to us. That’s a purpose of SQL Joins, it takes a mess and puts it out in a way that looks beautiful and this can be done over multiple tables.

数据可以以一种没有用户友好的混乱方式构造在数据库中，我们基本上使用SQL连接来相反地呈现它。 现在，这是通过某种方式完成的，因此我们可以将数据存储在结构最佳的数据库中，以保护我们的数据完整性，并对其进行规范化，并通过适当的关系，数据类型，键，索引等在各个表之间进行分隔当我们实际上想要输出数据时，例如在网页上，在应用程序内或在另一个人内，我们需要对结果进行组织和结构化，因此对我们来说很有意义。 这是SQL Join的目的，它会弄乱并以看起来漂亮的方式将其发布，并且可以在多个表上完成。

• free SQL Server Management Studio and Visual Studio add-in.免费SQL Server Management Studio和Visual Studio加载项 。

For example, let’s say that we have three different tables with relationships between them:

例如，假设我们有三个不同的表，它们之间具有关系：

We don’t need any specifics of what kind of tables, data we are dealing with but basically, the SQL Join will take all of this (Table1, Table2, Table3) and it’s going to present us with a generated table that is more pretty, structured, user-friendly and it makes more sense to us.

我们不需要处理哪种表，数据的任何细节，但基本上，SQL Join将接受所有这些表（Table1，Table2，Table3），它将为我们提供一个更漂亮的生成表，结构化，用户友好，这对我们来说更有意义。

Now, the way this is done is by using foreign and primary key connections. So, these tables are going to have relationships between them, as shown below, this is known as a foreign key connection that references a primary key:

现在，完成此操作的方法是使用外键和主键连接。 因此，这些表之间将具有关系，如下所示，这称为引用主键的外键连接：

In other words, one of these tables is the parent and another one is the child and we want the end result to combine them into one generated table as shown above.

换句话说，这些表中的一个是父表，另一个是子表，我们希望最终结果将它们组合成一个生成的表，如上所示。

The relationship that is used within the SQL Join condition is often already defined in the database (usually foreign key/primary key connection) but it doesn’t have to be. If it’s not already in the database, it’s known as an ad hoc relationship.

在SQL Join条件中使用的关系通常已经在数据库中定义了（通常是外键/主键连接），但不一定必须如此。 如果它不在数据库中，则称为临时关系。

You should know that when we do this, these columns that are connected are going to be indexed to make data joining faster. This is another complex subject and we highly recommend that you check out articles below associated with indexes that speed up retrieval of rows from the table or view:

您应该知道，当我们这样做时，将对这些连接的列进行索引，以使数据连接更快。 这是另一个复杂的主题，我们强烈建议您查看以下与索引相关的文章，这些索引可加快从表或视图中检索行的速度：

• How to identify and solve SQL Server index scan problems 如何识别和解决SQL Server索引扫描问题
• How to optimize SQL Server query performance – Statistics, Joins and Index Tuning 如何优化SQL Server查询性能–统计信息，联接和索引调整
• How to create and optimize SQL Server indexes for better performance 如何创建和优化SQL Server索引以获得更好的性能
• Top things you need in a SQL Server query plan analysis tool SQL Server查询计划分析工具中需要的重要事项

Moving on, when we are joining tables it’s a common practice to replace the columns names with more user-friendly names. For example, let’s say that we got a comment on a website, it says “This article is awesome!”, and it’s posted by a reader named ”Michael“:

继续，当我们连接表时，通常的做法是将列名称替换为更易于使用的名称。 例如，假设我们在一个网站上发表评论，上面写着“这篇文章太棒了！”，并由名为“ Michael”的读者发布：

This username on a website is probably a user ID in a comment table. So, let’s say we have two tables: Users table and Comments table and there’s a foreign key from the User ID referencing the User ID in the Users table:

网站上的此用户名可能是评论表中的用户ID。 因此，假设我们有两个表：Users表和Comments表，并且User ID中有一个外键引用Users表中的User ID：

Well, when we present that data on a database we will want to replace that user ID with the actual username. Otherwise, we could end up with a user that’s named ”4523“ and nobody would know who that is. Since all of this information is within the Comments table, we can just take the information from the Users table and put that username instead of the user ID.

好吧，当我们在数据库上显示该数据时，我们将要用实际的用户名替换该用户ID。 否则，我们最终可能会遇到一个名为“ 4523”的用户，而没人知道那是谁。 由于所有这些信息都在“注释”表中，因此我们只需从“用户”表中获取信息，然后输入该用户名而不是用户ID。

This is how SQL Join works conceptually. We have to think conceptually because it’s important to understand that this article should only be helping you understand the concepts of a different kind of SQL Joins. This is why we got these mockup examples instead of writing actual queries. Also because joins are done differently from a database management system to a database management system. All you need to know is how the joins work and the expected results. Then, when you start working with MySQL, SQL Server, Oracle database, etc. hopefully you’ll be able to just figure out how to do their joins. Ultimately, the most important thing when it comes to joins is to know beforehand what the results are going to be because if you understand the joins then you’ll be able to just type it out.

这就是SQL Join在概念上的工作方式。 我们必须从概念上进行思考，因为很重要的一点是，了解本文仅应有助于您理解另一种SQL连接的概念。 这就是为什么我们得到这些样机示例而不是编写实际查询的原因。 另外，因为联接的完成方式与数据库管理系统与数据库管理系统不同。 您只需要了解联接的工作方式和预期结果。 然后，当您开始使用MySQL，SQL Server，Oracle数据库等时，希望您能够弄清楚如何进行它们的联接。 最终，涉及联接时最重要的事情是事先知道结果将是什么，因为如果您了解联接，则可以将其键入。

Bear in mind that all of this is data manipulation language (DML). This is different from data definition language or data description language (DDL). These are two parts of the SQL concept and when dealing with SQL Joins, it’s important to know that we already have the definition of a database. This simply means that the database might already have been designed and structured with the column and rows but now, we’re manipulating that data to look a certain way. So, by quering data using joins you’re not actually changing the structure of the database itself. You’re just changing the presentation of that database.

请记住，所有这些都是数据操作语言（DML）。 这与数据定义语言或数据描述语言（DDL）不同。 这是SQL概念的两个部分，在处理SQL连接时，重要的是要知道我们已经有了数据库的定义。 这只是意味着数据库可能已经使用列和行进行了设计和结构化，但是现在，我们正在操纵该数据以某种方式查找。 因此，通过使用联接查询数据实际上并没有改变数据库本身的结构。 您只是在更改该数据库的表示形式。

Joining data from multiple tables depends a lot on database design. A database can be structured to have a friendly design but in reality, it doesn’t work because it’s not normalized, there’s repeating data, etc. A simple example is a table called ”Comments“:

从多个表中联接数据在很大程度上取决于数据库设计。 可以对数据库进行结构化以使其具有友好的设计，但实际上，由于未规范化，重复的数据等原因，它无法正常工作。一个简单的示例是一个名为“ Comments”的表：

Let’s say inside this table we got three comments by the username”Michael”.

假设在此表中，用户名“ Michael”得到了三个注释。

If you show this to someone who is not familiar with databases, he or she will probably say that user Michael posted a comment “This article is awesome!”. The same user posted another comment and so on. You get the point. But this isn’t the best way to store data because of data integrity and also repeating data that we mentioned above. In this artificial example, we got the same username three times. The best practice here would be to use a user ID and get rid of the “Username” and “Michael” and have something like this so we can apply the SQL Join logic:

如果向不熟悉数据库的人展示此内容，则他或她可能会说用户Michael发表了一条评论“本文很棒！”。 同一用户发布了另一条评论，依此类推。 你明白了。 但这不是存储数据的最佳方法，因为它具有数据完整性以及重复我们上面提到的数据。 在此人工示例中，我们获得了相同的用户名3次。 这里的最佳实践是使用用户ID并摆脱“用户名”和“ Michael”，并具有类似这样的内容，因此我们可以应用SQL Join逻辑：

Here we simply have a Users table with the ”UserID“ column which has all of our user IDs for each individual person. So we started off with this friendly design but in reality, it does not work because it’s not normalized, there’s repeating data, etc. The only con in this case it that it looks pretty. Once we have that, we can break it up and normalize it so that we have two tables.

在这里，我们仅是一个带有“ UserID”列的Users表，其中包含每个人的所有用户ID。 因此，我们从这种友好的设计开始，但实际上，由于它没有被标准化，重复的数据等原因，它无法正常工作。在这种情况下，唯一的缺点是看起来很漂亮。 一旦有了它，我们就可以将其分解并规范化，以便有两个表。

Next thing we got to do is to take up those pieces of the puzzle and put them back together in a SQL Join to get the final result. We basically want to replicate the original table we had before by using joins of our normalized database and recreate it. This UserID 12 in the ”Comments” table will be connected with the UserID 12 in the ”Users“ table which is assotiated with the name ”Michael”.

我们要做的下一件事是解决这些难题，并将它们放回SQL Join中以获得最终结果。 我们基本上希望通过使用规范化数据库的联接来复制之前拥有的原始表并重新创建它。 “注释”表中的该UserID 12将与“用户”表中的与用户名“ Michael”相关的UserID 12连接。

There are a couple different kinds of SQL Joins like: inner joins, outer joins, cross joins, etc. and we’d highly recommend that you check out detailed articles below that covers not only basics but also shows actual examples of data retrieval:

SQL联接有几种不同类型，例如：内部联接，外部联接，交叉联接等，我们强烈建议您阅读以下详细的文章，这些文章不仅涵盖基础知识，还显示了数据检索的实际示例：

• SQL Join clause introduction and overview SQL Join子句介绍和概述
• SQL Join overview and tutorial SQL Join概述和教程

翻译自: https://www.sqlshack.com/sql-join-introduction-and-overview/