Comprendre les jointures avec MySQL

Une base de données ?

Nous allons pas entreprendre ici un cours de base de données (sinon il nous faudrait plus d’un tuto) mais nous allons juste vous rafraîchir la mémoire et vous mettre les idées au clair. Si nous devions schématiser à l’extrême, nous dirons qu’une base de données c’est comme un fichier Excel mais en extrêmement plus puissant (c’est pour cela que nous illustrerons souvent les exemples à partir de grilles Excel). Chaque donnée est enregistrée dans une colonne d’une « table ». La puissance d’une base de données et du langage SQL réside dans la possibilité de filtrer les données en leur donnant une cohérence entre elles, notamment grâce à des jointures.

Des quoi ? Des jointures ?

Lorsque l’on effectue une requête SQL, on génère un tableau (on parle même de table) comprenant les résultats de l’exécution de cette requête. Cette table est « temporaire » par opposition au contenu des tables qui lui est « permanent ». Si vous souhaitez « stocker » le résultat d’une requête dans ce cas on parlera de « vue » ou « view ».

Pour en revenir à la jointure, elle peut être définie comme l’union de « n » tables contenant des valeurs identiques dans « m » de leurs colonnes. Je vais illustrer cela par un exemple assez simple, une table « author » qui contient les auteurs et une table « post » qui contient leurs articles. Nous allons réaliser la jointure des deux tables sur le champ « author_id », pour que la ligne apparaisse dans le résultat il faut qu’à chaque valeur de la table des auteurs on puisse associer une ou plusieurs ligne de la table des articles.

Création de notre jeu d’essai

Pour la suite du tutoriel nous allons partir du script SQL suivant afin de générer notre base de données de test. L’exemple est relativement simple, il s’agit d’une base modélisant une gestion d’articles et de leurs catégories un peu à la WordPress (mais en beaucoup plus simple quand même).

01.--
02.-- Structure de la table `post`
03.--
04. 
05.CREATE TABLE IF NOT EXISTS `post` (
06.`post_id` int(11) NOT NULL auto_increment,
07.`post_title` varchar(50) NOT NULL,
08.`category_id` int(11) default NULL,
09.PRIMARY KEY  (`post_id`)
10.) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;
11. 
12.--
13.-- Contenu de la table `post`
14.--
15. 
16.INSERT INTO `post` (`post_id`, `post_title`, `category_id`) VALUES
17.(1, 'Bienvenue', 1),
18.(2, 'Settimeout Javascript', 2),
19.(3, 'Sprites CSS', 3),
20.(4, 'Firebug', 2),
21.(5, 'Google Apps', NULL),
22.(6, 'Colorisation Photoshop', 3),
23.(7, 'Base photographie', 5),
24.(8, 'Jointure SQL', 4),
25.(9, 'Article top secret', NULL);
26. 
27.--
28.-- Structure de la table `category`
29.--
30. 
31.CREATE TABLE IF NOT EXISTS `category` (
32.`category_id` int(11) NOT NULL auto_increment,
33.`category_name` varchar(50) NOT NULL,
34.PRIMARY KEY  (`category_id`)
35.) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
36. 
37.--
38.-- Contenu de la table `category`
39.--
40. 
41.INSERT INTO `category` (`category_id`, `category_name`) VALUES
42.(1, 'News'),
43.(2, 'Javascript'),
44.(3, 'Graphisme'),
45.(4, 'SQL'),
46.(5, 'Photographie'),
47.(6, 'PHP');

Vous remarquerez la possibilité que la clef étrangère « category_id » de la table « post » puissent être nulle, nous insistons sur ce point, vous verrez que ce sera très important par la suite. Le script SQL précédent nous donne donc la base suivante, la table des articles :

Et la table des catégories :

La jointure simple

La jointure présentée en introduction de l’article était une jointure simple ; c’est-à-dire qu’elle ne requiert rien de plus que l’utilisation de l’opérateur égal = .

Remarque : les autres opérateurs de MySQL (<, <=, >, >= ou <>) fonctionnent également pour faire des jointures.

Si nous devions récupérer la liste de tous les articles et de leurs catégories, il suffirait d’écrire la requête SQL suivante :

1.SELECT *
2.FROM post AS p, category AS c
3.WHERE p.category_id = c.category_id;

La requête nous donnant le résultat suivant :

Votre perspicacité vous aura sans doute permis de remarquer qu’il manquait 2 articles dans le résultat de la requête. En effet, nous souhaitions récupérer l’ensemble des articles et de leurs catégories, or comme nous l’avons expliqué auparavant une jointure simple fonctionne de la manière suivante : à chaque ligne de l’ensemble A (ici la table auteur) doit correspondre une ligne dans l’ensemble B (ici la table catégorie) où les champs servant de jointure ont la même valeur. Dans la théorie ensembliste, on parlerait de l’intersection de l’ensemble A et de l’ensemble B.

Il est donc naturel que les lignes où le champ « category_id » est à NULL ne soient pas apparues dans le résultat. Quant à la catégorie 6, elle n’apparaît pas, tout simplement parce qu’aucun article n’appartient à cette catégorie. Mais alors dans ce cas comment faire pour récupérer des données d’une table avec lesquelles on ne peut pas faire de jointure ? Avec les jointures complexes pardi !

Les jointures complexes

La structure classique d’une requête SQL est de la forme : SELECT [un ensemble de champs] FROM [un ensemble de tables] WHERE [un ensemble de conditions qui s'appliquent]. Les jointures simples sont effectuées dans la clause WHERE comme nous l’avons vu précédemment, mais ce n’est pas le cas des jointures complexes. En effet elles ont droit à un traitement de faveur et dispose donc de leurs propres mots clefs : INNER JOIN, LEFT JOIN et RIGHT JOIN.

Les requêtes SQL ont donc une structure quelque peu différente, et ces nouvelles clauses doivent être placées entre le FROM et le WHERE, comme ceci : SELECT [...] FROM [...] [INNER | LEFT | RIGHT] JOIN [...] WHERE [...].

INNER JOIN

Premier type de jointure complexe et finalement peu de nouveautés puisque le « INNER JOIN » fonctionne exactement de la même manière qu’une jointure simple. Son principal intérêt est d’apporter une certaine lisibilité et de mieux distinguer les jointures internes (INNER) des jointures externes (OUTER)

1.SELECT *
2.FROM post AS p
3.INNER JOIN category AS c ON p.category_id = c.category_id;

Nous vous épargnerons une nouvelle capture d’écran qui n’apporterait rien de plus.

LEFT (OUTER) JOIN

Deuxième type de jointure complexe et sans doute le plus intéressant, le LEFT JOIN va nous permettre de « donner du mou » à la requête SELECT. Rappelez-vous tout à l’heure quand nous disions qu’une jointure obligeaient les valeurs de l’ensemble A à appartenir à celles de l’ensemble B et bien avec le LEFT JOIN cette contrainte est levé (dans une certaine mesure). Voici comment MySQL explique la jointure dans la documentation :

S’il y a une ligne dans A qui répond à la clause WHERE, mais qu’il n’y avait aucune ligne dans B qui répondait à la condition du LEFT JOIN, alors une ligne supplémentaire de B est générée avec toutes les colonnes mises à NULL.

Voici le code de la requête :

1.SELECT *
2.FROM post AS p
3.LEFT JOIN category AS c ON p.category_id = c.category_id;

Et comme un schéma vaut cent discours, voici de quoi illustrer les explications précédentes, vous remarquerez que les lignes de la table des auteurs qui n’avaient pas de correspondance dans la tables des catégories ont été ajouté dans les résultats en remplaçant toutes les informations relatives à la catégorie par NULL :

RIGHT (OUTER) JOIN

Troisième de type de requête complexe, le RIGHT JOIN une jointure externe comme son nom l’indique, c’est-à-dire qu’elle fait référence à des données extérieures aux tables de la clause FROM. Il fonctionne de manière analogue au LEFT JOIN mais en inversant la contrainte n’existence des données ; à savoir que s’il n’existe pas de lignes dans la table A qui répondent à la jointure, une ligne supplémentaire de A est générée avec toutes les colonnes mises à NULL.

1.SELECT *
2.FROM post AS p
3.RIGHT JOIN category AS c ON p.category_id = c.category_id;

Voilà de quoi illustrer la requête :

Remarque : il aurait été parfaitement correct d’écrire cette requête avec un LEFT JOIN mais en inversant les deux tables dans la requête.

FULL (OUTER) JOIN

Nous risquons de décevoir ceux qui attendent le support du FULL JOIN par MySQL, mais nous allons vous livrer ici un quatrième type de jointure non supporté nativement par MySQL, mais qui peut l’être avec un peu de ruse !

Si nous récapitulons ce qu’on a vu jusqu’à maintenant, nous savons comment récupérer toutes les données d’une table A même s’il n’existe pas de jointure possible avec la table B avec un LEFT JOIN, nous savons écrire la réciproque avec le RIGHT JOIN. Mais que faire si nous souhaitons récupérer à la fois toutes les lignes de A et de B dans une même requête en effectuant une jointure ?

La réponse aurait été avec un FULL JOIN si MySQL l’avait supporté, fort heureusement il existe une fonction bien pratique : l’UNION. La clause UNION permet d’additionner le résultat de deux requêtes SQL tout en supprimant les doublons apparaissant dans les deux ensembles de résultats. Pour obtenir le résultat attendu il suffit de réaliser l’UNION d’un LEFT JOIN et d’un RIGHT JOIN, comme ceci :

1.(SELECT *
2.FROM post AS p
3.LEFT JOIN category AS c ON p.category_id = c.category_id)
4.UNION
5.(SELECT *
6.FROM post AS p
7.RIGHT JOIN category AS c ON p.category_id = c.category_id);

Ce qui nous donne :

Pour aller plus loin…

Nous vous conseillons de consulter la documentation officielle de MySQL et la manière dont MySQL optimise les requêtes LEFT et RIGHT JOIN