>>>> So there are two issues here which I hope to clarify: scoping
>>>> on joins, and NATURAL and USING join column sets.
I've been looking some more at this business, and I have found one of
the reasons that I was confused. The SQL92 spec says (6.3 syntax rule
2)
2) Case:
a) If a <table reference> TR is contained in a <from clause> FC with no intervening <derived
table>,then the scope clause SC of TR is the <select statement: single row> or innermost <query
specification>that contains FC. The scope clause of the exposed <correlation name> or exposed <table name>
ofTR is the <select list>, <where clause>, <group by clause>, and <having clause> of SC,
togetherwith the <join condition> of all <joined table>s contained in SC that contains TR.
b) Otherwise, the scope clause SC of TR is the outermost <joined table> that contains TR with no
intervening<derived table>. The scope of the exposed <correlation name> or exposed <table name>
ofTR is the <join condition> of SC and of all <joined table>s contained in SC that contain TR.
I mistakenly read this with the assumption that <derived table> means
a sub-SELECT. It does mean that, but it also means a <joined table>,
*if and only if* that joined table is labeled with a <correlation name>.
The relevant productions are:
<table reference> ::= <table name> [ [ AS ] <correlation name> [ <left paren>
<derivedcolumn list> <right paren> ] ] | <derived table> [ AS ] <correlation name> [
<leftparen> <derived column list> <right paren> ] | <joined table>
<derived table> ::= <table subquery>
<table subquery> ::= <subquery>
<subquery> ::= <left paren> <query expression> <right paren>
<query expression> ::= <non-join query expression> | <joined table>
So "(<joined table>) AS foo" has a <subquery> but "<joined table>" doesn't.
AFAICT, this means that table references defined within the join are
invisible outside "(<joined table>) AS foo", but they are visible
outside a plain "<joined table>". This is more than a tad bizarre
... but it explains the examples you quoted from Date and Darwen.
However, as long as a table reference is visible, I think that the
set of qualified column names available from it should not depend on
whether it came from inside a JOIN expression or not. Comments?
regards, tom lane
