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

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

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)