Re: Ugly problem with USING vs ON in JOIN

Joe,

I don't know where the rules might be spelled out...

But I'd say at 6.1 and higher you shouldn't try to qualify your key field.

Charles

On Mon, Mar 14, 2011 at 2:57 PM, Joe Pluta<joepluta@xxxxxxxxxxxxxxxxx> wrote:

On further examination, the USING rules are not as clear cut as they
might otherwise be.

TRUE:
This works at v5r4 but not 7.1:
select a.key, b.key, a.data, b.data
from fileA A join fileb B using (key)


and

this works at 7.1 but not v5r4:
select key, a.data, b.data
from fileA A join fileb B using (key)


However:

This works at 7.1:
select a.key, a.*
from fileA A join fileb B using (key)


but this does not work at 7.1:
select b.key, a.*
from fileA A join fileb B using (key)


I don't quite get the rules. I also don't understand how a.key + a.*
doesn't give me either a syntax error or a duplicate column. Either key
is part of a or it isn't, and it seems that the SQL engine isn't quite sure.

I hate to stop using USING, because it really is helpful especially when
you have more than one field in the JOIN, so I need to get a better
handle on this.

Joe