sql2008外部连接_SQL连接–内部，外部，左右连接

妖狐艹你老母 2022-12-08 04:44 126阅读 0赞

![7906477cec4ddeb275b1a1f12e72cd69.png][]

sql2008外部连接

Here you will get detailed information about joins in SQL.

在这里，您将获得有关SQL中联接的详细信息。

Join is a very basic concept in SQL which can be confusing at times. The joins are used when we need to find out the solution to a query involving the attributes of more than one table which have at least one attribute in common. Hence the need of join is pretty much clear in itself. There are different types of join which are used for different purposes.

联接是SQL中一个非常基本的概念，有时可能会造成混淆。 当我们需要找出涉及多个表的属性(至少具有一个共同的属性)的查询的解决方案时，可以使用联接。 因此，加入的需要本身就很清楚了。 有不同类型的联接用于不同目的。

## 加入SQL **(**Joins in SQL**)** ##

Let’s say we have two tables, a table named STUDENT and the other table named TEACHER.

假设我们有两个表，一个名为STUDENT的表，另一个名为TEACHER的表。

The first table “STUDENT” stores the reference details of the student and the second table “TEACHER” stores the details of the teachers who are in the school and the class they are teaching.

第一个表“ STUDENT”存储了学生的参考详细信息，第二个表“ TEACHER”存储了在校教师和所教课程的详细信息。

**STUDENT**

**学生**

<table> 
 <tbody> 
  <tr> 
   <td><strong>ClassID</strong></td> 
   <td><strong>Name</strong></td> 
   <td><strong>House address</strong></td> 
  </tr> 
  <tr> 
   <td>101</td> 
   <td>Raj</td> 
   <td>xyz</td> 
  </tr> 
  <tr> 
   <td>201</td> 
   <td>Varun</td> 
   <td>byc</td> 
  </tr> 
  <tr> 
   <td>301</td> 
   <td>Chittiz</td> 
   <td>abc</td> 
  </tr> 
  <tr> 
   <td>401</td> 
   <td>Hema</td> 
   <td>def</td> 
  </tr> 
 </tbody> 
</table>

**TEACHER**

**老师**

<table> 
 <tbody> 
  <tr> 
   <td><strong>Teacher id</strong></td> 
   <td><strong>Class name</strong></td> 
   <td><strong>ClassID</strong></td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>First</td> 
   <td>101</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Second</td> 
   <td>201</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Third</td> 
   <td>301</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Fourth</td> 
   <td>401</td> 
  </tr> 
 </tbody> 
</table>

In the second table, the “ClassID” is a foreign key which is used as a reference of the first table in the second one.

在第二个表中，“ ClassID”是一个外键，用作第二个表中第一个表的引用。

Now, if we want to find out the name of the student whose teacher id is 1; we need to find the join of the above mentioned tables since it requires us to gather the information of both the tables. Hence, the joins are only used where both the tables have at least one attribute (here ClassID) in common and we need to find the solution to a query which involves the attributes from both the tables.

现在，如果我们想找出老师ID为1的学生的姓名， 我们需要查找上述表的联接，因为这需要我们收集两个表的信息。 因此，仅在两个表具有至少一个共同的属性(此处为ClassID)的情况下使用联接，并且我们需要找到涉及两个表的属性的查询的解决方案。

### 加盟类型 **(**Types of Join**)** ###

Basically there are four types of joins namely, inner join, left join, right join, full outer join. The explanation of each one of the mentioned joins is as given below.

基本上有四种类型的联接，即内部联接，左联接，右联接，完全外联接。 所提及的每个连接的说明如下。

![Joins in SQL - Inner, Outer, Left and Right Join][Joins in SQL - Inner_ Outer_ Left and Right Join]

#### 1.内部加入 **(**1. Inner Join**)** ####

Let us consider the following two tables, the first table’s name is Country (saves the id of different countries) and the other table’s name is State (saves the various states in those countries).

让我们考虑以下两个表，第一个表的名称为Country(保存不同国家的ID)，另一个表的名称为State(保存这些国家中的各个州)。

**COUNTRY**

**国家**

<table> 
 <tbody> 
  <tr> 
   <td><strong>CountryId</strong></td> 
   <td><strong>CountryName</strong></td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>China</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>India</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>USA</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <strong>国家/地区编号</strong> </td> 
   <td> <strong>国家的名字</strong> </td> 
  </tr> 
  <tr> 
   <td> 1个 </td> 
   <td> 中国 </td> 
  </tr> 
  <tr> 
   <td> 2 </td> 
   <td> 印度 </td> 
  </tr> 
  <tr> 
   <td> 3 </td> 
   <td> 美国 </td> 
  </tr> 
 </tbody> 
</table>

**STATE**

**州**

<table> 
 <tbody> 
  <tr> 
   <td><strong>StateId</strong></td> 
   <td><strong>CountryId</strong></td> 
   <td><strong>StateName</strong></td> 
  </tr> 
  <tr> 
   <td>01</td> 
   <td>2</td> 
   <td>GOA</td> 
  </tr> 
  <tr> 
   <td>02</td> 
   <td>2</td> 
   <td>RAJASTHAN</td> 
  </tr> 
  <tr> 
   <td>03</td> 
   <td>5</td> 
   <td>KATHMANDU</td> 
  </tr> 
  <tr> 
   <td>04</td> 
   <td>6</td> 
   <td>ISLAMABAD</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <strong>StateId</strong> </td> 
   <td> <strong>国家/地区编号</strong> </td> 
   <td> <strong>州名</strong> </td> 
  </tr> 
  <tr> 
   <td> 01 </td> 
   <td> 2 </td> 
   <td> 果阿 </td> 
  </tr> 
  <tr> 
   <td> 02 </td> 
   <td> 2 </td> 
   <td> 拉贾斯坦邦 </td> 
  </tr> 
  <tr> 
   <td> 03 </td> 
   <td> 5 </td> 
   <td> 加德满都 </td> 
  </tr> 
  <tr> 
   <td> 04 </td> 
   <td> 6 </td> 
   <td> 伊斯兰堡 </td> 
  </tr> 
 </tbody> 
</table>

***select \* from COUNTRY***

***从国家/地区中选择\****

***inner join STATE***

***内部连接状态***

***on COUNTRY.CountryId=STATE.CountryId***

***在COUNTRY.CountryId = STATE.CountryId上***

The above mentioned command applies an inner join on the two table, since the common attribute is Country id, we have applied the join on the same.

上面提到的命令在两个表上应用了内部联接，因为common属性是Country id，所以我们在同一表上应用了联接。

The inner join returns all the matching values from both the tables. Here, in table State, since the only matching CountryId value in the country table is \{CountryId = 2\}, as a result of the inner join we will get the following result:

内部联接返回两个表中的所有匹配值。 在这里，在表State中，由于国家/地区表中唯一匹配的CountryId值为\{CountryId = 2\}，作为内部联接的结果，我们将得到以下结果：

#### 2.正确加入 **(**2. Right Join**)** ####

The  right (or right outer join) on the other hand, displays the data which is common in both the tables, as well as the data which is present in the right table (exclusively).

另一方面，右(或右外部联接)显示两个表中都通用的数据以及右表中(排他)的数据。

This basically means that the entire right table’s data would be displayed on application of a right join.

基本上，这意味着整个右表的数据将在应用右联接时显示。

When there is no match in the Left Table, it displays NULL.

如果左表中没有匹配项，则显示NULL。

**Example:**

**例：**

**COUNTRY**

**国家**

**STATE**

**州**

***select \* from COUNTRY***

***从国家/地区中选择\****

***right  join STATE***

***正确加入STATE***

***on COUNTRY.CountryId=STATE.CountryId***

***在COUNTRY.CountryId = STATE.CountryId上***

The above mentioned command applies a right join on the two tables, since the common attribute is CountryId; we have applied the join on CountryId itself.

上面提到的命令在两个表上应用了右连接，因为公共属性是CountryId。 我们已将联接应用于CountryId本身。

The right table is the second table we refer to.

右表是我们引用的第二个表。

We would get the following table as a result of the application of the right join:

由于应用了正确的联接，我们将获得下表：

<table> 
 <tbody> 
  <tr> 
   <td><strong>CountryId</strong></td> 
   <td><strong>CountryName</strong></td> 
   <td><strong>StateId</strong></td> 
   <td><strong>StateName</strong></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>India</td> 
   <td>01</td> 
   <td>GOA</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>India</td> 
   <td>02</td> 
   <td>RAJASTHAN</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>NULL</td> 
   <td>03</td> 
   <td>KATHMANDU</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>NULL</td> 
   <td>04</td> 
   <td>ISLAMABAD</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <strong>国家/地区编号</strong> </td> 
   <td> <strong>国家的名字</strong> </td> 
   <td> <strong>StateId</strong> </td> 
   <td> <strong>州名</strong> </td> 
  </tr> 
  <tr> 
   <td> 2 </td> 
   <td> 印度 </td> 
   <td> 01 </td> 
   <td> 果阿 </td> 
  </tr> 
  <tr> 
   <td> 2 </td> 
   <td> 印度 </td> 
   <td> 02 </td> 
   <td> 拉贾斯坦邦 </td> 
  </tr> 
  <tr> 
   <td> 5 </td> 
   <td> 空值 </td> 
   <td> 03 </td> 
   <td> 加德满都 </td> 
  </tr> 
  <tr> 
   <td> 6 </td> 
   <td> 空值 </td> 
   <td> 04 </td> 
   <td> 伊斯兰堡 </td> 
  </tr> 
 </tbody> 
</table>

In the result, it is clearly depicted that the values in the Left Table which have absolutely no matching values in the Right one are not being displayed. Only those values of the Left Table are displayed which have common attributes with the Right one. Whereas all the values in the Right Table are displayed. The rows in the Right Table with no match in the Left Table are displayed as NULL (Empty).

结果清楚地显示出，左表中的值在右表中绝对没有匹配值没有被显示。 仅显示左表的那些值与右表具有相同的属性。 显示右表中的所有值。 右表中的行与左表中不匹配的行显示为NULL(空)。

#### 3.左联接 **(**3. Left Join**)** ####

The left join (or left outer join) on the other hand, displays the data which is common in both the tables, as well as the data which is present in the left table (exclusively).

另一方面，左联接(或左外部联接)显示两个表中都通用的数据，以及左表中(唯一)存在的数据。

This basically means that the entire Left Table’s data would be displayed on application of a Left Join.

基本上，这意味着整个左表数据将在应用左联接时显示。

When there is no match in the Left Table, it displays NULL.

如果左表中没有匹配项，则显示NULL。

**COUNTRY**

**国家**

**STATE**

**州**

***select \* from COUNTRY***

***从国家/地区中选择\****

***left join STATE***

***左加入州***

***on COUNTRY.CountryId =STATE.CountryId***

***在COUNTRY.CountryId = STATE.CountryId上***

The above mentioned command applies a Left Join on the two tables, since the common attribute is CountryId; we have applied the join on Countryid itself.

上面提到的命令在两个表上应用了左联接，因为公共属性是CountryId。 我们已将联接应用于Countryid本身。

The Left Table is the first table we refer to.

左表是我们引用的第一个表。

We would get the following table as a result on application of left join:

通过左联接的应用，我们将得到下表：

<table> 
 <tbody> 
  <tr> 
   <td><strong>CountryId</strong></td> 
   <td><strong>CountryName</strong></td> 
   <td><strong>Stateid</strong></td> 
   <td><strong>Statename</strong></td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>China</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>India</td> 
   <td>01</td> 
   <td>GOA</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>India</td> 
   <td>02</td> 
   <td>RAJASTHAN</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>USA</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <strong>国家/地区编号</strong> </td> 
   <td> <strong>国家的名字</strong> </td> 
   <td> <strong>Stateid</strong> </td> 
   <td> <strong>州名</strong> </td> 
  </tr> 
  <tr> 
   <td> 1个 </td> 
   <td> 中国 </td> 
   <td> 空值 </td> 
   <td> 空值 </td> 
  </tr> 
  <tr> 
   <td> 2 </td> 
   <td> 印度 </td> 
   <td> 01 </td> 
   <td> 果阿 </td> 
  </tr> 
  <tr> 
   <td> 2 </td> 
   <td> 印度 </td> 
   <td> 02 </td> 
   <td> 拉贾斯坦邦 </td> 
  </tr> 
  <tr> 
   <td> 3 </td> 
   <td> 美国 </td> 
   <td> 空值 </td> 
   <td> 空值 </td> 
  </tr> 
 </tbody> 
</table>

In the result, it is clearly depicted that the values in the right column which have absolutely no matching values in the left one are not being displayed. Only those attributes of the right column are displayed which have common attributes with the left one. Whereas all the values in the Left Table are displayed. The rows in the Left Table with no match in the Right Table are displayed as NULL (Empty)

结果清楚地表明，没有显示右列中绝对没有匹配值的值。 仅显示右列的属性，这些属性与左列具有相同的属性。 而将显示“左表”中的所有值。 左表中没有匹配项的行在右表中显示为NULL(空)

#### 4.完全外部联接 **(**4. Full Outer Join**)** ####

The Full Outer Join, as the name suggests, shows all the content of both the tables. The Full Outer Join returns all matching records from both the tables whether the other table matches or not.

顾名思义，完全外部联接显示了两个表的所有内容。 完全外部联接返回两个表中的所有匹配记录，而不管另一个表是否匹配。

**COUNTRY**

**国家**

**STATE**

**州**

***select \* from COUNTRY***

***从国家/地区中选择\****

***full outer join STATE***

***完全外部联接STATE***

***on COUNTRY.CountryId=TEACHER.CountryId***

***在COUNTRY.CountryId = TEACHER.CountryId上***

The above mentioned command applies a Full Outer Join on the two tables, since the common attribute is CountryId; we have applied the join on Countryid itself.

上面提到的命令在两个表上应用了完全外部联接，因为公共属性是CountryId。 我们已将联接应用于Countryid本身。

We would get the following table as a result on application of Full Outer Join:

通过使用完全外部联接，我们将得到下表：

This Join results in all the rows. We get NULL (Empty), when there is no match.

此联接导致所有行。 当没有匹配项时，我们得到NULL(空)。

Joins are essentially important to work with tables in SQL and the above mentioned description genuinely elaborate the usage of each one of them.

联接对于使用SQL中的表至关重要，上面的描述确实详细说明了每个联接的用法。

Comment down below if you have any queries related to SQL joins.

如果您有任何与SQL连接相关的查询，请在下面注释。

> 翻译自: [https://www.thecrazyprogrammer.com/2019/05/joins-in-sql.html][https_www.thecrazyprogrammer.com_2019_05_joins-in-sql.html]

sql2008外部连接

[7906477cec4ddeb275b1a1f12e72cd69.png]: /images/20221123/486d37ce133f41c1a4c743cddc0502fc.png
[Joins in SQL - Inner_ Outer_ Left and Right Join]: /images/20221123/1eb5d6969faf43338adfbfcb6d22c820.png
[https_www.thecrazyprogrammer.com_2019_05_joins-in-sql.html]: https://www.thecrazyprogrammer.com/2019/05/joins-in-sql.html