RE: [HACKERS] Enhancing PGSQL to be compatible with Informix SQL

At 11:08 PM 1/6/00 +0200, Ansley, Michael wrote:

>>> What about >2 table joins? Wish I had my book here, but I though tyou
>>> could do multiple OUTER joins, no?
>
>Oracle uses a syntax which I quite like. The query above would become:
>
>SELECT *
>FROM tab, tab2
>WHERE tab1.col1 = tab2.col2 (+)
>
>I've actually used queries something like this:
>
>SELECT blah, blah, blah
>FROM t1, t2, t3, t4
>WHERE t1.start_date BETWEEN t2.start_date (+) AND t2.end_date (+)
>AND t1.y = t2.y (+)
>AND t3.x (+) = t1.x
>AND t3.y (+) = t1.y
>AND t4.x = t1.x;

Good...you saved me the trouble of digging out some examples from the
code I'm porting, which occasionally due similar things :)

I think the ANSI SQL 92 equivalent is something like:

select ...
from t1 inner join t4 on t1.x=t4.x,
t2 left outer join t1
on t2.y=t1.y and
(t1.start_date between t2.start_date and t1.start_date),
t3 left outer join t1 on t3.x=t1.x and t3.y = t1.y;

I've never used an ANSI SQL 92 compliant RDBMS, I'm not sure
if t2/t1 become ambiguous and need to be given different names
using "as foo" in each case, etc. Actually, you would in
order to build the target list unambiguously I guess...

But that's the general gist. I think - Thomas, am I at all
close?

Of course, you can continue to write the inner join in the
old way:

select ...
from t1 inner join t2 on t1.x=t2.x;

and

select ...
from t1,t2 where t1.x=t2.x;

where the last form of the inner join might be considered an
optimization of a cross-join restricted by a boolean expression
in the where clause rather than a proper inner join. In other
words, the two queries return the same rows and one would be
very disappointed if the second form formed the cartesian product
of t1 and t2 and then filtered the resulting rows rather than do
an inner join!

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.