MySQL ON vs usando?
num MySQL {[[0]}, Qual é a diferença entre ON e USING()? Tanto quanto posso dizer, USING() é apenas uma sintaxe mais conveniente, enquanto ON permite um pouco mais de flexibilidade quando os nomes das colunas não são idênticos. No entanto, essa diferença é tão pequena, que seria de esperar que eles acabassem com USING().
4 answers
Em é o mais geral dos dois. Pode-se juntar tabelas em uma coluna, um conjunto de colunas e até mesmo uma condição. Por exemplo:
SELECT * FROM world.City JOIN world.Country ON (City.CountryCode = Country.Code) WHERE ...
A utilização de é útil quando ambas as tabelas partilham uma coluna com o mesmo nome exacto em que se juntam. Neste caso, pode-se dizer:
SELECT ... FROM film JOIN film_actor USING (film_id) WHERE ...
SELECT film.title, film_id # film_id is not prefixed
FROM film
JOIN film_actor USING (film_id)
WHERE ...
Para ilustrar, para fazer o acima com EM, teríamos de escrever:
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 ...
Nota a qualificação na cláusula SELECT. Seria inválido dizer apenas film_id uma vez que isso faria com que uma ambiguidade:
Erro 1052 (23000): a coluna 'film_id' na lista de Campos é ambígua
Quanto a select *, a coluna de junção aparece no resultado definido duas vezes com ON, enquanto aparece apenas uma vez com 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>
Pensei em vir aqui quando descobri que ON era mais útil do que USING. É quando as juntas OUTER são introduzidas em consultas.
ON beneficia de permitir que o conjunto de resultados da tabela em que uma consulta é OUTER junta para ser restrita, mantendo a junção OUTER. A tentativa de restringir os resultados definidos através da especificação de uma cláusula WHERE irá, efectivamente, alterar a junção OUTER para uma junção INNER.
Por exemplo:
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)
;
O Wikipedia tem a seguinte informação sobre USING:
O uso da construção é mais do que simples açúcar sintático, no entanto, uma vez que o conjunto de resultados difere do conjunto de resultados da versão com o predicado explícito. Especificamente, quaisquer colunas mencionadas no uso a lista aparecerá apenas uma vez, com um nome não qualificado, em vez de uma vez para cada mesa na junção. No caso acima, haverá um único Coluna da partida e número Funcionario.DepartmentID ou Departamento.DepartmentID.
Mesas de que estava a falar:
A documentação Postgres também os define muito bem:
{[2] a cláusula ON é o tipo mais geral de condição de adesão: é preciso um Expressão do valor booleano do mesmo tipo que é usada em um onde clausula. Um par de linhas de T1 e T2 correspondem se a expressão ON é verdade.A cláusula de utilização é uma abreviatura que permite - lhe aproveitar a situação específica em que ambos os lados da junção utilizam o mesmo nome para a(s) coluna (s) de junção. É necessária uma lista separada por vírgulas da os nomes das colunas partilhadas e forma uma condição de junção que inclui um comparação de igualdade para cada um. Por exemplo, juntar T1 e T2 com A utilização de (A, b) produz a condição de junção em T1.a = T2.a e T1.B = T2.B.
Além disso, o resultado da junção com a aplicação suprime colunas redundantes: não há necessidade de imprimir ambas as colunas emparelhadas, uma vez que devem ter valores iguais. Enquanto a junção produz todas as colunas a partir do T1 seguido por todas as colunas do T2, O JOIN usando produz uma coluna de saída para cada dos pares de colunas enumerados (na ordem enumerada), seguidos de qualquer colunas restantes do T1, seguidas das restantes colunas do T2.
O PhpMyAdmin parece ter alguns problemas com {[[0]}. Para o registro este é o phpMyAdmin executado no Linux Mint, versão: "4.5.4. 1deb2ubuntu2", servidor de banco de dados: "10.2.14-MariaDB-10.2.14+maria~xenial - mariadb.org binária distribuição".
Executei os comandos SELECT Usando JOIN e USING tanto no phpMyAdmin como no Terminal (linha de comandos), e os do phpMyAdmin produzem alguma confusão Respostas:
1) a cláusula LIMIT no final parece ser ignorada.
2) o suposto número de linhas como relatado no topo da página com os resultados é às vezes errado: por exemplo 4 são retornados, mas no topo diz "mostrando linhas 0 - 24 (2503 total, consulta levou 0,0018 segundos.)"
Ligar-se ao mysql normalmente e executar as mesmas consultas não produz estes erros. Nem estes erros ocorrem ao executar a mesma consulta em phpMyAdmin usando JOIN ... ON .... Presumivelmente um insecto phpMyAdmin.