What is the purpose of the USING parameter in SQL?

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.

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.

StackExchange Database Administrators Q#24124, answer score: 8