Path: utzoo!dciem!nrcaer!sce!cognos!garyp
From: garyp@cognos.UUCP (Gary Puckering)
Newsgroups: comp.databases
Subject: Re: outerjoins
Message-ID: <8720@cognos.UUCP>
Date: 21 Aug 90 14:00:21 GMT
References: <28462@netnews.upenn.edu>
Reply-To: garyp@cognos.UUCP (Gary Puckering)
Organization: Cognos Inc., Ottawa, Canada
Lines: 60

In article <28462@netnews.upenn.edu> aaron@grad2.cis.upenn.edu.UUCP (Aaron Watters) writes:
>What are interesting (simple) examples of the uses for outerjoins? -aaron

Examples are abundant in real data processing applications.  Almost
any application where there are master files and transaction files
will yield outer join queries, because it is often the case that
some masters will not have any transactions but must still be
included in the processing.

Consider a time reporting system where Employees work on Projects
and Time Transactions are generated for the hours worked.  The
database might look like this:

	EMPLOYEES ( EMP_NO, EMP_NAME, ... )
	PROJECTS ( PROJECT_NO, PROJECT_NAME, ... )
	TIME_TXN ( EMP_NO, PROJECT_NO, HOURS )

One of the application requirements is to produce a report which
shows the hours worked on each project.  A simple join query would
do the trick:

	SELECT PROJECT_NAME, SUM(HOURS)
	FROM PROJECTS P, TIME_TXN T
	GROUP BY PROJECT
	ORDER BY PROJECT
	WHERE P.PROJECT_NO = T.PROJECT_NO

Unfortunately, this query will not report on those projects which
have no time transactions in the current reporting period.  In
applications like this one it is often very important to report, for
example, that Project 123 had no activity.  Users sometimes get
upset when they can't find what they are looking for.

The simplest way to address this requirement is to use an outer join
for this query, keeping the unmatched rows in the PROJECTS table.
Without the outer join operation, it would be necessary to write
this request as a parameterized query on TIME_TXN nested within a
query on PROJECTS.  For such a simple application, this is
considerably more work -- and might not be executed very efficiently
by the database engine.

An advantage to having an outer join operation is that applications
that need them can express them within a single query, thus
providing the optimizer with more semantics about the request.  If
an outer join has to be broken up into two queries, the optimizer
can't know what is really going on and therefore can't do a good job
of optimizing such situations.  This could become even more
important if the database is distributed sometime in the future.

The outer join I've described here, where unmatched rows in the
leftmost table are preserved, seems to be the most useful of the
various forms of outer join.  The need for preserving unmatched rows
in the left-hand table, or in both tables, seems far less
prevalent.  I can't even think of any good examples off the top of
my head.  Can anyone else?
-- 
Gary Puckering                             Cognos Incorporated
  VOICE: (613) 738-1338 x6100              P.O. Box 9707
  UUCP:  uunet!mitel!sce!cognos!garyp      Ottawa, Ontario
  INET:  garyp%cognos.uucp@uunet.uu.net    CANADA  K1G 3Z4