Native Relational Operations (Cont.)
Join
Consider the tables R and S, with headings given by
Head(R) = A1...An B1...Bk and Head(S) = B1...Bk C1...Cm
where
n,
k,
m are the numbers of attributes of each category,
A,
B, and
C.
We define
B1…Bk to the complete subset of attributes shared by these two tables.
The join of the tables
R and
S is the table represented as
R∞S, with a heading
Head(R∞S) = A1...An B1...Bk C1...Cm
A row
t is in the table
R∞S if and only if there are two rows
u in
R and
v in
S, such that
u[Bi] = v[Bi] for all
i,
1≤i≤k; then column values on the row
t are defined as follows:
t[Ai]=u[Ai] for 1≤i≤n,
t[Bi]=u[Bi] for 1≤i≤k, and
t[Ci]=v[Ci] for 1≤i≤m.
- If
B1…Bk is an empty set, k=0, then R∞S=R×S.
- If
R and S are compatible, then R∞s=R∩S.
- The ordering of the columns of a table is not significant.
An Example
| R |
| A |
B1 |
B2 |
| a1 |
b1 |
b1 |
| a1 |
b2 |
b1 |
| a2 |
b1 |
b2 |
|
|
| S |
| B1 |
B2 |
C |
| b1 |
b1 |
c1 |
| b1 |
b1 |
c2 |
| b1 |
b2 |
c3 |
| b2 |
b2 |
c4 |
|
|
⇒
R∞S
|
| R∞S |
| A |
B1 |
B2 |
C |
| a1 |
b1 |
b1 |
c1 |
| a1 |
b1 |
b1 |
c2 |
| a2 |
b1 |
b2 |
c3 |
|
|