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

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.