Re: amPiguous!

[Jochem van Dieten]

On 5/7/05, Chris wrote:
> Somethign else to think about as well, look at this slight modification:
> 
> select pk from a left join b using (pk);
> 
> 
> Now, it's not likely this is a valid query for your table structure

It is very likely it is. It is even an example in the MySQL manual.


> but, in this instance, a.pk and b.pk are not necessarily the same. b.pk 
> could potentially be NULL while a.pk was not

There is nothing ambiguous about this example. The SQL standard is
very clear about the way field names should be resolved in in joins.
In this case the relevant quote is:

<quote>
7.7 <joined table>
(..)
Syntax Rules
(..)
7)
If NATURAL is secified or if a <join specification> immediately
containing a <named columns join> is specified, then:
(..)
d) If there is at least one corresponding join column, then let SLCC
be a <select list> of <derived columns>s of the form
COALESCE ( TA.C, TB.C ) AS C
for every column C that is a corresponding join column, taken in order
of their ordinal positions in RT1.
</quote> ISO/IEC 9075-2:2003

In a <named columns join> (i.e. a join with the USING keyword) every
column named in the join is only present once in the resultset. And
since the selection mechanism for the value uses COALESCE there is
absolutely no ambiguity in which value gets choses: never the NULL.


Dan is absolutely correct to expect his syntax to work.

Jochem