左连接与左外连接在SQL Server中。

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

左连接和左外部连接的区别是什么?

16 个解决方案

#1

As per the documentation: FROM (Transact-SQL):

根据文件:(Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets), and what this means in this case is that whether you specify it or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will of course make a difference.

外层关键字被标记为可选(括在方括号中)，在这种情况下，这意味着无论您指定与否都没有区别。注意，虽然join子句的其他元素也被标记为可选的，但是把它们去掉当然会有所不同。

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

例如，JOIN子句的整个类型部分是可选的，在这种情况下，如果您只是指定JOIN，那么默认值就是内部的。换句话说，这是合法的:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here's a list of equivalent syntaxes:

这里有一个等价语法的列表:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.

还可以看看我在另一个问题上留下的答案:SQL left join vs multitables on FROM line?

#2

To answer your question there is no difference between LEFT JOIN and LEFT OUTER JOIN, they are exactly same that said...

要回答你的问题左连接和左外连接没有区别，它们是完全相同的。

At the top level there are mainly 3 types of joins:

1. INNER
2. 内心的
3. OUTER
4. 外
5. CROSS
6. 交叉

---

1. INNER JOIN - fetches data if present in both the tables.

   内部连接——如果在两个表中都存在，则获取数据。

2. OUTER JOIN are of 3 types:

   外部连接有三种类型:

   1. LEFT OUTER JOIN - fetches data if present in the left table.
   2. 左外连接——如果在左表中，则获取数据。
   3. RIGHT OUTER JOIN - fetches data if present in the right table.
   4. 右外连接——如果在右表中，则获取数据。
   5. FULL OUTER JOIN - fetches data if present in either of the two tables.
   6. 完整的外部连接——如果在两个表中的任何一个中，则获取数据。

3. CROSS JOIN, as the name suggests, does [n X m] that joins everything to everything.
   Similar to scenario where we simply lists the tables for joining (in the FROM clause of the SELECT statement), using commas to separate them.

   交叉连接，顾名思义，就是把所有的东西连接到所有的东西上。类似于我们只列出要连接的表(在SELECT语句的FROM子句中)的场景，使用逗号分隔它们。

---

Points to be noted:

注意点:

• If you just mention JOIN then by default it is a INNER JOIN.
• 如果您只是提到了JOIN，那么默认情况下它是一个内部连接。
• An OUTER join has to be LEFT | RIGHT | FULL you can not simply say OUTER JOIN.
• 外部连接必须是|右| FULL，不能简单地说外部连接。
• You can drop OUTER keyword and just say LEFT JOIN or RIGHT JOIN or FULL JOIN.
• 你可以去掉OUTER关键字，写上左连接或右连接或全连接。

---

For those who want to visualise these in a better way, please go to this link: A Visual Explanation of SQL Joins

如果您希望以更好的方式来可视化这些内容，请访问这个链接:SQL连接的可视化解释

#3

What is the difference between left join and left outer join?

左连接和左外部连接的区别是什么?

Nothing. LEFT JOIN and LEFT OUTER JOIN are equivalent.

什么都没有。左连接和左外连接是等效的。

#4

Read more at Visual Representation of SQL Joins

更多地阅读SQL连接的可视化表示。

#5

I'm a PostgreSQL DBA, as far as I could understand the difference between outer or not outer joins difference is a topic that has considerable discussion all around the internet. Until today I never saw a difference between those two; So I went further and I try to find the difference between those. At the end I read the whole documentation about it and I found the answer for this,

我是一名PostgreSQL DBA，就我所能理解的外部连接差异之间的区别而言，这是一个在互联网上有很多讨论的话题。直到今天我才发现这两者之间的区别;我进一步尝试找出它们之间的区别。最后我读了关于它的整个文档我找到了答案，

So if you look on documentation (at least in PostgreSQL) you can find this phrase:

所以如果你看一下文档(至少在PostgreSQL中)，你会发现这句话:

"The words INNER and OUTER are optional in all forms. INNER is the default; LEFT, RIGHT, and FULL imply an outer join."

“任何形式的内部和外部都是可选的。”内心是默认;左、右、满表示外部连接。

In another words,

换句话说,

LEFT JOIN and LEFT OUTER JOIN ARE THE SAME

左连接和左外部连接是相同的

RIGHT JOIN and RIGHT OUTER JOIN ARE THE SAME

右连接和右外连接是一样的

I hope it can be a contribute for those who are still trying to find the answer.

我希望它能对那些仍在寻找答案的人有所帮助。

#6

Nothing to say in words beside this:

除了这句话，没有什么可说的了:

#7

I find it easier to think of Joins in the following order:

我发现按照下面的顺序来考虑连接更容易:

• CROSS JOIN - a Cartesian product of both tables. ALL joins begin here
• 交叉连接——两个表的笛卡尔积。所有连接在这里开始
• INNER JOIN - a CROSS JOIN with a filter added.
• 内连接——与添加过滤器的交叉连接。
• OUTER JOIN - an INNER JOIN with missing elements (from either LEFT or RIGHT table) added afterward.
• 外部连接-一个内部连接缺少元素(来自左或右表)之后添加。

Until I figured out this (relatively) simple model, JOINS were always a bit more of a black art. Now they make perfect sense.

在我发现这个(相对)简单的模型之前，连接总是有点像一种黑色的艺术。现在他们说得很有道理。

Hope this helps more than it confuses.

希望这能带来更多的帮助而不是混淆。

#8

Left Join and Left Outer Join are one and the same. The former is the shorthand for the latter. The same can be said about the Right Join and Right Outer Join relationship. The demonstration will illustrate the equality. Working examples of each query have been provided via SQL Fiddle. This tool will allow for hands on manipulation of the query.

左连接和左外部连接是相同的。前者是后者的简写。对于右连接和右外部连接关系也是如此。这次*将说明平等。每个查询的工作示例都是通过SQL Fiddle提供的。该工具将允许手动操作查询。

Given

鉴于

Left Join and Left Outer Join

左连接和左外连接。

Results

结果

---

Right Join and Right Outer Join

右连接和右外连接

Results

结果

#9

Why are LEFT/RIGHT and LEFT OUTER/RIGHT OUTER the same? Let's explain why this vocabulary. Understand that LEFT and RIGHT joins are specific cases of the OUTER join, and therefore couldn't be anything else than OUTER LEFT/OUTER RIGHT. The OUTER join is also called FULL OUTER as opposed to LEFT and RIGHT joins that are PARTIAL results of the OUTER join. Indeed:

为什么左/右和左外/右外是一样的?让我们来解释一下为什么这个词汇。要知道左连接和右连接是外部连接的特定情况，因此不可能是除了外部左/外部右之外的任何东西。外部连接也称为完整外部连接，而不是作为外部连接的部分结果的左和右连接。事实上:

Table A | Table B     Table A | Table B      Table A | Table B      Table A | Table B
   1    |   5            1    |   1             1    |   1             1    |   1
   2    |   1            2    |   2             2    |   2             2    |   2
   3    |   6            3    |  null           3    |  null           -    |   -
   4    |   2            4    |  null           4    |  null           -    |   -
                        null  |   5             -    |   -            null  |   5
                        null  |   6             -    |   -            null  |   6

                      OUTER JOIN (FULL)     LEFT OUTER (partial)   RIGHT OUTER (partial)

It is now clear why those operations have aliases, as well as it is clear only 3 cases exist: INNER, OUTER, CROSS. With two sub-cases for the OUTER. The vocabulary, the way teachers explain this, as well as some answers above, often make it looks like there are lots of different types of join. But it's actually very simple.

现在很清楚为什么这些操作有别名，而且很清楚只有三种情况存在:内部、外部、交叉。外部有两个子案例。这些词汇，老师们解释这些的方式，以及上面的一些答案，常常让人觉得有很多不同类型的连接。其实很简单。

#10

There are mainly three types of JOIN

连接主要有三种类型

1. Inner: fetches data, that are present in both tables
   • Only JOIN means INNER JOIN
   • 只有连接意味着内部连接。
2. Inner:获取数据，在两个表中都存在的数据，连接表示内部连接

3. Outer: are of three types

   外层:有三种类型

   • LEFT OUTER - - fetches data present only in left table & matching condition
   • 左外——只在左表和匹配条件下获取数据
   • RIGHT OUTER - - fetches data present only in right table & matching condition
   • 右外-取数据只存在于正确的表和匹配条件。
   • FULL OUTER - - fetches data present any or both table
   • 完整的外部-获取数据显示任何一个或两个表
   • (LEFT or RIGHT or FULL) OUTER JOIN can be written w/o writing "OUTER"
   • (左或右或满)外连接可以写成w/o

4. Cross Join: joins everything to everything

   交叉连接:连接到所有的东西

#11

Syntactic sugar, makes it more obvious to the casual reader that the join isn't an inner one.

句法上的甜头，让那些漫不经心的读者更清楚地认识到，连接不是一个内在的连接。

#12

To answer your question

回答你的问题

In Sql Server joins syntax OUTER is optional

在Sql Server中，join语法是可选的

It is mentioned in msdn article : https://msdn.microsoft.com/en-us/library/ms177634(v=sql.130).aspx

msdn文章中提到:https://msdn.microsoft.com/en-us/library/ms177634(v=sql.130).aspx

So following list shows join equivalent syntaxes with and without OUTER

所以下面的列表显示了连接等价的语法

LEFT OUTER JOIN => LEFT JOIN
RIGT OUTER JOIN => RIGHT JOIN
FULL OUTER JOIN => FULL JOIN

Other equivalent syntaxes

其他相同的语法

INNER JOIN => JOIN
CROSS JOIN => ,

Strongly Recommend Dotnet Mob Artice : Joins in Sql Server

强烈推荐Dotnet Mob Artice:连接到Sql Server。

#13

There are only 3 joins:

• A) Cross Join = Cartesian (E.g: Table A, Table B)
• A) Cross Join = Cartesian (E)g:表A，表B)
• B) Inner Join = JOIN (E.g: Table A Join/Inner Join Table B)
• B)内连接=连接(E)g:表A连接/内连接表B)

• C) Outer join:

  C)外连接:

     There are three type of outer join
     1)  Left Outer Join     = Left Join
     2)  Right Outer Join    = Right Join
     3)  Full Outer Join     = Full Join    
  

Hope it'd help.

希望它会有帮助。

#14

LEFT JOIN performs a join starting with the first (left-most) table and then any matching second (right-most) table records.

左连接执行一个连接，从第一个(最左)表开始，然后是任何匹配的第二个(最右)表记录。

LEFT JOIN and LEFT OUTER JOIN are the same.

左连接和左外部连接是相同的。

According to DoFactory

根据DoFactory

#15

In ,both the left join and left outer join performing the same operations.

在，左连接和左外部连接执行相同的操作。

Basically the left join provides all the records from the table 1 and only matching records from table 2.

基本上，左连接提供表1中的所有记录，并且只提供表2中的匹配记录。

It does not provides the unmatched records from the table 2.

它不提供表2中未匹配的记录。

#16

I know that this is old however wanted to put my two cents in. I understand that the LEFT JOIN should be the same as LEFT OUTER JOIN but in my experience I have seen a LEFT JOIN pull back different Results than a LEFT OUTER JOIN so I have started to use the key word OUTER to be more specific and proper. Rows that should have come back in a LEFT JOIN did not where as when I would use a LEFT OUTER JOIN it did. I was trying to explain this to a colleague when he was unable to get the rows that he needed as well so I decided to Google the difference so as to have some sort of backing to show him. This might be a SQL Server specific thing to which I am uncertain about. I would say that in good practice it would be more advisable to explicitly state that you want an outer join to occur. Just my opinion.

我知道这是旧的，但想把我的两分钱放进去。我知道左连接应该与左外连接相同，但是根据我的经验，我看到左连接拉回的结果与左外连接不同，所以我开始使用关键词OUTER来更加具体和恰当。应该在左连接中返回的行不像我使用左外连接时那样。我正试着向一个同事解释这个问题，当时他也无法得到他需要的行，所以我决定用谷歌来解释这个差异，以便有一些支持来给他看。这可能是一个SQL服务器特定的东西，我对此不确定。我想说的是，在良好的实践中，更明智的做法是明确声明你希望外部连接出现。只是我的意见。