Re: Trouble with FULL OUTER JOIN ... USING

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.