HP 3000 Manuals HP 3000 Manuals
Examples of JOIN Operations
The following examples show JOIN operations involving two, three, and
four tables, and an outer JOIN involving two tables..
JOINing Two Tables
A simple-item JOIN occurs if the JOIN item list between two tables are
all non-search items.
The following two-table JOIN illustrates the situation:
T1<I1,I2,I3> JOIN T2<J1,J2,J3>
| | | |
I-list J-list
JOINing Three Tables
The case of a three-table JOIN is more complicated. Consider the
following example:
(T1<I1,I2,I3> JOIN T2<J1,J2,J3>)<K1,K2,K3> JOIN T3<L1,L2,L3>
| | | | | | | |
I-list J-list K-list L-list
| |
I/J JOIN
| |
K/L JOIN
This three-table JOIN consists of two separate JOINs. The first is the
"nested" I/J JOIN. The second is the "outer" K/L JOIN, which uses the
results of the nested I/J JOIN as input.
JOINing Four Tables
The case of a four-table JOIN is most easily explained by thinking of the
above three-table JOIN nested within an outer JOIN, as follows:
((T1<I1,I2,I3> JOIN T2<J1,J2,J3>)<K1,K2,K3> JOIN T3<L1,L2,L3>)
| | | | | | | |
I-list J-list K-list L-list
| |
I/J JOIN
| |
K/L JOIN
|
M/N JOIN
<M1,M2,M3> JOIN T4<N1,N2,N3>
| | | |
M-list N-list
|
M/N JOIN continued
Outer JOINs
The JOIN operation has been enhanced to allow the inclusion of all
records of a specified table. For example, you want to see all
occurrences of records in one dataset. Field values are always filled
in, but some will have default values. The syntax in the Using Clause of
the view table definition could be:
(TABLE1 <ITEM> LEFTJOIN TABLE2 <ITEM2>) <ITEM>
JOIN TABLE3 <ITEM3>
This causes all records of TABLE1 to be selected. Where matches on
specified fields occur in TABLE2 or TABLE3, the relevant field values
would be filled in with appropriate values from the matched records. For
unmatched records, a special default value should be provided, indicating
that this TABLE1 record was not matched by a record that could have
provided a value for this field.
Here is an example of a left outer JOIN. In this operation, if TABLEA and
TABLEB are joined thus A<x> LEFTJOIN B<y>, the resulting TABLER will
contain all the records of TABLEA. Where the columns x and y are equal,
the items from TABLEB will be appended to those of TABLEA. Where x <> any
y in TABLEB, predefined values will be substituted for those columns that
would have otherwise been derived from TABLEB.
In this example, TABLEA is a list of employees and TABLEB is a list of
employee responsibilities.
TABLEA
------------------------
| EMPNUM | NAME |
------------------------
| 1 | Williams |
| 2 | Smythe |
| 3 | Johnson |
| 4 | Shapiro |
| 5 | Durand |
| 6 | Moreno |
------------------------
TABLEB
---------------------------
| EMPNUM | RESPONSIBILITY |
---------------------------
| 1 | KSAM |
| 1 | CPE |
| 2 | Batch |
| 3 | SQL |
| 4 | Debug |
| 4 | Maintainence |
| 7 | Testing |
---------------------------
The operation
A<EMP-NUM> LEFTJOIN B<EMP-NUM>
where LEFTJOIN is a left outer JOIN, will result in TABLER.
TABLER
------------------------------------------------------
| EMP-NUM | NAME | RESPONSIBILITY |
------------------------------------------------------
| 1 | Williams | KSAM |
| 1 | Williams | CPE |
| 2 | Smythe | Batch |
| 3 | Johnson | SQL |
| 5 | Durand | ???? |
| 6 | Moreno | ???? |
------------------------------------------------------
The value ???? indicates that this column is undefined.
There is also a right outer JOIN and an outer JOIN as well as a left
outer JOIN.
The right outer JOIN is the symmetrical inverse of a left outer JOIN such
that A<x> LEFTJOIN B<y> is equal to B<y> RIGHTJOIN A<x>. In the outer
JOIN, records from both tables are preserved.
Access Server supports only the left JOIN. The right JOIN can be decried
by reversing the operands and using the left JOIN.