Databases Reference
In-Depth Information
5.3.8.2 Natural join
The
natural join
(nat-join) is a variation on the equi-join, based on the equality of
allcommon attributes in two tables.
To be specific, suppose that S and T are tables and that the set of all common attributes
between these tables is {C
1
,...,C
n
}. Thus, each table may have additional attributes, but no
further attributes in common. The natural join of S and T, which we denote by:
S nat-join T
is formed in two steps:
1.
Form the equi-join on the common attributes {C
1
,...,C
n
}.
2.
Remove the second set of common columns from the table.
Consider these tables:
A
1
A
2
A
3
A
4
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
B
1
A
2
A
4
B
4
a
b
c
d
c
j
l
f
f
b
d
g
x
y
z
h
s
j
l
j
In this case, the set of common attributes is {A
2
,A
4
}. The corresponding columns are
shaded for easier identification.
The equi-join on A
2
and A
4
is:
S.A
1
S.A
2
S.A
3
S.A
4
T.B
1
T.A
2
T.A
4
T.B
4
a
b
c
d
f
b
d
g
i
j
k
l
c
j
l
f
i
j
k
l
s
j
l
j
Deleting the second set of common columns (the columns that come from T, as shaded in
the previous table) gives:
S.A
1
S.A
2
S.A
3
S.A
4
T.B
1
T.B
4
a
b
c
d
f
g
