natural - mysql join types

MySQL ON vs USING?

Question

En MySQL JOIN , ¿cuál es la diferencia entre ON y USING() ? Por lo que puedo decir, USING() es una sintaxis más conveniente, mientras que ON permite un poco más de flexibilidad cuando los nombres de las columnas no son idénticos. Sin embargo, esa diferencia es tan pequeña que uno pensaría que acabarían con USING() .

¿Hay más en esto de lo que parece? En caso afirmativo, ¿qué debo usar en una situación dada?

Answer 1

Es sobre todo azúcar sintáctica, pero hay algunas diferencias notables:

ON es el más general de los dos. Uno puede unir tablas en una columna, un conjunto de columnas e incluso una condición. Por ejemplo:

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

USAR es útil cuando ambas tablas comparten una columna con el mismo nombre exacto en el que se unen. En este caso, se puede decir:

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

Un buen tratamiento adicional es que uno no necesita calificar completamente las columnas de unión:

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

Para ilustrar, para hacer lo anterior con ON , tendríamos que escribir:

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 ...

Observe la calificación film.film_id en la cláusula SELECT . No sería válido simplemente decir film_id ya que eso daría lugar a una ambigüedad:

ERROR 1052 (23000): la columna ''film_id'' en la lista de campos es ambigua

En cuanto a select * , la columna de unión aparece en el conjunto de resultados dos veces con ON mientras que aparece solo una vez con 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>

Answer 2

Para aquellos que experimentan con esto en phpMyAdmin, solo una palabra:

phpMyAdmin parece tener algunos problemas con el uso. Para el registro, esto es phpMyAdmin ejecutado en Linux Mint, versión: "4.5.4.1deb2ubuntu2", Servidor de base de datos: "10.2.14-MariaDB-10.2.14 + maria ~ xenial - mariadb.org distribución binaria".

He ejecutado comandos SELECT utilizando JOIN y USING tanto en phpMyAdmin como en Terminal (línea de comando), y los de phpMyAdmin producen algunas respuestas desconcertantes:

1) una cláusula LIMIT al final parece ser ignorada.
2) el número supuesto de filas como se informó en la parte superior de la página con los resultados a veces es incorrecto: por ejemplo, se devuelven 4, pero en la parte superior dice "Mostrando las filas 0 a 24 (2503 en total, la consulta tomó 0.0018 segundos). "

Iniciar sesión en mysql normalmente y ejecutar las mismas consultas no produce estos errores. Tampoco se producen estos errores al ejecutar la misma consulta en phpMyAdmin usando JOIN ... ON ... Presumiblemente un error phpMyAdmin.

Answer 3

Pensé que me pondría aquí con cuando me hubiera parecido que ON era más útil que USING . Es cuando las uniones OUTER se introducen en las consultas.

ON beneficia de permitir que se restrinja el conjunto de resultados de la tabla a la que se une una consulta OUTER mientras se mantiene la combinación OUTER . Intentar restringir el conjunto de resultados mediante la especificación de una cláusula WHERE , efectivamente, cambiará la combinación OUTER en una INNER .

Concedido esto puede ser un caso relativo de la esquina. Vale la pena poner allí, aunque ...

Por ejemplo:

CREATE TABLE country (
countryId int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
country varchar(50) not null,
UNIQUE KEY countryUIdx1 (country)
) ENGINE=InnoDB;
insert into country(country) values ("France");
insert into country(country) values ("China");
insert into country(country) values ("USA");
insert into country(country) values ("Italy");
insert into country(country) values ("UK");
insert into country(country) values ("Monaco");
CREATE TABLE city (
cityId int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
countryId int(10) unsigned not null,
city varchar(50) not null,
hasAirport boolean not null default true,
UNIQUE KEY cityUIdx1 (countryId,city),
CONSTRAINT city_country_fk1 FOREIGN KEY (countryId) REFERENCES country (countryId)
) ENGINE=InnoDB;
insert into city (countryId,city,hasAirport) values (1,"Paris",true);
insert into city (countryId,city,hasAirport) values (2,"Bejing",true);
insert into city (countryId,city,hasAirport) values (3,"New York",true);
insert into city (countryId,city,hasAirport) values (4,"Napoli",true);
insert into city (countryId,city,hasAirport) values (5,"Manchester",true);
insert into city (countryId,city,hasAirport) values (5,"Birmingham",false);
insert into city (countryId,city,hasAirport) values (3,"Cincinatti",false);
insert into city (countryId,city,hasAirport) values (6,"Monaco",false);
-- Gah. Left outer join is now effectively an inner join
-- because of the where predicate
select *
from country left join city using (countryId)
where hasAirport
;
-- Hooray! I can see Monaco again thanks to
-- moving my predicate into the ON
select *
from country co left join city ci on (co.countryId=ci.countryId and ci.hasAirport)
;

Answer 4

Wikipedia tiene la siguiente información sobre el uso:

Sin embargo, la construcción USING es más que un mero azúcar sintáctico, ya que el conjunto de resultados difiere del conjunto de resultados de la versión con el predicado explícito. Específicamente, cualquier columna mencionada en la lista de USO aparecerá solo una vez, con un nombre no calificado, en lugar de una vez para cada tabla en la unión. En el caso anterior, habrá una sola columna DepartmentID y ningún empleado.DepartmentID o department.DepartmentID.

Tablas de las que se hablaba:

La documentación de Postgres también los define bastante bien:

La cláusula ON es el tipo más general de condición de unión: toma una expresión de valor booleano del mismo tipo que la utilizada en una cláusula WHERE. Un par de filas de T1 y T2 coinciden si la expresión ON se evalúa como verdadera.

La cláusula USING es una abreviatura que le permite aprovechar la situación específica en la que ambos lados de la unión utilizan el mismo nombre para la (s) columna (s) de unión. Toma una lista separada por comas de los nombres de columna compartidos y forma una condición de unión que incluye una comparación de igualdad para cada uno. Por ejemplo, la unión de T1 y T2 con el USO (a, b) produce la condición de unión EN T1.a = T2.a y T1.b = T2.b.

Además, la salida de USO ÚNICO suprime las columnas redundantes: no hay necesidad de imprimir ambas columnas coincidentes, ya que deben tener valores iguales. Mientras que JOIN ON produce todas las columnas de T1 seguidas de todas las columnas de T2, JOIN USING produce una columna de salida para cada uno de los pares de columnas enumerados (en el orden listado), seguido de las columnas restantes de T1, seguidas de las columnas restantes de T2 .