MySQL JOIN ON vs USING ?

Question

Dans une base de données MySQL JOIN Quelle est la différence entre ON y USING() ? D'après ce que je sais, USING() est simplement une syntaxe plus pratique, alors que ON permet un peu plus de flexibilité lorsque les noms des colonnes ne sont pas identiques. Cependant, cette différence est si mineure que l'on pourrait penser qu'ils ont tout simplement supprimé la fonction USING() .

Y a-t-il plus que ce que l'on peut voir ? Si oui, lequel dois-je utiliser dans une situation donnée ?

Answer 1

Il s'agit principalement de sucre syntaxique, mais quelques différences sont à noter :

ON est le plus général des deux. On peut joindre des tables sur une colonne, un ensemble de colonnes et même une condition. Par exemple :

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

UTILISER est utile lorsque les deux tables partagent une colonne du même nom sur laquelle elles se rejoignent. Dans ce cas, on peut dire :

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

Un autre avantage est qu'il n'est pas nécessaire de qualifier complètement les colonnes de jonction :

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

Pour illustrer, pour faire ce qui précède avec ON nous devrions écrire :

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

Remarquez le film.film_id qualification dans le SELECT clause. Il serait invalide de dire simplement film_id car cela créerait une ambiguïté :

ERREUR 1052 (23000) : La colonne 'film_id' dans la liste des champs est ambiguë.

Quant à *`select ** la colonne de jonction apparaît deux fois dans le jeu de résultats avec la mentionONalors qu'il n'apparaît qu'une seule fois avecUSING` :

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

J'ai pensé que je pourrais vous faire part de ce que j'ai trouvé. ON pour être plus utile que USING . C'est quand OUTER Les jointures sont introduites dans les requêtes.

ON bénéficie de l'autorisation du jeu de résultats de la table sur laquelle porte la requête. OUTER l'adhésion sur à restreindre tout en maintenant la OUTER rejoindre. Tenter de restreindre l'ensemble des résultats en spécifiant une WHERE modifiera effectivement le OUTER se rejoignent dans un INNER rejoindre.

D'accord, il s'agit peut-être d'un cas particulier. Mais cela vaut la peine d'en parler.....

Par exemple :

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 3

Wikipedia dispose des informations suivantes sur USING :

La construction USING est plus qu'une simple suggestion syntaxique. l'ensemble de résultats diffère de l'ensemble de résultats de la version avec le prédicat explicite. Plus précisément, toutes les colonnes mentionnées dans la liste USING n'apparaîtra qu'une seule fois, avec un nom non qualifié, plutôt qu'une fois pour chaque table dans la jointure. Dans le cas ci-dessus, il y aura une seule colonne DepartmentID et aucune colonne employee.DepartmentID ou department.departmentID.

Les tables dont il était question :

Le site Postgres documentation les définit également assez bien :

La clause ON est le type le plus général de condition de jointure : elle prend une expression de valeur booléenne du même type que celle utilisée dans une clause WHERE d'une clause WHERE. Une paire de rangées de T1 et T2 correspond si l'expression ON vaut true.

La clause USING est un raccourci qui vous permet de tirer parti de l'utilisation de la situation spécifique où les deux côtés de la jointure utilisent le même nom pour la ou les colonnes de jonction. Elle prend une liste de noms de colonnes partagées, séparées par des virgules, et forme une condition de jointure qui inclut un critère de sélection. séparée par des virgules et forme une condition de jointure qui inclut une condition d'égalité comparaison d'égalité pour chacune d'entre elles. Par exemple, la jointure de T1 et T2 avec USING (a, b) produit la condition de jointure ON T1.a = T2.a AND T1.b = T2.b.

En outre, la sortie de JOIN USING supprime les colonnes redondantes : il n'est pas nécessaire d'imprimer les deux colonnes correspondantes, puisqu'elles doivent avoir des valeurs égales. Alors que JOIN ON produit toutes les colonnes de T1 suivies de toutes les colonnes de T2, JOIN USING produit toutes les colonnes de T2. suivies de toutes les colonnes de T2, JOIN USING produit une colonne de sortie pour chaque pour chacune des paires de colonnes répertoriées (dans l'ordre), suivie de toutes les colonnes restantes de T1, suivies de toutes les colonnes restantes de T2.

Answer 4

Tables de base de données

Pour montrer comment fonctionnent les clauses USING et ON, supposons que nous ayons l'élément suivant post y post_comment des tables de la base de données, qui forment une relation de table un-à-plusieurs par l'intermédiaire de la fonction post_id colonne Clé étrangère dans le post_comment qui fait référence à la table post_id Colonne de clé primaire dans le post table :

Le parent post a 3 rangées :

| post_id | title     |
|---------|-----------|
| 1       | Java      |
| 2       | Hibernate |
| 3       | JPA       |

et le post_comment La table enfant contient les 3 enregistrements :

| post_comment_id | review    | post_id |
|-----------------|-----------|---------|
| 1               | Good      | 1       |
| 2               | Excellent | 1       |
| 3               | Awesome   | 2       |

La clause JOIN ON utilisant une projection personnalisée

Traditionnellement, lors de l'écriture d'un INNER JOIN o LEFT JOIN nous utilisons la clause ON pour définir la condition de jointure.

Par exemple, pour obtenir les commentaires ainsi que le titre et l'identifiant de l'article qui leur sont associés, nous pouvons utiliser la requête de projection SQL suivante :

SELECT
   post.post_id,
   title,
   review
FROM post
INNER JOIN post_comment ON post.post_id = post_comment.post_id
ORDER BY post.post_id, post_comment_id

Et nous obtenons l'ensemble des résultats suivants :

| post_id | title     | review    |
|---------|-----------|-----------|
| 1       | Java      | Good      |
| 1       | Java      | Excellent |
| 2       | Hibernate | Awesome   |

La clause JOIN USING utilisant une projection personnalisée

Lorsque la colonne de la clé étrangère et la colonne qu'elle référence ont le même nom, nous pouvons utiliser la clause USING, comme dans l'exemple suivant :

SELECT
  post_id,
  title,
  review
FROM post
INNER JOIN post_comment USING(post_id)
ORDER BY post_id, post_comment_id

Et, le jeu de résultats pour cette requête particulière est identique à la requête SQL précédente qui a utilisé la clause ON :

| post_id | title     | review    |
|---------|-----------|-----------|
| 1       | Java      | Good      |
| 1       | Java      | Excellent |
| 2       | Hibernate | Awesome   |

La clause USING fonctionne pour Oracle, PostgreSQL, MySQL et MariaDB. Le serveur SQL ne prend pas en charge la clause USING, vous devez donc utiliser la clause ON à la place.

La clause USING peut être utilisée avec les instructions INNER, LEFT, RIGHT et FULL JOIN.

Clause SQL JOIN ON avec SELECT *

Maintenant, si nous changeons la requête précédente de la clause ON pour sélectionner toutes les colonnes en utilisant SELECT * :

SELECT *
FROM post
INNER JOIN post_comment ON post.post_id = post_comment.post_id
ORDER BY post.post_id, post_comment_id

Nous allons obtenir l'ensemble des résultats suivants :

| post_id | title     | post_comment_id | review    | post_id |
|---------|-----------|-----------------|-----------|---------|
| 1       | Java      | 1               | Good      | 1       |
| 1       | Java      | 2               | Excellent | 1       |
| 2       | Hibernate | 3               | Awesome   | 2       |

Comme vous pouvez le voir, le post_id es dupliqué car les deux post y post_comment Les tableaux contiennent un post_id colonne.

Clause SQL JOIN USING avec SELECT *

D'autre part, si nous exécutons un SELECT * qui comporte la clause USING pour la condition JOIN :

SELECT *
FROM post
INNER JOIN post_comment USING(post_id)
ORDER BY post_id, post_comment_id

Nous obtiendrons l'ensemble des résultats suivants :

| post_id | title     | post_comment_id | review    |
|---------|-----------|-----------------|-----------|
| 1       | Java      | 1               | Good      |
| 1       | Java      | 2               | Excellent |
| 2       | Hibernate | 3               | Awesome   |

Vous pouvez voir que cette fois, le post_id est dédupliquée, de sorte qu'il n'existe qu'une seule colonne post_id la colonne étant incluse dans le jeu de résultats.

Conclusion

Si le schéma de la base de données est conçu de telle sorte que les noms des colonnes de clés étrangères correspondent aux colonnes qu'elles référencent, et que les conditions de JOIN vérifient uniquement si la valeur de la colonne de clé étrangère est égale à la valeur de sa colonne miroir dans l'autre table, vous pouvez alors utiliser la clause USING.

Sinon, si le nom de la colonne de la clé étrangère diffère de celui de la colonne de référence ou si vous voulez inclure une condition de jointure plus complexe, vous devez utiliser la clause ON à la place.

Answer 5

Pour ceux qui expérimentent avec ceci dans phpMyAdmin, juste un mot :

phpMyAdmin semble avoir quelques problèmes avec USING . Pour mémoire, il s'agit de phpMyAdmin exécuté sur Linux Mint, version : "4.5.4.1deb2ubuntu2", serveur de base de données : "10.2.14-MariaDB-10.2.14+maria~xenial - distribution binaire mariadb.org".

J'ai couru SELECT à l'aide de JOIN y USING à la fois dans phpMyAdmin et dans Terminal (ligne de commande), et celles de phpMyAdmin produisent des réponses déroutantes :

1) a LIMIT à la fin semble être ignorée.
2) le nombre supposé de lignes tel qu'il est indiqué en haut de la page contenant les résultats est parfois erroné : par exemple, 4 lignes sont renvoyées, mais en haut de la page, il est indiqué "Showing rows 0 - 24 (2503 total, Query took 0.0018 seconds.)".

En se connectant normalement à mysql et en exécutant les mêmes requêtes, ces erreurs ne se produisent pas. Ces erreurs ne se produisent pas non plus lorsque l'on exécute la même requête dans phpMyAdmin en utilisant JOIN ... ON ... . Probablement un bug de phpMyAdmin.