Re: Table name scope (was Re: [BUGS] Outer joins aren't working with views)

> The first and second items here seem to be perfectly clear that the
> names t1 and t2 have scope across the whole SELECT statement and are not
> hidden within the <joined table> formed by the OUTER JOIN clause.

You are right. If there is a "correlation name", then those underlying
table names become invisible, but that was not in the example here.
Rereading my Date and Darwen clarified this for me. However, there are
*some* columns for which this explicit table qualification is not
allowed, including in the example of NATURAL JOIN.

Date and Darwen, 4th ed, pp 142-144 discuss various aspects of join
scope and behavior. For NATURAL JOIN, the columns with common names
forming the join columns *lose* their underlying table name, since they
can't be traced back to a column from a specific table (the table of
origin is ambiguous). And for a NATURAL JOIN, it is impossible to get
back two columns with the same name, since those columns were unified by
the join process.

The process is required to join on the columns with names in common, and
to swallow one of each pair in the result. How should you refer to the
column that remains?
 create table t1 (id int, id2 int); create table t2 (id int, name text); select * from t1 natural left outer join t2;

must return something from the set of columns (id, id2, name), and two
columns of name "id" will not be visible. Also, column "id" cannot be
qualified with a table name. So
 select t1.id from t1 natural join t2;

is not legal (though perhaps could be justified as an extension). The
columns *not* involved in the join operation, id2 and name, *can* be
qualified by the underlying table name, but the only way to get the same
for "id" after the natural join is to use a correlation name. e.g.
 select tx.id from (t1 natural join t2) as tx; select t1.id2 from t1 natural join t2;

are both legal.

> It'd be useful to check the above example against Oracle and other
> implementations, but the parts of the spec that I can follow seem
> to say that we've got the right behavior now.

Oracle does not support SQL9x join syntax, so we can't ask it for an
example. Not sure about the others.

Comments?
                      - Thomas