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?
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 
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.
INNER JOIN when that's the behaviour you want may be a nice idea if someone else needs to review your T-SQL script and perhaps doesn't remember this bit of the documentation.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.
For some MSDN reference. To paraphrase, inner join is the default type of join.
