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 mailing list archive is Copyright 1997-2025 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.