Michael Currie Michael Currie - 6 months ago 15
SQL Question

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

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

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