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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
