Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL | JOIN USING vs JOIN ON

Tags:

sql

join

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

like image 940
Bruno Zamengo Avatar asked Aug 06 '17 11:08

Bruno Zamengo


People also ask

What is the difference between on and using join clause?

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.

Can SQL join use in on?

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.

Why we use on in joins?

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 join more efficient than WHERE?

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


1 Answers

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.

like image 59
Gordon Linoff Avatar answered Oct 18 '22 05:10

Gordon Linoff