garyp@cognos.uucp (Gary Puckering) (11/20/87)
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 3Z4UH2@PSUVM.BITNET (Lee Sailer) (11/23/87)
RBASE manages to disquise its JOINs in several interesting ways.
1. The VIEW is a virtual natural join of up to 5 relations.
2. The JOIN is a Theta-Join of two tables.
3. The INTERSECTION command---ah, yes---is (1) an Intersection if
both relations (tables) have identical columns, or (2) a natural join
otherwise.
4. The UNION command is a relational Union if the tables have identical
columns, or an Outer Join otherwise.
RBASE seems to have lots of the important parts, but Oh My do they ever
organize things in bizarre ways.
lee
jack@cca.CCA.COM (Jack Orenstein) (11/24/87)
In article <25882UH2@PSUVM> UH2@PSUVM.BITNET (Lee Sailer) writes: >RBASE seems to have lots of the important parts, but Oh My do they ever >organize things in bizarre ways. about the following: > [1. and 2. omitted] > >3. The INTERSECTION command---ah, yes---is (1) an Intersection if >both relations (tables) have identical columns, or (2) a natural join >otherwise. > >4. The UNION command is a relational Union if the tables have identical >columns, or an Outer Join otherwise. There is nothing bizarre about this (although 1 and 2 did sound odd). A natural join is exactly an intersection when the relations have identical columns. Same for (full) outer join and union. This isn't just a convenient overloading of operators - it's what you get as a result of the semantics of natural join and outer join. Jack Orenstein This is not a disclaimer.
billc@blia.BLI.COM (Bill Coffin) (11/24/87)
In article <1804@cognos.UUCP>, garyp@cognos.uucp (Gary Puckering) writes: > One thing that is noticeably missing from most relational dbms > implementations is the Outer Join operation. > [ ... ] > a) Which relational dbms products currently provide an outer join > capability (and with what syntax)? Britton Lee SQL and IDL provide outer joins, with the following syntax: SELECT a.emp-no, fname, lname, AVG(rating), salary FROM employee a, project-rate b WHERE a.emp-no *= b.emp-no note the "*=". BLI also supports "=*", "*>", "*<>", and so on. That is, every relational predicate can be restated as an outer join. (This leads to some semantic gotchas in more complex queries, but is also more flexible than the PRESERVE syntax.) > c) Will the performance of a "real" outer join be significantly > better than that of a "simulated" outer join? Yes. -- W.H.Coffin. billc@blia.BLI.COM (ucbvax!{mtxinu|ucsfcgl}!blia!billc) >> the usual disclaimer about my employer and my wretched opinions. << >> the usual witticisms that swell netnews to ridiculous proportions. <<