Re: Trouble with FULL OUTER JOIN ... USING

I see the same in v7.2...would be curious what version you're on...

But in any case, I stay away from USING for production code..

Simply because it can't be used once you go past 2 tables....

Charles

On Fri, May 1, 2020 at 10:34 AM Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>
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.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com