× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.