I've never found one reason for USING and have avoided it, however one scenario arose for which I liked it.
We imported several data objects from an MS SQL open source solution. The tables were all designed with GUID keys. Joining the data via ON method yielded such ugliness because the keys are truly meaningless to the eye.
I tried USING, and all the repeating keys of the GUIDs were removed from the result set. Nice!
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Friday, May 1, 2020 12:35
To: Midrange Systems Technical Discussion
Subject: Trouble with FULL OUTER JOIN ... USING
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
midrange.com
