Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!swrinde!ucsd!ucbvax!ulysses!swfc
From: swfc@ulysses.att.com (Shu-Wie F Chen)
Newsgroups: comp.databases
Subject: Re: outerjoins
Message-ID: <13592@ulysses.att.com>
Date: 14 Aug 90 19:02:14 GMT
References: <28462@netnews.upenn.edu>
Sender: netnews@ulysses.att.com
Reply-To: swfc@ulysses.att.com (Shu-Wie F Chen)
Organization: AT&T Bell Labs
Lines: 51

In article <28462@netnews.upenn.edu>, aaron@grad2.cis.upenn.edu (Aaron
Watters) writes:
|>The various varieties of outerjoins are interesting operations.

The only reference that I could find on outer joins was in Elmasri and
Navathe's Fundamentals of Database Systems (page 164-165).  Since I
didn't know what they were, I am providing a definition:

"The OUTER JOIN operation was developed to take the union of tuples from
two relations that are not union compatible.  This operation will take
the UNION of tuples in two relations that are partially compatible,
meaning that only some of their attributes are union compatible. The
attributes that are not union compatible from either relation are kept
in the result, and tuples that have no values for these attributes are
padded with null values..."

|>In particular, they are interesting because they don't act like
|>relational operations in the sense that
|>	(r outerjoin s) minus t
|>or	(r outerjoin s) join t
|>don't seem to be well defined.  To be fanciful, I would hypothesize

I don't see how they are not well-defined given the above definition.

|>that the outerjoin lies on the fringes of the standard relational paradigm,
|>and its usefulness indicates useful directions in which the paradigm
|>should be broadened.  Comments?

I don't quite see why the outerjoin lies on the fringes of the *standard
relational paradigm*.  What differentiates it from other operators with
respect to the paradigm?

It is certainly useful, but adding new operators within the relational
model does not broaden the paradigm.

|>
|>What are interesting (simple) examples of the uses for outerjoins? -aaron

Elmasri and Navathe give several examples:

1. Suppose we want a list of all employee names and also the name of the
departments they manage *if they happen to manage a department*.  The
input relations are EMPLOYEE(ENAME, ESSN,...) and DEPARTMENT(DNAME, MGRSSN...).

2. Suppose we want a list of all the people at a school with their name,
SSN, and department, their advisor if they are students, and their rank
if they are faculty.  The input relations are STUDENT(NAME, SSN,
DEPARTMENT, ADVISOR) and FACULTY(NAME, SSN, DEPARTMENT, RANK).

Cheers,
*swfc