Les jointures

Les jointures sont un moyen de consulter les informations provenant de plusieurs tables en même temps. Pour spécifier une jointure dans un SELECT il suffit de préciser plusieurs tables dans la clause FROM.

SELECT liste_colonnes FROM table1, table2
/* fait une jointure entre table 1 et table2 */

Jointure brute

Une jointure brute fait un produit cartésien entre les données de T1 et T2, c’est à dire associe toutes les colonnes de chaque ligne de T1, à toutes les colonnes de chaque ligne de T2, en effectuant toutes les combinaisons possibles.
Ainsi une jointure brute sur la table ‘PET’ et la table ‘EVENT’ renvoie 90 lignes de 10 colonnes (6 colonnes de la table PET, accolées à 4 colonnes de la table EVENT)

mysql> SELECT COUNT(*) FROM pet;
mysql> 9
mysql> SELECT COUNT(*) FROM event;
mysql> 10
mysql> SELECT COUNT(*) FROM pet, event;
mysql> 90
mysql> select * from pet,event limit 2;
+———-+——–+———+——+————+————+———-+———–+———-+—————————–+
| name | owner | species | sex | birth | death | name | date
| type | remark |
+———-+——–+———+——+————+————+———-+——–+———-+—————————–+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL | Fluffy | 1995
-05-15 | litter | 4 kittens, 3 female, 1 male |
| Claws | Gwen | cat | m | 1994-03-17 | NULL | Fluffy | 1995
-05-15 | litter | 4 kittens, 3 female, 1 male |

EquiJointure

Il est évident que les résultats ramenés précédemment ne sont pas très pertinents : des infos qui concernent l’animal ‘Claws’ dans ‘PET’ sont accolées avec des infos qui concernent ‘Fluffy!
Il est donc intéressant de filtrer ce résultat pour ne ramener que les lignes ayant un sens, c’est à dire les lignes où existe une correspondance, celles qui concerne le même animal.
Cette condition est facile à écrire, il suffit de rajouter une clause WHERE dans le SELECT en imposant que le nom d’animal dans ‘PET’ soit égal au nom d’animal dans ‘EVENT’.

Cette égalité nécessaire définit ce qu’on appelle une EQUI JOINTURE.
Cette ‘condition de jointure’, permet de relier les tables sur une collen commune : la clé de jointure. Sur notre table ‘pet’ c’est ‘name’ qui fait le lien et est la clé de jointure.

mysql> select p.name as « nom », birth as « né le », remark « evenement »
-> from pet p, event e
-> where p.name = e.name;
+———-+————+—————————–+
| nom | né le | evenement |
+———-+————+—————————–+
| Fluffy | 1993-02-04 | 4 kittens, 3 female, 1 male |
| Buffy | 1989-05-13 | 5 puppies, 2 female, 3 male |
| Buffy | 1989-05-13 | 3 puppies, 3 female |
| Chirpy | 1998-09-11 | needed beak straightened |
| Slim | 1996-04-29 | broken rib |
| Bowser | 1979-08-31 | NULL |
| Fang | 1990-08-27 | NULL |
| Fang | 1990-08-27 | Gave him a new chew toy |
| Claws | 1994-03-17 | Gave him a new flea collar |
| Whistler | 1997-12-09 | First birthday |
+———-+————+—————————–+

notes : – on évitera de faire des ‘SELECT *’ dans les jointures pour éviter les colonnes redondantes comme ‘name’ ici. On précisera explicitement les colonnes utiles à afficher.

– on verra dans le paragraphe suivant qu’il est possible de mettre la clause de jointure ailleurs que dans la clause WHERE…

Il est bien évidemment possible de rajouter des conditions à la condition de jointure, comme dans tout SELECT. Si l’on veut limiter la requête précédente aux chiens on rajoute la condtion ‘species = « dog »

mysql> select p.name as « nom », birth as « né le », remark « evenement »
-> from pet p, event e
-> where p.name = e.name
-> and species = ‘dog’;
+——–+————+—————————–+
| nom | né le | evenement |
+——–+————+—————————–+
| Buffy | 1989-05-13 | 5 puppies, 2 female, 3 male |
| Buffy | 1989-05-13 | 3 puppies, 3 female |
| Bowser | 1979-08-31 | NULL |
| Fang | 1990-08-27 | NULL |
| Fang | 1990-08-27 | Gave him a new chew toy |
+——–+————+—————————–+

On peut également utiliser les syntaxes INNER JOIN ou NATURAL JOIN pour ce type de jointure.

tests sur les limites ….

Pour faire nos tests nous allons utiliser les tables de démonstration de la DATABASE ‘World’, qui contient des tables sur les pays, villes et langues du monde.

Nous allons nous intéresser au nombre de langues parlées par pays, et + particulièrement aux tables COUNTRY (code, name,…) et COUNTRYLANGUAGE(countrycode, language,…)

Pour avoir des colonnes de jointures de meme nom, on modifie la table en SQL.

ALTER TABLE `country` CHANGE `Code` `countryCode` CHAR( 3 );

Reprenons notre jointure simple, avec condition de jointure dans le WHERE
La condition de jointure doit être forcément vérifiée. On joint sur le code de pays commun aux 2 tables, et on fait un ‘GROUP BY ‘ pays pour pouvoir compter les langues dans chaque groupe :

SELECT name Pays, count(language) « nb langues »
FROM country c, countrylanguage l
WHERE c.countrycode = l.countrycode
GROUP BY name

Pays langues
—– ——-
Afghanistan 5
Albania 3
Algeria 2
Am. Samoa 3
Andorra 4
Angola 9
Anguilla 1
Antigua 2
Argentina 3
Armenia 2
Aruba 4
Australia 8
Austria 8
Azerbaijan 4
Bahamas 2
Bahrain 2
Bangladesh 7
Barbados 2
Belarus 4
… …

 

Maintenant, on limite le resultat aux pays commencant par ‘A’, our y voir + clair dans les tests.
La condition supplémentaire est forcement ajoutée dans le WHERE

SELECT name Pays, count(language) « nb langues »
FROM country c, countrylanguage l
WHERE c.countrycode = l.countrycode
AND name LIKE ‘A%’
GROUP BY name

–> on en a désormais 14. L’Antartique n’apparait pas car la condition de jointure n’est pas satisfaite pour ce pays (son code n’apparait pas dans la tables des langues parlées…).
Nos tests peuvent commencer !

Les jointures externes

Il n’y a parfois pas de correspondance systématique entre les valeurs des colonnes de jointures.
Ainsi l’antartique qui n’a pas d’habitant, n’a pas de langue parlée.
Elle apparait dans la table des pays, mais pas dans celle des langages!
La jointure externe (OUTER JOIN) permet de résoudre ces problèmes en faisant correspondre des lignes qui n’ont pas de valeurs communes. Des lignes de valeurs NULL sont alors rajoutées pur compléter la table déficitaire. A droite dans un LEFT JOIN, à gauche dans un RIGHT JOIN.

Jointure externe, avec clause LEFT JOIN et ON

SELECT name Pays, count(language) « nb langues »
FROM country c LEFT JOIN countrylanguage l
ON c.countrycode = l.countrycode
WHERE name LIKE ‘A%’
GROUP BY name
–> on en a 15 !!! avec l’antartique
— elle ne verifie pas le critere de jointure mais sort qd meme
— avec la jointure externe, qui complete la table de droite ‘l’ avec des NULL

Jointure externe, avec clause LEFT JOIN et ON
en changeant l’ordre des tables !

SELECT name Pays, count(language) « nb langues »
FROM countrylanguage l LEFT JOIN country c
ON c.countrycode = l.countrycode
WHERE name LIKE ‘A%’
GROUP BY name
–> on en a de nouveau 14 !!! sans l’antartique
— c’est la table de gauche qui est deficitaire
— et c’est celle de droite qui est eventuellement complétée avec des NULL
— mais comme il n’y a pas de langues parlée sans pays…

Jointure externe, avec clause RIGHT JOIN et ON
Un RIGHT JOIN est strictement équivallent à un LEFT JOIN, en inversant l’ordre des tables. On utilise en général des LEFT JOIN !

SELECT name Pays, count(language) « nb langues »
FROM country c RIGHT JOIN countrylanguage l
ON c.countrycode = l.countrycode
WHERE name LIKE ‘A%’
GROUP BY name
–> on n’en a plus que 14 !!! l’antartique a (re)disparu
— c’est ‘c’ qui a été complétée avec des NULL
— ce qui ne sert pas a grand chose

Jointure externe avec USING.
La clause USING(c1,c2) <==> ON t1.c1 = t2.c1, mais nécessite des colonnes homonymes

SELECT name Pays, count(language) « nb langues »
FROM country c LEFT JOIN countrylanguage l
USING (countrycode)
WHERE name LIKE ‘A%’
GROUP BY name