Re: Ugly problem with USING vs ON in JOIN

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

Joe,

The problem is that while IBM added USING at v5r4, they didn't
implement it correctly.

It got fixed at 6.1, but not PTF'ed back to v5r4.

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

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

The fix is good because at 6.1 you can do this:
select key, a.data, b.data, c.data
from fileA A
join fileb B using (key)
join filec C using (key)

There should be some posts from me from a while ago discussing the
issue. My decisions was to only use JOIN USING at v5r4 in ad-hoc
stuff. Production SQL had to continue to use the JOIN ON syntax.

Charles


On Mon, Mar 14, 2011 at 11:27 AM, Joe Pluta<joepluta@xxxxxxxxxxxxxxxxx> wrote:

Here's a real beauty. Try this with any file:

with t1 as (select key1, data1 from myfile)
select t1.* from t1 join myfile using (key1)

Then try this:

with t1 as (select key1, data1 from myfile)
select t1.* from t1 join myfile on t1.key1 = myfile.key1


myfile is any file with a single unique key field. key1 is that key
field. data1 is any other field in the file. Actually, it doesn't HAVE
to be that way; you can use any two fields. But if you don't use a
unique field you may end up with a REALLY big result set.

The first select will return only data1 in the result set, while the
second will return both key1 and data1. I'm one behind on DB2 group
PTFs, but this is a pretty big one and it just bit me badly.

Please note that this is a completely stripped down query and I realize
that it effectively does "select key1, data1 from myfile". The actual
query is much more involved. But this reproduces the problem in the
fewest steps.

Now I have to start looking for USING in all my queries.

Joe