Re: Trouble with FULL OUTER JOIN ... USING -- MIDRANGE-L

Hi Joe

Your question was intriguing, but I didn't have time to look at it until today.

Anyhow, there is some documentation that might help, namely, from the 7.1 SQL Reference is a new paragraph -

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.

This is different from the columns you get in either a LEFT or RIGHT OUTER JOIN _and_ with FULL OUTER JOIN ON - you get 4 columns with those, only 3 with FULL OUTER JOIN USING.

Right after that paragraph is this -

The USING clause is equivalent to ajoin-conditionin which each column from the lefttable-referenceis compared equal to a column of the same name in the righttable-reference.

So the JOIN clause in your statement,

T1 FULL OUTER JOIN T2 USING (F1)

is the same as

T1 FULL OUTER JOIN T2 ON T1.F1 = T2.F1

as you probably know.

I used a SELECT * so that I could see whatever the result set WOULD be without me choosing the columns of it.

So here are the statements I ran, each followed by their result set - the NULL marker is not shown here, these are copied from the spreadsheet you get when saving the results in ACS.

select * from t1 left outer join t2 on t1.f1 = t2.f1;

F1 F2 F1 F2
T1 T1 ONLY

BOTH IN BOTH BOTH IN BOTH

select * from t1 right outer join t2 on t1.f1 = t2.f1;

F1 F2 F1 F2

T2 T2 ONLY
BOTH IN BOTH BOTH IN BOTH

select * from t1 full outer join t2 on t1.f1 = t2.f1;

F1 F2 F1 F2
T1 T1 ONLY

BOTH IN BOTH BOTH IN BOTH

T2 T2 ONLY

select * from t1 full outer join t2 using (f1);

F1 F2 F2
T1 T1 ONLY
BOTH IN BOTH IN BOTH

T2 ONLY

I think that the phrase in the first quote above, "contains the columns from the USING clause first", _carries_ an implication of columns from T1. This probably works more as expected with INNER JOINs - the 1st column when specifying USING is common to both. Nothing is lost.

So it appears this is "working as designed", although this is kind of hurting MY brain, too!

The problem with FULL OUTER is, there isn't a common value to put in the column for F1. Maybe those USING column(s) should have the value from either of the tables being JOINed - this would still make it work for INNER joins.

The result you expect does seem like it'd be much more helpful - and it would retain data, not lose it.

Might be time for an RFE, especially if you could find different behavior in other RDBMS'.

Whew! That was fun on a Saturday! Better than this quote:

I’m so bored, I went outside and knocked on my own door, then ran back inside to ask who it was

Cheers!
Vern

On 5/1/2020 11:34 AM, Joe Pluta wrote:

I've run into variations of this issue alot over the years. The problem is that the fields in the USING clause don't always work in a way that is convenient. Here's an example:

CREATE TABLE QTEMP/T1
(F1 CHAR (10 ) NOT NULL WITH DEFAULT,
   F2 CHAR (10 ) NOT NULL WITH DEFAULT)

CREATE TABLE QTEMP/T2
(F1 CHAR (10 ) NOT NULL WITH DEFAULT,
   F2 CHAR (10 ) NOT NULL WITH DEFAULT)

INSERT INTO T1 VALUES('T1', 'T1 ONLY')
INSERT INTO T2 VALUES('T2', 'T2 ONLY')
INSERT INTO T1 VALUES('BOTH', 'IN BOTH')
INSERT INTO T2 VALUES('BOTH', 'IN BOTH')

SELECT F1, T1.F2, T2.F2 FROM
T1 FULL OUTER JOIN T2 USING (F1)

F1          F2          F2
T1          T1 ONLY     -
BOTH        IN BOTH     IN BOTH
-           -           T2 ONLY

Note that the F1 field is only populated when there is a record in T1. If I flip the order of the join to T2 FULL OUTER JOIN T1, I only have data in F1 if the record exists in T2. The problem is that I am unable to get a full list of all keys in both files from the FULL OUTER JOIN.

Intellectually I guess I understand it, but I thought the JOIN fields would always be populated on an OUTER JOIN, with whichever record had the data. In fact, I can do it myself if I coalesce the JOIN fields and use ON instead of USING:

SELECT COALESCE(T1.F1, T2.F1) F1, T1.F2, T2.F2
FROM T1 FULL OUTER JOIN T2 ON (T1.F1 = T2.F1)

F1    F2          F2
T1          T1 ONLY     -
BOTH        IN BOTH     IN BOTH
T2          -           T2 ONLY

I guess I'm just wondering if that's the expected result of USING, and if it's documented somewhere. I don't mind the second syntax, although it gets pretty wordy when you have a lot of key fields. It's also prone to errors if you get the field names wrong. I realize that's always the case, but the second syntax creates a lot more opportunity for failure.