连接和内部连接的区别

Both these joins will give me the same results:

这两种结合会给我同样的结果:

SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK

vs

vs

SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK

Is there any difference between the statements in performance or otherwise?

在性能或其他方面的表述有什么不同吗?

Does it differ between different SQL implementations?

不同的SQL实现之间有区别吗?

7 个解决方案

#1

They are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.

它们在功能上是等价的，但是内部连接可以更容易阅读，特别是如果查询包含其他连接类型(比如左、右或交叉)的话。

#2

Just typing JOIN performs an INNER JOIN by default.

只需输入JOIN，默认情况下会执行内部连接。

For all others, one picture is sometimes worth more than hundreds of words:

对于所有其他的，一幅画有时价值超过几百字:

Image courtesy of Code Project.

代码项目提供的图像。

---

#3

No, there is no difference, pure syntactic sugar.

不，没有区别，纯语法糖。

#4

INNER JOIN = JOIN:

内连接=加入:

INNER JOIN is the default if you don't specify the type when you use the word JOIN.

如果您在使用单词JOIN时没有指定类型，那么内部连接是默认的。

You can also use LEFT OUTER JOIN or RIGHT OUTER JOIN, in which case the word OUTER is optional, or you can specify CROSS JOIN.

您还可以使用左外连接或右外连接，在这种情况下，单词OUTER是可选的，或者您可以指定交叉连接。

OR

或

For an inner join, the syntax is:

对于内部连接，语法是:

SELECT ...
FROM TableA
[INNER] JOIN TableB

选择……从表a[内]到表b。

(in other words, the "INNER" keyword is optional - results are the same with or without it)

(换句话说，“INNER”关键字是可选的——无论使用与否，结果都是一样的)

#5

Similarly with OUTER JOINs, the word "OUTER" is optional. It's the LEFT or RIGHT keyword that makes the JOIN an "OUTER" JOIN.

与外部连接类似，“外部”一词是可选的。左或右关键字使连接成为“外部”连接。

However for some reason I always use "OUTER" as in LEFT OUTER JOIN and never LEFT JOIN, but I never use INNER JOIN, but rather I just use "JOIN":

但是由于某些原因，我总是使用“OUTER”作为左外连接，从不离开JOIN，但我从不使用内部连接，而是使用“JOIN”:

SELECT ColA, ColB, ...
FROM MyTable AS T1
     JOIN MyOtherTable AS T2
         ON T2.ID = T1.ID
     LEFT OUTER JOIN MyOptionalTable AS T3
         ON T3.ID = T1.ID

#6

Does it differ between different SQL implementations?

不同的SQL实现之间有区别吗?

Yes, Microsoft Access doesn't allow just join. It requires inner join.

是的，微软Access不允许加入。它需要内连接。

#7

As the other answers already state there is no difference in your example.

因为其他答案已经表明，你的例子没有区别。

The relevant bit of grammar is documented here

这里记录了相关的语法位

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

Showing that all are optional. The page further clarifies that

显示所有都是可选的。这一页进一步阐明了这一点。

INNER Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. When no join type is specified, this is the default.

内部指定返回所有匹配的行。从两个表中丢弃不匹配的行。当没有指定连接类型时，这是默认值。

The grammar does also indicate that there is one time where the INNER is required though. When specifying a join hint.

语法也表明，有一次需要内部的。指定连接提示时。

See the example below

看下面的例子

CREATE TABLE T1(X INT);
CREATE TABLE T2(Y INT);

SELECT *
FROM   T1
       LOOP JOIN T2
         ON X = Y;

SELECT *
FROM   T1
       INNER LOOP JOIN T2
         ON X = Y;