CODEWITHSUNDEEP
sqljoin
Bruno Zamengo
Bruno Zamengo

Reputation: 860

SQL | JOIN USING vs JOIN ON

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:

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

Upvotes: 17

Views: 19578

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 21

Related Questions