SQL FULL JOIN 和 INNER JOIN 的区别

Difference between FULL JOIN & INNER JOIN

提问by Twinkle

What is the difference between a FULL JOINand an INNER JOIN?

aFULL JOIN和 an 和有INNER JOIN什么不一样？

When I do a FULL JOIN, I get 832 records and with an INNER JOIN, I get 830 records.

当我执行 a 时FULL JOIN，我得到 832 条记录，而执行 a 时INNER JOIN，我得到 830 条记录。

回答by Lasse V. Karlsen

NOTE: All of these can be found on Wikipedia: Join (SQL).

注意：所有这些都可以在维基百科上找到：Join (SQL)。

There are three types of OUTER joins:

外连接分为三种类型：

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

• 左外连接
• 右外连接
• 全外连接

The keyword OUTER is optional in all implementations that follow the standard, so FULL JOIN is the same as FULL OUTER JOIN. (I've omitted the word OUTERfrom the SQL in the rest of this answer.)

关键字 OUTER 在遵循标准的所有实现中都是可选的，因此 FULL JOIN 与 FULL OUTER JOIN 相同。（我在OUTER本答案的其余部分中省略了SQL 中的单词。）

Let's look at what each does.

让我们看看每个人都做了什么。

Consider the following two input data sets:

考虑以下两个输入数据集：

 Set "A"    Set "B"

 AA         BB
--------   --------
 Item 1     Item 3
 Item 2     Item 4
 Item 3     Item 5
 Item 4     Item 6

Notice that there are some items in A that aren't in B, and vice versa.

请注意，A 中有一些项目不在 B 中，反之亦然。

Now, if we write an SQL statement like this, using LEFT join:

现在，如果我们编写这样的 SQL 语句，使用 LEFT 连接：

SELECT * FROM A LEFT JOIN B ON AA = BB

You'll get the following result (the empty holes are actually NULLmarks):

你会得到以下结果（空洞实际上是NULL标记）：

 AA         BB
--------   --------
 Item 1
 Item 2
 Item 3     Item 3
 Item 4     Item 4

Notice that you'll get allthe rows from AA, or rather, allthe rows from the leftpart of the join clause.

请注意，您将获得AA 中的所有行，或者更确切地说，是 join 子句左侧部分的所有行。

If you switch to using a RIGHT join:

如果您切换到使用 RIGHT 连接：

SELECT * FROM A RIGHT JOIN B ON AA = BB

 AA         BB
--------   --------
 Item 3     Item 3
 Item 4     Item 4
            Item 5
            Item 6

Notice that you get all the rows from the right part of the join clause.

请注意，您从 join 子句的右侧部分获取了所有行。

However, if you want all the rows of both, you'll use a FULL join:

但是，如果您想要两者的所有行，您将使用 FULL 连接：

SELECT * FROM A FULL JOIN B ON AA = BB

 AA         BB
--------   --------
 Item 1            <-----+
 Item 2                  |
 Item 3     Item 3       |
 Item 4     Item 4       |
            Item 5       +--- empty holes are NULL's
            Item 6       |
   ^                     |
   |                     |
   +---------------------+

---

---

As suggested in a comment, let me complete the other different ways to join.

正如评论中所建议的，让我完成其他不同的加入方式。

With INNER join:

使用 INNER 连接：

SELECT * FROM A INNER JOIN B ON AA = BB

 AA         BB
--------   --------
 Item 3     Item 3
 Item 4     Item 4

With INNER join we only get the rows that actually match up, no holes because of joining.

使用 INNER 连接，我们只能得到实际匹配的行，因为连接没有空洞。

A CROSS join produces a cartesian product, by matching up every row from the first set with every row from the second set:

CROSS 连接通过将第一组中的每一行与第二组中的每一行进行匹配来产生笛卡尔积：

SELECT * FROM A CROSS JOIN B

 AA         BB
--------   --------
 Item 1     Item 3      ^
 Item 1     Item 4      +--- first item from A, repeated for all items of B
 Item 1     Item 5      |
 Item 1     Item 6      v
 Item 2     Item 3      ^
 Item 2     Item 4      +--- second item from A, repeated for all items of B
 Item 2     Item 5      |
 Item 2     Item 6      v
 Item 3     Item 3      ... and so on
 Item 3     Item 4
 Item 3     Item 5
 Item 3     Item 6
 Item 4     Item 3
 Item 4     Item 4
 Item 4     Item 5
 Item 4     Item 6

Also note that we don't specify which columns that match, since there is no matching done.

另请注意，我们没有指定匹配的列，因为没有完成匹配。

Finally, NATURAL join, in this syntax we don't specify which columns that match, but matches on column names. In our contrived example, no column names are the same, but let's say for this specific example that the column names was XX in both tables, then we would get the following result:

最后，NATURAL join，在这个语法中，我们不指定匹配哪些列，而是匹配列名。在我们人为的示例中，没有列名是相同的，但是对于这个特定示例，假设两个表中的列名都是 XX，那么我们将得到以下结果：

SELECT * FROM A NATURAL JOIN B

 +----------+------- matches on the names, and then the data
 |          |
 v          v
 XX         XX
--------   --------
 Item 3     Item 3
 Item 4     Item 4

As you can see, you get the same as a INNER join, but don't have to type out the match part of the join clause.

如您所见，您获得的结果与 INNER 连接相同，但不必键入连接子句的匹配部分。

回答by Ralf de Kleine

A FULL OUTER JOINis a union of the LEFT OUTER JOINand RIGHT OUTER JOIN.

AFULL OUTER JOIN是LEFT OUTER JOINand 的并集RIGHT OUTER JOIN。

(did that make sense?)

（这有意义吗？）

Nice visual explanation describing joins (bottom left describes full outer join):

描述连接的很好的视觉解释（左下角描述了完整的外部连接）：

Credits go to C.L. Moffatt's blogpost on codeproject

致谢 CL Moffatt 在codeproject上的博文

回答by Silvio Donnini

The difference is in the behaviour of unmatched rows.

不同之处在于不匹配行的行为。

For example, if table A has a row which doesn't have a correspondence in table B in the field onto which the join is defined, an inner join would omit the row altogether, while a full join would include the row, but with NULL values for the fields of table B. Viceversa for unmatched rows of table B.

例如，如果表 A 有一行在定义联接的字段中与表 B 中没有对应关系，则内部联接将完全省略该行，而完全联接将包括该行，但带有 NULL表 B 的字段值。反之亦然，用于表 B 的不匹配行。

回答by Alexandre N.

In a pretty simple way, the main difference is:

以一种非常简单的方式，主要区别在于：

INNER JOIN- Returns only matched rows. Therefore, unmatched rows are not included.

INNER JOIN- 只返回匹配的行。因此，不包括不匹配的行。

FULL JOIN- Returns those rows that exist in the right table and not in the left, plus the rows that exist in the left table and not in the right, beyond the inner join rows.

FULL JOIN- 返回那些存在于右表中但不在左表中的行，以及存在于左表中但不在右表中的行，超出内部联接行。

回答by tster

A full join will return rows from both tables even if there are no matching rows in the other table. A full join is like a right join and a left join at the same time. An inner join will only return rows which have at least 1 partner in the other table.

即使另一个表中没有匹配的行，完整连接也会从两个表中返回行。完全联接就像同时进行右联接和左联接。内连接将只返回在另一个表中至少有 1 个伙伴的行。

回答by Hans Olsson

That means that your tables matches quite well.

这意味着您的表格匹配得很好。

The Wiki page found hereshows a quite a good example of how they work.

此处的 Wiki 页面显示了一个很好的示例，说明了它们的工作方式。

回答by Pavel Radzivilovsky

Inner join wouldn't bring any NULLs in the join target field. If there's no matching record, the original will not be in the table.

内部联接不会在联接目标字段中带来任何 NULL。如果没有匹配的记录，则原始记录将不在表中。

回答by Ben Cawley

Consider Table A and Table B

考虑表 A 和表 B

Table A - (Key, Name)

表 A -（键、名称）

1, Data1 2, Data2 4, Data4 5, Data5

1、数据 1 2、数据 2 4、数据 4 5、数据 5

Table B - (Key, FKey, Name)

表 B -（键、F键、名称）

1, 1, DataA 2, 2, DataB 3, NULL, DataC 4, 4, DataD

1, 1, DataA 2, 2, DataB 3, NULL, DataC 4, 4, DataD

An inner join would return

内部联接将返回

• 1, Data1, 1, 1, DataA
• 2, Data2, 2, 2, DataB
• 4, Data3, 4, 4, DataD

• 1, 数据1, 1, 1, 数据A
• 2, 数据2, 2, 2, 数据B
• 4、数据3、4、4、数据D

whilst a full outer join would return

而一个完整的外连接会返回

• 1, Data1, 1, 1, DataA
• 2, Data2, 2, 2, DataB
• NULL, NULL, 3, NULL, DataC
• 4, Data4, 4, 4, DataD
• 5, Data5, NULL, NULL, NULL

• 1, 数据1, 1, 1, 数据A
• 2, 数据2, 2, 2, 数据B
• NULL, NULL, 3, NULL, DataC
• 4、数据4、4、4、数据D
• 5、数据5、NULL、NULL、NULL