什么是默认的T-SQL连接行为，内部的还是外部的?

This question was asked for MySQL already, but for Transact-SQL, what is the default JOIN behaviour?

这个问题已经问过MySQL了，但是对于Transact-SQL，默认的连接行为是什么?

That is, is simply writing JOIN in a query synonymous with writing INNER JOIN (as is the case with MySQL), or something else, like perhaps FULL OUTER JOIN?

也就是说，仅仅是在查询中编写连接就等同于编写内部连接(就像MySQL那样)，还是其他什么，比如完整的外部连接?

3 个解决方案

#1

JOIN defaults to INNER JOIN behaviour.

连接默认为内部连接行为。

To verify this, I ran the following code:

为了验证这一点，我运行了以下代码:

DECLARE @A TABLE (x INT)
INSERT INTO @A
    SELECT 1 UNION ALL
    SELECT 2

DECLARE @B TABLE (x INT)
INSERT INTO @B
    SELECT 2 UNION ALL
    SELECT 3

SELECT 
    A.x AS 'A.x', 
    B.x AS 'B.x'
FROM @A A
JOIN @B B
    ON A.x = B.x

This produces just one row, consistent with INNER JOIN behaviour:

这只产生一行，与内部连接行为一致:

A.x  | B.x
-----+-----
2    | 2

Contrast this with a FULL OUTER JOIN:

将此与完整的外部连接进行对比:

...

SELECT 
    A.x AS 'A.x', 
    B.x AS 'B.x'
FROM @A A
FULL OUTER JOIN @B B
    ON A.x = B.x

This of course shows all three rows:

这当然显示了这三行:

A.x  | B.x
-----+-----
1    | NULL
2    | 2
NULL | 3

#2

In T-SQL, JOIN without an explicit type is an INNER JOIN, as specified by the documentation on the FROM clause (excerpt):

在T-SQL中，没有显式类型的连接是内部连接，如FROM子句(摘录)的文档所指定:

[ FROM { <table_source> } [ ,...n ] ]   
<table_source> ::=   
{  
    ...
    | <joined_table>
    ...   
}  

<joined_table> ::=   
{  
    <table_source> <join_type> <table_source> ON <search_condition>
    ...   
}  

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

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.

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

#3

For some MSDN reference. To paraphrase, inner join is the default type of join.

对于一些MSDN参考。换句话说，内部连接是默认的连接类型。

https://msdn.microsoft.com/en-us/library/zt8wzxy4.aspx

#1