Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (6.1k points)

How can you differentiate between ON and USING() in MySQL join method? What all I know is that USING() is a little bit more convenient syntax wise whereas ON is flexible when there are no identical column names. 

If there is more to it, please explain.

1 Answer

0 votes
by (11.7k points)

You should note down few differences of MySQL ON and USING:

People use ON most commonly. It can be used to join tables ON a column, a set of columns, and even a condition.

For Instance:

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

USING comes in handy when both tables share a column of the exact same name on which they join. It can be said:

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

One more thing is that it does not need to fully qualify the joining columns:

SELECT film.title, film_id -- film_id is not prefixed

FROM film

JOIN film_actor USING (film_id)

WHERE ...

If  you want to do it with ON:

 SELECT film.title, film.film_id -- film.film_id is required here

FROM film

JOIN film_actor ON (film.film_id = film_actor.film_id)

WHERE …

Notice the film.film_id qualification in the SELECT clause. This would be wrong to say that film_id since that would make for an ambiguity:

ERROR 1052 (23000): Column 'film_id' in field list is ambiguous

As for select *, the joining column displays in the result set twice with ON while it appears only once with USING:

mysql> create table t(i int);insert t select 1;create table t2 select*from t;

Query OK, 0 rows affected (0.11 sec)

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.19 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> select*from t join t2 on t.i=t2.i;

+------+------+

| i    | i    |

+------+------+

|    1 |    1 |

+------+------+

1 row in set (0.00 sec)

mysql> select*from t join t2 using(i);

+------+

| i    |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

mysql>

Related questions

0 votes
4 answers
0 votes
1 answer
asked Oct 19, 2020 in SQL by dev_sk2311 (45k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Browse Categories

...