CODEWITHSUNDEEP
mysqlsyntaxjoin
Ken
Ken

Reputation: 78922

Is there a disadvantage to using "USING" instead of "ON" in MySQL?

Two snippets of MySQL: 

SELECT * FROM annoyingly_long_left_hand_table 
    LEFT JOIN annoyingly_long_right_hand_table 
    ON annoyingly_long_left_hand_table.id = annoyingly_long_right_hand_table.id;

vs

SELECT * FROM annoyingly_long_left_hand_table 
    LEFT JOIN annoyingly_long_right_hand_table 
    USING (id);

Given that both tables have an id field, is there any disadvantage to using the second version. It isn't just laziness - the version with USING seems far clearer to me.

(Please don't mention aliasing - I want to know if there is any reason to favour one conditional structure over the other)

Upvotes: 2

Views: 621

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562931

USING is a bit less flexible than a general-purpose ON clause:

  • The column(s) in both tables must have the same name.

  • The comparison must be equality.

  • No other table in the query can have a column of the same name.

Upvotes: 1

Kent Fredric
Kent Fredric

Reputation: 57414

There is a small functional difference between the two, in that instead of getting 2 ID columns, you only get one.

SELECT * FROM foo JOIN bar ON ( foo.id == bar.id )

id , fooname, id, barname

SELECT * FROM foo JOIN bar USING ( id )

id, fooname, barname

So there are at least some additional knowledge requirements for users utilising this syntax, http://dev.mysql.com/doc/refman/5.0/en/join.html

Upvotes: 6

Related Questions