ReadVitamin

The Outer Join is an extension of the Inner Join which includes both rows that qualify for a simple Inner Join as well as a specified set of rows that do NOT match the join conditions mentioned in the query. This is the reason sometimes it gets a bit complicated to code the Outer Joins correctly. There are three types of outer joins:

Left Table (Left Outer Join).
Right Table (Right Outer Join).
Both Tables (Full Outer Join).

As mentioned before the sole purpose of an OUTER JOIN is to find and return rows that do NOT match at least one row from another table. It is something like an “exception” reporting, but at the same time, it does the INNER JOIN processing too. Therefore, the matching rows are returned a
long with all rows without a matching value from another table, which might be due to the existence of a NULL or invalid data values.

1. Left Table (Left Outer Join). In any Outer Join it is very important to know which table is Outer table; this is the table that decides the how the rows (matching as well as non-matching) would be returned. In a Left Outer Join, the table that is mentioned at the left position to the key word “Left Outer Join” is the outer table. This means the non-matching rows would be returned from this table. For example,

Table A                                      Table B

F1	F2	F3
AA	1	A1
BB	2	B2
CC	3	C3
DD	4	D4

F1	F2	F3
CC	3	C3
DD	4	D4
EE	5	E5
FF	6	F6

SELECT A.f1 OuterCol,
B.f1 InnerCol
FROM A LEFT OUTER JOIN B
ON A.f1 = B.f1

The above query would return:

OuterCol    InnerCol
DD             DD
AA             NULL
CC             CC
BB             NULL

See the NULL values appearing in the inner table,
a further addition of a WHERE condition,

SELECT A.f1 OuterCol,
B.f1 InnerCol
FROM A LEFT OUTER JOIN B
ON A.f1 = B.f1
WHERE A.F1 = ‘AA’

Would return the following result.

OuterCol InnerCol
AA NULL

2. Right Table (Right Outer Join). In a Right Outer Join, the table that is mentioned at the right position to the key word “Right Outer Join” is the outer table. This means the non-matching rows would be returned from this table. For example,

SELECT A.f1 OuterCol,
B.f1 InnerCol
FROM A RIGHT OUTER JOIN B
ON A.f1 = B.f1

OuterCol      InnerCol
NULL            FF
DD             DD
NULL            EE
CC             CC

But notice that neither of the following queries would return any rows as the value ‘AA’ is not present in the outer table (B).

SELECT A.f1 OuterCol, B.f1 InnerCol
FROM A RIGHT OUTER JOIN B
ON A.f1 = B.f1
WHERE A.F1 = ‘AA’SELECT A.f1 OuterCol, B.f1 InnerCol
FROM A right outer JOIN B
ON A.f1 = B.f1
WHERE B.F1 = ‘AA’

3.Both Tables (Full Outer Join). In a Full Outer Join, both the tables that are mentioned at both sides of the key word Full Outer Join are the outer tables. This means the non-matching rows would be returned from both the tables. For example,

SELECT A.f1 OuterColA,
B.f1 OuterColB
FROM a full outer JOIN b
ON A.f1 = B.f1

OuterColA   OuterColB
NULL           FF
DD            DD
AA               NULL
NULL           EE
CC            CC
BB               NULL

Unlike the Right Outer Join, if you run the following query you would see the resulted row which would be similar to that of inner join query. This happens because the table A is also treated as an Outer table.

SELECT A.f1 OuterColA,
B.f1 OuterColB
FROM a full outer JOIN b
ON A.f1 = B.f1
WHERE a.F1 = ‘AA’

OuterCol OuterColB
AA NULL

Popularity: 41% [?]