MySQL JOIN: ON vs. USING – When Should I Use Which?

MySQL JOIN: ON vs. USING

MySQL's JOIN statement provides two syntax options for joining tables: ON and USING. While USING may appear to be merely a more concise alternative to ON, there are subtle differences that warrant consideration.

Differences between ON and USING

• Flexibility: ON offers greater flexibility as it allows joins on a column, multiple columns, or even complex conditions. For instance:

SELECT * FROM world.City JOIN world.Country ON (City.CountryCode = Country.Code) WHERE ...

• Column Naming: USING is most useful when the tables being joined share an identically named common column. In such cases, the column name can be omitted:

SELECT ... FROM film JOIN film_actor USING (film_id) WHERE ...

Unlike ON, USING does not require fully qualified column names in the SELECT list or WHERE clause:

SELECT film.title, film_id -- film_id not prefixed
FROM film
JOIN film_actor USING (film_id)
WHERE ...

Specific Use Cases

• Use ON: When joining on multiple columns, conditions, or non-identical column names.
• Use USING: When joining tables with an identically named common column for simplicity and performance.

Ambiguity and SELECT *

When using ON to join tables, the joined column appears twice in the result set. With USING, it appears only once. This can lead to ambiguity issues when selecting all columns with SELECT *.

The above is the detailed content of MySQL JOIN: ON vs. USING – When Should I Use Which?. For more information, please follow other related articles on the PHP Chinese website!