OUTER JOIN

SQL has two significant OUTER JOINS. LEFT and RIGHT OUTER JOINS.

LEFT OUTER JOIN statement is used to retrieve all records from LEFT table and only matched data of specified column(s) from RIGHT table.

This simple example will depict us logic of LEFT OUTER JOIN

CREATE TABLE TABLE_1
(
ID1 INT,
NUMBER1 VARCHAR(20)
)

INSERT INTO TABLE_1(ID1, NUMBER1) VALUES(1,'One')
INSERT INTO TABLE_1(ID1, NUMBER1) VALUES(2,'Two')
INSERT INTO TABLE_1(ID1, NUMBER1) VALUES(3,'Three')
INSERT INTO TABLE_1(ID1, NUMBER1) VALUES(4,'Four')
INSERT INTO TABLE_1(ID1, NUMBER1) VALUES(5,'Five')
INSERT INTO TABLE_1(ID1, NUMBER1) VALUES(6,'Six')
INSERT INTO TABLE_1(ID1, NUMBER1) VALUES(7,'Seven')
INSERT INTO TABLE_1(ID1, NUMBER1) VALUES(8,'Eight')
INSERT INTO TABLE_1(ID1, NUMBER1) VALUES(9,'Nine')
INSERT INTO TABLE_1(ID1, NUMBER1) VALUES(10,'Ten')

select * from TABLE_1

ID1	NUMBER1
1	One
2	Two
3	Three
4	Four
5	Five
6	Six
7	Seven
8	Eight
9	Nine
10	Ten

CREATE TABLE TABLE_2
(
ID2 INT,
NUMBER2 VARCHAR(20)
)

INSERT INTO TABLE_2(ID2, NUMBER2) VALUES(6,'Six')
INSERT INTO TABLE_2(ID2, NUMBER2) VALUES(7,'Seven')
INSERT INTO TABLE_2(ID2, NUMBER2) VALUES(8,'Eight')
INSERT INTO TABLE_2(ID2, NUMBER2) VALUES(9,'Nine')
INSERT INTO TABLE_2(ID2, NUMBER2) VALUES(10,'Ten')
INSERT INTO TABLE_2(ID2, NUMBER2) VALUES(11,'Eleven')
INSERT INTO TABLE_2(ID2, NUMBER2) VALUES(12,'Twelve')
INSERT INTO TABLE_2(ID2, NUMBER2) VALUES(13,'Thirteen')
INSERT INTO TABLE_2(ID2, NUMBER2) VALUES(14,'Fourteen')
INSERT INTO TABLE_2(ID2, NUMBER2) VALUES(15,'Fifteen')

select * from TABLE_2

ID2	NUMBER2
6	Six
7	Seven
8	Eight
9	Nine
10	Ten
11	Eleven
12	Twelve
13	Thirteen
14	Fourteen
15	Fifteen

select * from TABLE_1 LEFT OUTER JOIN TABLE_2 on TABLE_1 .ID1 = TABLE_2 .ID2

ID1	NUMBER1	ID2	NUMBER2
1	One	NULL	NULL
2	Two	NULL	NULL
3	Eight	NULL	NULL
4	Four	NULL	NULL
5	Five	NULL	NULL
6	Six	6	Six
7	Seven	7	Seven
8	Eight	8	Eight
9	Nine	9	Nine
10	Ten	10	Ten

RIGHT OUTER JOIN will return all the records from RIGHT table and only corresponding matching records from LEFT table as shown in below.

select * from TABLE_1 RIGHT OUTER JOIN TABLE_2 on TABLE_1 .ID1 = TABLE_2 .ID2

ID1	NUMBER1	ID2	NUMBER2
6	Six	6	Six
7	Seven	7	Seven
8	Eight	8	Eight
9	Nine	9	Nine
10	Ten	10	Ten
NULL	NULL	11	Eleven
NULL	NULL	12	Tweleve
NULL	NULL	13	Thirteen
NULL	NULL	14	Fourteen
NULL	NULL	15	Fifteen