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:
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".
The USING clause: This allows you to specify the join key by name. The ON clause: This syntax allows you to specify the column names for join keys in both tables.
Yes. ON should be used to define the join condition and WHERE should be used to filter the data. I used the word should because this is not a hard rule.
The purpose of the ON clause is to specify the join conditions, that is, to define how the tables should be joined. Specifically, you define how the records should be matched.
“Is there a performance difference between putting the JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there's no difference. The following queries are algebraically equivalent inside MySQL and will have the same execution plan.
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 join
s, 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 join
s 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With