sql – What is the difference between using and on in join requests?

Question:

Subject in the title, i.e. are queries equivalent (if mysql is considered)

SELECT * FROM `tab1` RIGHT JOIN `tab` ON `table2`.`id` = `table1`.`id`  

and

SELECT * FROM `tab1` RIGHT JOIN `tab2` USING(`id`)

If not, which one is better and what is the difference?

Answer:

USING (column_name(s)) is essentially syntactic sugar over ON . According to the docs , it is used to specify a list of columns that should exist in both tables.

A USING expression such as:

A LEFT JOIN B USING (C1, C2, C3, ...)

semantically identical to the ON expression:

A.C1 = B.C1 AND A.C2 = B.C2 AND A.C3 = B.C3,...

While ON can "glue" columns with different names.

But with ON , you can do a little more operations, for example, you can attach not only a column, but also a set of columns or even a whole condition, for example:

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

---

Additionally

USING – when listing fields in a query, it is not necessary to specify a prefix:

SELECT film.title, film_id  // # film_id указан без префикса
FROM film
JOIN film_actor USING (film_id)
WHERE ...

Same with ON :

SELECT film.title, film.film_id // # film.film_id обязателен префикс
FROM film
JOIN film_actor ON (film.film_id = film_actor.film_id)
WHERE ...

If you do not list the fields explicitly, but use select * to connect columns, then in the result set with ON the column will "pop up" twice, while with USING – only once:

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>

---

_{info borrowed from enSO MySQL ON vs USING?}