What is the default T-SQL JOIN behaviour, INNER or OUTER?

Question

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?

Answer 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