What's the difference between "using" and "on" in table joins in MySQL?

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

I don't use the USING syntax, since

1. most of my joins aren't suited to it (not the same fieldname that is being matched, and/or multiple matches in the join) and
2. it isn't immediately obvious what it translates to in the case with more than two tables

ie assuming 3 tables with 'id' and 'id_2' columns, does

T1 JOIN T2 USING(id) JOIN T3 USING(id_2)

become

T1 JOIN T2 ON(T1.id=T2.id) JOIN T3 ON(T1.id_2=T3.id_2 AND T2.id_2=T3.id_2)

or

T1 JOIN T2 ON(T1.id=T2.id) JOIN T3 ON(T2.id_2=T3.id_2)

or something else again?

Finding this out for a particular database version is a fairly trivial exercise, but I don't have a large amount of confidence that it is consistent across all databases, and I'm not the only person that has to maintain my code (so the other people will also have to be aware of what it is equivalent to).

An obvious difference with the WHERE vs ON is if the join is outer:

Assuming a T1 with a single ID field, one row containing the value 1, and a T2 with an ID and VALUE field (one row, ID=1, VALUE=6), then we get:

SELECT T1.ID, T2.ID, T2.VALUE FROM T1 LEFT OUTER JOIN T2 ON(T1.ID=T2.ID) WHERE T2.VALUE=42

gives no rows, since the WHERE is required to match, whereas

SELECT T1.ID, T2.ID, T2.VALUE FROM T1 LEFT OUTER JOIN T2 ON(T1.ID=T2.ID AND T2.VALUE=42)

will give one row with the values

1, NULL, NULL

since the ON is only required for matching the join, which is optional due to being outer.

Answer 2

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.