three or more tables JOIN USING common key

Question

Does this work for any RDBMS?

CREATE TABLE TBLA (MYKEY CHAR (10 ), FLDA CHAR (10 ));
CREATE TABLE TBLB (MYKEY CHAR (10 ), FLDB CHAR (10 ));                          
CREATE TABLE TBLC (MYKEY CHAR (10 ), FLDC CHAR (10 ));

Query:

select mykey, flda, fldb, fldc  
from tbla                       
  join tblb using (mykey)       
  join tblc using (mykey)

Perhaps with an explicit join order?

select mykey, flda, fldb, fldc  
from ((tbla                       
          join tblb using (mykey))
      join tblc using (mykey))

It doesn't seem to work in DB2 for IBM i 7.1...I get an SQL0203 - Name MYKEY is ambiguous at the second USING (MYKEY)

I'm wondering about the other DBMS.

SQL Fiddler shows it working with Oracle 11gR2 and MySQL 5.6.6 m9...but not MS SQL Server 2012; it apparently doesn't support the JOIN USING syntax.
http://sqlfiddle.com/#!4/ad8c0

It'd be nice to have some confirmation from actual users of those DBs and any others that chime in; particularly DB2 for LUW.

I believe the `JOIN..USING` syntax is not valid for DB2 LUW. The reference to `MYKEY` is indeed ambiguous if you have more than one join. — mustaccio, Oct 21 '14 at 18:56
In Oracle it is some "extension" of a natural join. You can use it in left/right/full joins. The restriction: column(s) used in USING mustn't have table alias anywhere in a query. — Multisync, Oct 21 '14 at 19:06
No, natural join (`using`) will work in some platforms, but not all. For example, there is no such support in SQL Server. — Aaron Bertrand, Oct 21 '14 at 19:21
@mustaccio, interesting...I hadn't expected that JOIN USING would have been added to DB2 for i before DB2 LUW or z/OS... — Charles, Oct 21 '14 at 20:42
@Multisync, it appears from the Oracle docs that NATURAL JOIN and USING are two different things. Though from the behavior, I'd call USING a subset(?) of the natural join. MySQL also documents them as two different things. Though the MySQL doc does say "The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables." — Charles, Oct 21 '14 at 20:52
@charles That's why I wrote an "extension". USING can do everything that NATURAL can + different types of joins (NATURAL = all columns with the same names + only inner join). So in my view natural is a subset of USING — Multisync, Oct 21 '14 at 20:59
Personally, you should avoid `USING` (beyond the usual advice for attempts at interoperability). Why? It's simply not flexible enough. Nearly all sets will have some sort of restriction on them (say, date ranges), and these should be moved into the joins if at all possible (only use the `WHERE` clause for things that solely touch `TblA`, and a few specialized cases). Plus, most of the time you care about columns other than the primary keys, which often end up named differently. (Side note: `NATURAL JOIN`s are next to useless on production dbs with audit fields) — Clockwork-Muse, Oct 22 '14 at 12:43

score 0 · Answer 1 · answered Oct 21 '14 at 19:15

0

You should use alias for the tables.

select a.mykey, a.flda, b.fldb, c.fldc  
from tbla a                      
  join tblb b on a.mykey = b.mykey       
  join tblc c on b.mykey = c.mykey

answered Oct 21 '14 at 19:15

Ankit Vora

702
1
5
16

I'm well aware of how to use JOIN ON. I'm not asking about alternatives for JOIN USING. – Charles Oct 21 '14 at 20:37
@Charles Yes, but notice how the second JOIN resolves the ambiguity by explicitly stating a.mykey in the first and b.mykey in the second. With USING, there's no way to say how the second join should be created, by joining A to C or B to C. – user2338816 Oct 21 '14 at 23:43
@user2338816 Perhaps, but the use of explicit parenthesis to specify join order should also remove any ambiguity. `( A join B ) join C` But I get the same error. – Charles Oct 23 '14 at 13:11
@Charles I don't quite see how parentheses would ensure which one of A.mykey or B.mykey should be used. But it seems extremely academic. As long as there is no specific SQL standard that states how it should be done, and I don't see one yet in docs I have for ISO SQL 2006, it's up to the vendor. Different vendors may implement differently. In any case, I do agree that it shouldn't matter; a plausible implementation is possible. It apparently just isn't defined in the standard. – user2338816 Oct 24 '14 at 05:25
@user2338816, the parentheses force A join B to be done before C is considered. Once the AB results is built with using, there's only 1 MYKEY column in it. Thus, joining to C using MYKEY is unambiguous. – Charles Oct 24 '14 at 14:27

three or more tables JOIN USING common key

1 Answers1