外加入

左外連線

LEFT JOIN 返回左表中的所有行，與右表中符合 ON 子句條件的行匹配。不滿足 ON 子句的行在所有右表的列中都有 NULL。LEFT JOIN 的語法是：

SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column 

正確的外部加入

RIGHT JOIN 返回右表中的所有行，與左表中符合 ON 子句條件的行匹配。不滿足 ON 子句的行在所有左表的列中都有 NULL。RIGHT JOIN 的語法是：

SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column 

完全外部加入

FULL JOIN 結合了 LEFT JOIN 和 RIGHT JOIN。無論是否滿足 ON 子句中的條件，都會從兩個表返回所有行。不滿足 ON 子句的行在所有相對錶的列中返回 NULL（即，對於左表中的行，右表中的所有列將包含 NULL，反之亦然）。FULL JOIN 的語法是：

SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column  

例子

/* Sample test data. */
DECLARE @Animal table (
    AnimalId Int IDENTITY,
    Animal Varchar(20)
);

DECLARE @AnimalSound table (
    AnimalSoundId Int IDENTITY,
    AnimalId Int,
    Sound Varchar(20)
);

INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');
INSERT INTO @Animal (Animal) VALUES ('Frog');

INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpet');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (5, 'Roars');
/* Sample data prepared. */

LEFT OUTER JOIN

SELECT * 
FROM @Animal As t1 
LEFT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

結果為 LEFT JOIN

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  2             2           Meows
3           Elephant             3             3           Trumpet
4           Frog                 NULL          NULL        NULL

正確加入

SELECT * 
FROM @Animal As t1 
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

結果為 RIGHT JOIN

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  2             2           Meows
3           Elephant             3             3           Trumpet
NULL        NULL                 4             5           Roars

完全外部加入

SELECT * 
FROM @Animal As t1 
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

結果為 FULL JOIN

AnimalId    Animal               AnimalSoundId AnimalId    Sound
----------- -------------------- ------------- ----------- --------------------
1           Dog                  1             1           Barks
2           Cat                  2             2           Meows
3           Elephant             3             3           Trumpet
4           Frog                 NULL          NULL        NULL
NULL        NULL                 4             5           Roars