Bill
Although successive union selects is a good technical solution in the cases
you described, in many situations the outer joins dramatically degrade
performance of the querys. R:Base's optimizer (and most other db
optimizers) have trouble using indexes when unions are used.
On several projects I have worked on where multiple outer joins were an
issue we resorted to using temporary tables to get around the problem. We
build a temporary table for the base table and add columns for the foreign
key fields. We then issue and insert command to populate the table and
updates to add the data from the foreign key tables. The information needed
to build these temporary querys is stored in tables and all of the SQL can
be built in code. We do this primaryily in VB, but it can be done just as
easily in R:Base.
When I first saw this done, I thought the performance on this would be bad
also. I recently replaced a multiple table union with one of these temp
queries and the performance went from close to a minute to seconds. The
base table can even be a view to simplify matters even more.
Steve
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Bill Downall
Sent: Thursday, June 28, 2001 8:25 PM
To: [EMAIL PROTECTED]
Subject: Re: Outer Joins
Bob,
It is possible to do this with outer joins of views that are outer joins.
The cleanest way I have found to do this is with the UNION SELECT
syntax rather than the OUTER JOIN syntax.
However, with more than 2 tables, you have to consider many more
possible joins than with 2. 2 tables can give you links between the
two, plus rows from table 1 with no matches in table 2, and rows from
table 2 with no matches in table 1. With 3 tables, you have the 3-table
linked set, plus 3 possible combinations of 2 tables with no match in
the third table, plus 3 possible 1-table sets with no matches in either of
the other two, for a potential of 7 SELECT commands stacked up.
Of course, one or more of those combinations may not be possible in
your own joins, depending on constraints and business rules.
With 4 tables, you have way to many possible links, and you probably
have painted yourself into a corner with bad data design.
Make sure the first SELECT selects real columns from real tables, not
NULLS, because the first SELECT sets the datatypes for the entire
combined set.
Here's a sample that does a 3-table join, followed by two 2-table joins,
followed by rows from the middle table with no matches in either of the
other two.
SELECT t1.col1, t2.col2, t2.col3, t3.col4, t3.col5 +
FROM table1 t1, table2 t2, table3 t3 +
WHERE (t1.linkcol1 = t2.linkcol1) +
AND (t2.linkcol2 = t3.linkcol2) +
UNION ALL SELECT +
t1.col1, t2.col2, t2.col3, NULL, NULL +
FROM table1 t1, table2 t2 +
WHERE (t1.linkcol1 = t2.linkcol1) +
AND NOT EXIST +
(SELECT * from table3 t3 +
WHERE t3.linkcol2 = t2.linkcol2) +
UNION ALL SELECT +
NULL, t2.col2, t2.col3, t3.col4, t3.col5 +
FROM table2 t2, table3 t3 +
WHERE (t2.linkcol2 = t3.linkcol2) +
AND NOT EXIST +
(SELECT * from table1 t1 +
WHERE t1.linkcol1 = t2.linkcol1) +
UNION ALL SELECT +
NULL, t2.col2, t2.col3, NULL, NULL +
FROM table2 t2 +
WHERE NOT EXIST +
(SELECT * from table1 t1 +
WHERE t1.linkcol1 = t2.linkcol1) +
AND NOT EXIST +
(SELECT * from table3 t3 +
WHERE t3.linkcol2 = t2.linkcol2)
Bill
On Thu, 28 Jun 2001 16:09:08 -0400, Bob Powell wrote:
>I think I already know the answer to this one but I want
>to be sure. Is it possible to use an OUTER JOIN with more
>than two table/views at a time?
