Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Working with some code on a standard MySQL database, and I came across this statement:

SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1

What's throwing me is: what is USING for, what does it do?

My first assumption was that it involved working with foreign keys or possibly indexes, but the DB schema has no indications of any foreign keys or indexes, period.
My googlefu has failed me, probably because USING is such a common word in most text on sites out there. So I wonder if the DB gurus hear could shed some light on what this does before I look to modify this and multiple other queries in this code.

share|improve this question

2 Answers 2

What is also worth pointing out, is that if you are using USING, then you might get a different result set as from a JOIN. Read the below cited section on the JOIN documentation:

Join Processing Changes in MySQL 5.0.12

Note:

Natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions (prior to 5.0.12) must be rewritten to comply with the standard.

These changes have five main aspects:

  • The way that MySQL determines the result columns of NATURAL or USING join operations (and thus the result of the entire FROM clause).

  • Expansion of SELECT * and SELECT tbl_name.* into a list of selected columns.

  • Resolution of column names in NATURAL or USING joins.

  • Transformation of NATURAL or USING joins into JOIN ... ON.

  • Resolution of column names in the ON condition of a JOIN ... ON.

The most interesting change is maybe that (now citing from the documentation)

LEFT JOIN b USING (c1,c2,c3)

LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

are no longer quite the same, with respect to determining which columns to display for SELECT * expansion. The USING join selects the coalesced value of corresponding columns, whereas the ON join selects all columns from all tables. For the preceding USING join, SELECT * selects these values:

COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)

For the ON join, SELECT * selects these values:

a.c1, a.c2, a.c3, b.c1, b.c2, b.c3

With an inner join, COALESCE(a.c1,b.c1) is the same as either a.c1 or b.c1 because both columns will have the same value. With an outer join (such as LEFT JOIN), one of the two columns can be NULL. That column will be omitted from the result.

share|improve this answer
    
Nice perspective, and +1 !!! –  RolandoMySQLDBA Sep 12 '12 at 7:45
1  
Another reason for not using SELECT * –  ypercube Sep 12 '12 at 14:38

In the context of your query, USING helps satisfy a JOIN so long as the two tables involved in the JOIN have the same column names to join with. It is like doing a NATURAL JOIN.

Your query

SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1

works the same as

SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.bar_id WHERE foo_id = 1

According to the MySQL Documentation on JOIN syntax

The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:

a LEFT JOIN b USING (c1,c2,c3)

The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.

share|improve this answer
    
Hmm intresting. I'm more familiar with the latter syntax, but curious what would be the benefit of using USING instead of ON, since they would seem to perform the same function. Just less typing with USING? –  canadiancreed Sep 11 '12 at 15:23
    
I guess USING serves as a reminder of the common columns that relate one table to another. That's what makes NATURAL JOIN an option to use. –  RolandoMySQLDBA Sep 11 '12 at 15:25
    
I think you can do SELECT bar_id if you are using "USING", but you need to do SELECT foo.bar_id if you are using "ON". –  Rick James Sep 12 '12 at 0:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.