- 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
Our Random Articles
- Somdev Devvarman ousts Xavier Malisse 6-1, 3-6,7-6(5)
- How To Choose A Good Online Traffic School
- New Year Wishes 2010
- New Year Wishes
- How to use EXTRACT function with date and time columns
More Links
4 Comments
Material that you are providing is very easy to learn. But it is much better than this , if you ll give the full material of this at ONE LINK.
Thank you
Suresh.
That is right Suresh. This is the reason I have given all the links in one page, please visit this
http://readvitamin.com/teradata/
Hope this helps.
Hi. Can anyone tell me how to extract time from timestamp?
For Example
timestamp=’2008-03-28 14:11:04′
i want time ’14:11:04′ from timestamp through query.
Thanks in advance.
Hi Vaideesh,
If you need only time, you can probably try the following;
SELECT time;
To answer your question you should do something like this.
SELECT CAST(VaideeshTime as time(0)) from timestamp;
VaideeshTime
——–
14:11:04
Hope that helps.