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?
I don't use the USING syntax, since
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.
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.
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.
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.
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.
