What's the difference between “using” and “on”

Question

Is this

... T1 join T2 using(ID) where T2.VALUE=42 ...

the same as

... T1 join T2 on(T1.ID=T2.ID) where T2.VALUE=42 ...

for all types of joins?

My understanding of using(ID) is that it's just shorthand for on(T1.ID=T2.ID). Is this true?

Now for another question:

Is the above the same as

... T1 join T2 on(T1.ID=T2.ID and T2.VALUE=42) ...

This I don't think is true, but why? How does conditions in the on clause interact with the join vs if its in the where clause?

Answer 1

The USING clause is shorthand for an equi-join of columns, assuming the columns exist in both tables by the same name:

A JOIN B USING (column1)

A JOIN B ON A.column1=B.column1

You can also name multiple columns, which makes joins on compound keys pretty straightforward. The following joins should be equivalent:

A JOIN B USING (column1, column2)

A JOIN B ON A.column1=B.column1 AND A.column2=B.column2

Note that USING (<columnlist>) is required to have parentheses, whereas ON <expr> is not required to have parentheses (although parens may be used around <expr> just they may be included around an expression in any other context).

Also, no other tables joined in the query may have a column by that name, or else the query is ambiguous and you should get an error.

Regarding you question about additional conditions, assuming you use an INNER JOIN it should logically give the same result from the query, but the optimization plan may be affected, depending on the RDBMS implementation. Also OUTER JOIN gives a different result if you include conditions in the join versus the WHERE clause.

Answer 2

Your interpretation seems correct. This article may help.

As for the second question, I can't see why the result of your third example should be different from that of the first two. Any condition in an 'ON' clause has the same meaning as if it was in a 'WHERE' clause.

Answer 3

You got the answers here, I need not add to it. Once I did a performance test on this, and USING consistently and always ran faster than ON. Yes I am talking about 10 to 20 ms :) MySQL I am talking about

Answer 4

I believe you are correct - USING(xx) is short hand for joining on two columns with identical names.

As for the second question, both queries could be same or may be different depending upon the query planner implementation specific to the database. To find out for yourself (at least in postgres) do an EXPLAIN SELECT ... to see how the query plans will be executed.

Answer 5

There is a difference in the result that I don't see mentioned in the other answers. If you do this:

 JOIN ... ON t1.common = t2.common

then the result set will have two columns named common, specifically t1.common and t2.common, and trying to reference the unqualified name common will cause the query to be rejected as ambiguous (even though both columns necessarily contain the same value).

If, on the other hand, you do this:

 JOIN ... USING (common)

then the result set will have only one column named common, and it will be an unqualified name - neither t1.common nor t2.common will be present.

Answer 6

If there is only one join then there is no difference.

Downside to the using clause is both tables must have the same column name.