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

> >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;
> 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;

Hmm. I'm not sure what the Oracle example actually gives as a result,
and I find the syntax as confusing as others find SQL92 syntax ;)

> 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...

Once two tables are mentioned in an "outer join", then individual
columns can no longer be qualified by the original table names.
Instead, you are allowed to put table and column aliases on the join
expression:

select a, b, c, z
from (t1 left join t2 using (x)) as j1 (a, b, c)
right join t3 on (j1.a = t3.y);

(I think I have this right; I'm doing it from memory and have been
away from it for a little while).

- Thomas

--
Thomas Lockhart lockhart(at)alumni(dot)caltech(dot)edu
South Pasadena, California