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

At 06:56 AM 1/7/00 +0000, Thomas Lockhart wrote:

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

Me too :) As I pointed out in an earlier message, fortunately most
of the outer join examples I've seen are simpler, and more readable
in either style.

Thanks, BTW, for the status update, it's about what I gathered from
looking at the code.

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

Yeah, I think this is right, I'd seen in the syntax where a general
table reference can be a join and hadn't thought about being able
to table alias the entire result. This is useful, actually. Without
the column aliases something like:

select j1.a, j1.b, j2.foo ...

makes it clear as to which join a column comes from. This clarity's
often lacking in the Oracle-style queries, as I've noticed when I
decipher them during my port-to-Postgres work. You need to unwind
what comes from where, and often have to look at the data model to
figure it out if the names are unique to the different tables and
not fully qualified as "table_name.column_name".

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