Re: Outer joins aren't working with views

> It works for me:
> regression=# select t1.*, t2.* from t1 natural left outer join t2;
> id | id2 | id
> ----+-----+----
> 1 | 1 | 1

My recollection is that SQL9x requires that the join result lose the
link to the original table names. That is,

select id, id2 from t1 natural left outer join t2;

is legal, but

select t1.id, ...

is not.

If one needs to label the join product, then one uses aliases, as

select tx.* from (t1 natural left outer join t2) as tx;

or

select tx.* from (t1 natural left outer join t2) as tx (c1, c2);

I could see allowing in the target list explicit reference to the
underlying tables as an extension when there is no ambiguity.

However, in this case should the natural join in the original example do
the join on the column "id", and not have two columns of name "id"
available after the join?

How do you read the spec and this example? My original reading was from
the Date and Darwen book, and the SQL99 spec we have is (to put it
nicely) a bit harder to follow. I'll write some of this up for the
syntax section of the user's guide once I'm clear on it...

ref:
ansi-iso-9075-2-1999.txt from the draft documents we found on the web
last year.

ISO/IEC 9075-2:1999 SQL - Part 2: SQL/Foundation-
September 23, 1999
[Compiled using SQL3_ISO option]
section 7.7 rule 7

- Thomas