Re: Trouble with FULL OUTER JOIN ... USING

I agree. And I think that if the implementation were to make the columns from the USING clause, if it were to make them them the COALESCE of the columns from the JOIN'd tables, it'd not be an unreasonable thing to see.

Now I don't know the standards - if they even have thought of USING in the context of OUTER JOINs - it works fine for the several INNER types.

Are you up for submitting an RFE? The point might be one of losing data in this scenario.

Vern

On 5/3/2020 10:52 AM, Joe Pluta wrote:

On 5/2/2020 8:30 AM, Vernon Hamberg wrote:

The result table of the join contains the columns from the USING clause first, then the columns from the first table of the join that were not in the USING clause, followed by the remaining columns from the second table of the join that were not in the USING clause. Any column specified in the USING clause cannot be qualified in the query.

Yes, you hit on the issue: if you cannot qualify fields in the USING clause, and the FULL OUTER JOIN only returns the join fields from the left table, then when a record exists only in the right table, THERE IS NO WAY TO SEE THE KEY (join) FIELDS when you do a FULL OUTER JOIN ... USING.  And I'm not shouting, I just wanted to emphasize that particular piece of the puzzle. The Oracle documentation seems to indicate that you might be able to specify the USING fields as qualified names which would get around my case.  I could at least coalesce the key fields. But it looks like DB2 explicitly forbids it.

That's a weakness.