Path: utzoo!attcan!uunet!tut.cis.ohio-state.edu!unmvax!bbx!tantalum!tom
From: tom@eds.com (Tom H. Meyer)
Newsgroups: comp.databases
Subject: Re: outerjoins
Keywords: outerjoins
Message-ID: <2128@tantalum.UUCP>
Date: 15 Aug 90 17:00:52 GMT
References: <28462@netnews.upenn.edu>
Sender: usenet@tantalum.UUCP
Reply-To: tom@ozmium.UUCP (Tom H. Meyer)
Organization: EDS Research
Lines: 41

In article <28462@netnews.upenn.edu> aaron@grad2.cis.upenn.edu.UUCP (Aaron Watters) writes:
>The various varieties of outerjoins are interesting operations.
>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
>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?
>
>What are interesting (simple) examples of the uses for outerjoins? -aaron

I recommend you get a hold of this year's SIGMOD proceedings. Arnon
Rosenthal and Cesar Galindo-Legaria presented a paper,
"Query Graphs, Implementing Trees, and Treely-Reorderable Outerjoins"
which addresses your questions directly.

To summarize, outerjoins are useful when you wish to see all the fields
values of the join keys whether they matched something in the other table
or not. As an example, perhaps a query which joined classes to students
might not display all the classes if they had no students enrolled in
them. If this is not acceptable, an outerjoin will solve the problem.

A good introduction to outerjoin theory can be found in

C.J. Date, "The Outer Join", Proc @nd International Conf on Databases
(ICOD-2), Cambridge, England, Sept. 1983

There are many types of outer joins and Date introduces them all in this
paper. However, the "left" and "right" outerjoins (which some SQL
implementations denote: A.a *= B.a and A.a =* B.a) have some nasty
properties like non-associativity and distributivity. This makes query
optimization of outerjoins extremely hard. Dr. Rosenthal presents a 
complete algebra of these outerjoins including a characterization of
when they are well behaved.

Hope this helps

tom meyer, EDS Research               | If I don't see you in the future
tom@edsr.eds.com or ...uunet!edsr!tom | I'll see you in the pasture