MySQL JOIN: When to Use ON vs. USING?

MySQL JOIN ON vs USING: Syntax Difference and Use Cases

When joining tables in MySQL, you have the option to use either the ON or USING keywords. At first glance, USING may seem like a more convenient syntax compared to ON. However, there are subtle differences that make each keyword suited for specific scenarios.

ON: Greater Flexibility

ON provides greater flexibility as it allows you to join tables on a variety of conditions, including:

• A single column
• A set of columns
• A complex condition

For example, you can join tables based on a comparison between two columns or even on an expression:

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

USING: Simplicity and Convenience

USING is specifically designed for joining tables that share an exact match on a column with the same name. It simplifies the syntax by allowing you to specify the common column name instead of using the ON clause:

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

One significant advantage of USING is that it eliminates the need to fully qualify the joining columns in the SELECT clause:

SELECT film.title, film_id
-- No prefix for film_id required
FROM film
JOIN film_actor
USING (film_id)
WHERE ...

ON vs USING: Practical Examples

To illustrate the difference between the two keywords, consider the following examples:

Example with ON:

SELECT film.title, film.film_id -- Prefixing film.film_id required
FROM film
JOIN film_actor
ON (film.film_id = film_actor.film_id)
WHERE ...

In this example, ON is used because the columns to be joined are not exactly the same (film.film_id vs film_actor.film_id). Prefixing film.film_id in the SELECT clause is necessary to avoid ambiguity.

Example with USING:

SELECT *
FROM t
JOIN t2
USING (i);

In this example, USING is used because the joining column (i) is identical in both tables and has the same name. It simplifies the syntax and automatically includes the common column in the result set once.

Conclusion

While USING may appear as a more convenient syntax, ON offers greater flexibility in terms of join conditions and allows for more complex queries. Choose ON when comparing columns with different names or applying complex conditions. Use USING when joining tables with identical column names, especially when the join columns are part of the result set.

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