47

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

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?

Community
  • 1
  • 1
Michael Currie
  • 13,721
  • 9
  • 42
  • 58
  • 8
    For those of you voting down this perfectly legitimate question because I answered it myself, please note that it is now encouraged to [answer your own questions if you can](http://meta.stackexchange.com/questions/17845/etiquette-for-answering-your-own-question). – Michael Currie May 06 '14 at 16:59
  • 1
    possible duplicate of [Difference between JOIN and INNER JOIN](http://stackoverflow.com/questions/565620/difference-between-join-and-inner-join) – Matt Gibson May 06 '14 at 17:07
  • 4
    I didn't downvote, but I suspect people aren't downvoting because you answered your own question, but because the question seems to have been asked before. The answer is also pretty easy to find in Microsoft's documentation (e.g. ["When no join type is specified, this is the default."](http://technet.microsoft.com/en-us/library/ms177634.aspx) in the documentation for FROM) which can lead to downvotes, even though [it perhaps shouldn't](http://meta.stackexchange.com/questions/5280/embrace-the-non-googlers). – Matt Gibson May 06 '14 at 17:11

3 Answers3

41

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
Michael Currie
  • 13,721
  • 9
  • 42
  • 58
  • 10
    In general, "trying it out" should not be used as a valid approach to conclude particular behavior is guaranteed or "the default" -- there are numerous `SET` options that can influence T-SQL behavior, for example, even though this happens to not be the case for `JOIN`. Your code is fine to illustrate the behavior, but is not authoritative. This is why I felt the need to post another answer, obvious as the question may be. – Jeroen Mostert Jun 06 '16 at 14:35
22

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

[ 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.

Jeroen Mostert
  • 27,176
  • 2
  • 52
  • 85
4

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

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

daviesdoesit
  • 805
  • 9
  • 14