× 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 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.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.