Left Outer Join
LEFT JOIN
returns all rows from the left table, matched to rows from the right table where the ON
clause conditions are met. Rows in which the ON
clause is not met have NULL
in all of the right table's columns. The syntax of a LEFT JOIN
is:
SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Right Outer Join
RIGHT JOIN
returns all rows from the right table, matched to rows from the left table where the ON
clause conditions are met. Rows in which the ON
clause is not met have NULL
in all of the left table's columns. The syntax of a RIGHT JOIN
is:
SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Full Outer Join
FULL JOIN
combines LEFT JOIN
and RIGHT JOIN
. All rows are returned from both tables, regardless of whether the conditions in the ON
clause are met. Rows that do not satisfy the ON
clause are returned with NULL
in all of the opposite table's columns (that is, for a row in the left table, all columns in the right table will contain NULL
, and vice versa). The syntax of a FULL JOIN
is:
SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Examples
/* 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;
Results for 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
RIGHT OUTER JOIN
SELECT *
FROM @Animal As t1
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Results for 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
FULL OUTER JOIN
SELECT *
FROM @Animal As t1
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Results for 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