A view VIEWAB is defined with the following contents using two tables TABA and TABB:
Inner and Outer Join
The dataset which can be selected with a view depends primarily on whether the view implements an inner join or an outer join.
With an inner join, you only get the records of the cross-product table for which there is an entry in all tables involved in the view.
With an outer join, records are also selected for which there is no entry in some of the secondary tables.
The set of hits determined by an outer join can therefore be a true set of the subset of hits determined with an inner join.
Database views implement an "inner join," that is, you only get those records of the primary table for which there are also records of the secondary tables which were selected with the join. Help views and maintenance views, however, implement an outer join.
A view VIEWAB is defined with the following contents using two tables TABA and TABB:
TABA |
TABB | ||||
Field1 |
Field2 |
Field3 |
Field4 |
Field5 | |
1 |
Text1 |
1 |
A |
Text3 | |
2 |
Text2 |
1 |
B |
Text4 | |
3 |
Text7 |
2 |
A |
Text5 | |
|
2 |
B |
Text6 | |||
The join condition is:
TABA-FIELD1 = TABB-FIELD3.
Fields TABA-Field1, TABA-Field2 and TABB-Field5 are copied to the view.
If the view implements an inner join (database view), the following records can be selected with the view:
Field1 |
Field 2 |
Field 5 |
1 |
Text1 |
Text3 |
1 |
Text1 |
Text4 |
2 |
Text2 |
Text5 |
2 |
Text2 |
Text6 |
The last entry from TABA (3, Text7) cannot be selected with the view since there is no entry with FIELD3 = 3 in table TABB.
If the view implements an inner join (help view, maintenance view), the following records can be selected with the view:
Field1 |
Field 2 |
Field 5 |
1 |
Text1 |
Text3 |
1 |
Text1 |
Text4 |
2 |
Text2 |
Text5 |
2 |
Text2 |
Text6 |
3 |
Text7 |
The last entry from TABA (3, Text7) can thus be selected here.