Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!dciem!nrcaer!cognos!garyp
From: garyp@cognos.uucp (Gary Puckering)
Newsgroups: comp.databases
Subject: Outer join in SQL
Message-ID: <1804@cognos.UUCP>
Date: Thu, 19-Nov-87 17:29:29 EST
Article-I.D.: cognos.1804
Posted: Thu Nov 19 17:29:29 1987
Date-Received: Sat, 21-Nov-87 17:20:45 EST
Organization: Cognos Incorporated, Ottawa, Canada
Lines: 42

One thing that is noticeably missing from most relational dbms 
implementations is the Outer Join operation.  Since Outer Join is
frequently required in practical applications, this leads programmers
to coding clever queries such as the following:
  
  SELECT a.emp-no, fname, lname, AVG(rating), salary
  FROM employee a, project-rate b
  WHERE a.emp-no = b.emp-no
  GROUP BY a.emp-no, fname, lname, salary
  UNION
  SELECT emp-no, fname, lname, 0e10, salary
  FROM employee
  WHERE emp-no NOT IN (SELECT emp-no FROM project-rate)
  
Instead of:

  SELECT a.emp-no, fname, lname, AVG(rating), salary
  FROM employee a, project-rate b
  WHERE a.emp-no = b.emp-no
  PRESERVE employee
  GROUP BY a.emp-no, fname, lname, salary

This raises some obvious questions:

a)  Which relational dbms products currently provide an outer join
    capability (and with what syntax)?

b)  What will current relational dbms products do to execute this
    query (i.e. the first version I gave)?

c)  Will the performance of a "real" outer join be significantly
    better than that of a "simulated" outer join?  In other words,
    will query optimizers be able to recognize the first query I
    gave as being equivalent to the second, and execute it that way?

I'm looking forward to some interesting responses on this, especially
from the database vendors!
-- 
Gary Puckering                                   P.O. Box 9707
Cognos Incorporated                              3755 Riverside Dr.
VOICE:  (613) 738-1440   FAX: (613) 738-0002     Ottawa, Ontario
UUCP: decvax!utzoo!dciem!nrcaer!cognos!garyp     CANADA  K1G 3Z4