SQL | JOIN USING vs JOIN ON

Question

Well, this probably is just a "matter of style" question but I really would like to learn a little more about the difference between JOIN t ON <equal condition> and JOIN t USING(<list of columns>).

Of course I'm assuming the two tables have the same columns, with the same name and the same data-types. In such case:

• is there any real difference between the two syntaxes?
• is the USING clause supported in all (or at least the most important) dbms s?

I already read about NATURAL JOIN on w3resource.com and the following question about NATURAL JOIN vs ON clause but they don't seem to answer the previous two questions... Moreover, neither the w3resource.com EQUI JOIN section nor INNER JOIN section do mention the USING "technique".

Answer 1

For the most part, it is a matter of style (and the fact that not all databases support using). There is one small difference. If you do:

select *
from t1 join
     t2
     on t1.col1 = t2.col1

Then col1 appears twice in the result set. If you do:

select *
from t1 join
     t2
     using (col1)

Then col1 appears only once.

One aspect of using that I like is that it encourages foreign keys to have the same names as primary keys. When this is possible (and it is not always possible), I think this is a good design that makes databases easier to use.

There are situations where using could do unexpected things. In long chains of joins, it doesn't specify where the keys come from. However, I don't find this to be a problem in well-designed databases.

On the other hand, natural joins are an abomination and should never be used. They do not specify the columns being joined. They do not even respect foreign key relationships. Not being clear on the columns being compared is just an invitation for bugs in the code -- bugs that can be really, really hard to find.