Database Reference
In-Depth Information
Example 19:
Find department heads that are not division heads:
Note:
This is an example of a theta-join. Here, we make use of the fact that the table
Division
stores an attribute (
DvHead#
) which indicates the staff member who is head
of the division. We also make use of the fact that the table
Dept
stores a similar attribute
(
Dhead#
) to indicate the staff member who is head of the department. Further, every
department belongs to a division.
Example 20:
Get all students who share a birthday with another student.
Note:
This is an example of a Cartesian product of a relation with itself. Compare this
with Example 8 in section 7.4 and Example 3 in section 8.2.
Caution:
If you wish to join relation R1 with relation R2, make sure the R1's foreign
key does not allow for null values, since this will produce a spurious result. If in doubt,
use an
outer join
, as clarified in the next example.
Example 21:
Provide a list of all students and their assigned hall. Assume that it is
possible for
Hall#
in the table
Student
to have null values. The following solution would
be incorrect, since it could produce a spurious result:
Note:
The results will exclude all students who have not been assigned to a hall
(incidentally, this is an example of a natural join). To avoid this situation, use the
outer
join
by simply including a parenthesized plus sign (+) on the attribute of the table where
there may not be a corresponding row (in this case H.Hall#), when specifying the join
condition. The Query should therefore be written as:
