What is the purpose of the USING parameter in SQL?

Question

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.

Answer 1

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.

Answer 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.