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

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?

That's my understanding of using(ID), its just a short hand 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

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 2

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 3

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.

Answer 4

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 5

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.