Ce didacticiel va traiter des DML (Data Manipulation Language), c'est-à-dire le langage de manipulation des données sous MySql.
Entre autre, nous aborderons les requêtes et en particulier nous analyserons les jointures et les sous-select.
Pour faire nos exercices, nous avons besoin d'un jeu d'essai. Nul besoin de compliquer à outrance nos tables (pas de références aux clefs étrangères, nous avons juste besoin d'un cas d'école.
Nos tables sont déséquilibrés, à savoir des écrivains sans livres, des livres sans écrivains, des genres sans responsables et des responsables sans genres, afin d'obtenir des résultats parlants.
En cliquant sur ce lien, vous trouverez la base de données 'essai' contenant nos trois tables.
La 'Primary Key' est sur la colonne 'clef'. Nous avons remplacé 'Metz' par la valeur NULL pour le lieu de naissance de 'Paul Verlaine'.
+------+------------+-----------+------------+----------------------+------------+--------------+
| clef | nom | prenom | naissance | lieu | mort | repos |
+------+------------+-----------+------------+----------------------+------------+--------------+
| Bal | Balzac | Honoré | 1799-05-20 | Tours | 1850-08-18 | Paris |
| Duf | Dumas Fils | Alexandre | 1824-07-27 | Paris | 1895-11-27 | Marly-le-Roi |
| Dup | Dumas Père | Alexandre | 1802-07-24 | Villiers-Cotterêts | 1870-12-05 | Puys |
| Fla | Flaubert | Gustave | 1821-12-12 | Rouen | 1880-05-08 | Croisset |
| Hug | Hugo | Victor | 1802-01-26 | Besancon | 1885-05-22 | Paris |
| Rim | Rimbaud | Arthur | 1854-10-20 | Charleville-Mézières | 1891-11-10 | Marseille |
| San | Sand | George | 1804-07-01 | Paris | 1876-06-08 | Nohant-Vic |
| Ver | Verlaine | Paul | 1844-03-30 | NULL | 1896-01-08 | Paris |
| Zol | Zola | Emile | 1840-04-02 | Paris | 1902-09-29 | Paris |
+------+------------+-----------+------------+----------------------+------------+--------------+
Une 'Primary Key' sur le couple 'clef' et 'titre', avec un index sur 'clef' et sur 'genre'.
+------+------------------------+----------+---------+--------+ | clef | titre | parution | genre | prix | +------+------------------------+----------+---------+--------+ | Bal | Eugénie Grandet | 1833 | Roman | 100.00 | | Bal | Le Père Goriot | 1834 | Roman | 0.00 | | Duf | La Dame Aux Camélias | 1852 | Théatre | 110.00 | | Dup | Les Trois Mousquetaire | 1844 | Roman | 80.00 | | Dup | Vingt ans après | 1845 | Roman | 80.00 | | Fla | Madame Bovary | 1857 | Roman | 99.50 | | Hug | Hernani | 1830 | Théatre | 120.00 | | Hug | Les Contemplations | 1856 | Poésie | 78.50 | | Hug | Les Misérables | 1862 | Roman | 148.50 | | Ste | Le Rouge et le Noir | 1831 | Roman | 98.50 | | Ste | La Chartreuse de Parme | 1839 | Roman | 110.50 | | Ver | Poèmes Saturniens | 1866 | Poésie | 90.50 | +------+------------------------+----------+---------+--------+
Une 'Primary key' sur genre'.
+---------+--------+ | genre | chef | +---------+--------+ | Poésie | Alain | | Roman | Jean | | Science | albert | +---------+--------+
Si vous avez besoin d'afficher un résultat dans une réquête, nul besoin de préciser le nom de la table. Par exemple afficher le classique 'hello world', en donnant comme titre 'bonjour' :
select 'hello world' as 'bonjour'
Le fait d'utiliser 'as' permet de renommer une colonne qui parfois peut-être sans nom. Nous obtenons le résultat suivant :
+-------------+ | bonjour | +-------------+ | hello world | +-------------+
Cet usage du select peut servir à afficher des variables commençant par '@' dans des select un peu plus compliqué.
Normalement, le 'FROM' désigne une table, mais peut servir à initialiser des variables, comme dans l'exemple ci-après.
select @A as 'Init' from (select @A:=52) as x;
Nous obtenons le résultat suivant :
+------+ | Init | +------+ | 52 | +------+
On ne peut pas faire plus simple que le vidage de toutes les lignes d'une table. Voici la syntaxe du vidage de la table des responsables.
select * from responsables;
Le résultat obtenu est à l'identique du paragraphe 2-C).
L'opérateur '*' sert à indiquer que l'on désire toutes les colonnes de la tables.
From désigne le nom de la table. Il y a deux écritures possibles pour accéder à une table, à partir d'une base de données.
Dans un script 'sql', vous devez préciser le nom de votre base de données (par exemple 'essai') auquel vous accédez. Il suffit de mettre au début de votre script, la ligne suivante :
use essai;
Remarque : il n'est pas nécessaire de faire l'usage systématique de l'apostrophe culbutée, c'est-à-dire l'apostrophe penchée à gauche, que l'on nomme aussi accent grave «`», qui correspondant au caractères 96 de votre clavier, pour encadrer des noms de tables ou de colonnes.
Une autre solution est de préfixer votre table par le nom de votre base de données comme ci-après :
select * from essai.responsables;
Si vous désirez sélectionner les lignes dont telle colonne à telle valeur, il suffit d'utliser la clause 'where' dans votre select.
select * from ecrivains where prenom like 'Alexandre';
Et voici le résultat de la sélection :
+------+------------+-----------+------------+--------------------+------------+--------------+ | clef | nom | prenom | naissance | lieu | mort | repos | +------+------------+-----------+------------+--------------------+------------+--------------+ | Duf | Dumas Fils | Alexandre | 1824-07-27 | Paris | 1895-11-27 | Marly-le-Roi | | Dup | Dumas Père | Alexandre | 1802-07-24 | Villiers-Cotterêts | 1870-12-05 | Puys | +------+------------+-----------+------------+--------------------+------------+--------------+
La fonction 'like' permet de sélectionner aussi les sous-chaînes de caractères en utilisant l'opérateur '%' pour un nombre quelconque de caractères.
Vous trouverez comment codifier les expressions régulières sur le site officiel.
Vous avez aussi les principaux opérateurs de comparaisons que vous devez connaitre : '=', '<', '<=', '>', '>=', '<>', '!=', 'is null', is not null'. La suite sur le site officiel.
Vous avez la possibilité de regrouper les mêmes occurrences d'une colonne afin d'obtenir une seule ligne. De même, vous pouvez aussi les trier dans l'ordre que vous désirez, en utilsant 'asc' pour ascendant et 'desc' pour descendant.
select repos, count(*) as 'nbre' from ecrivains group by repos order by repos asc;
Nous comptabilisons le nombre de lignes dans la table des écrivains, ayant la même occurrence dans la colonne 'repos'. Nous trions le résultat dans l'ordre ascendant.
+--------------+------+ | repos | nbre | +--------------+------+ | Croisset | 1 | | Marly-le-Roi | 1 | | Marseille | 1 | | Nohant-Vic | 1 | | Paris | 4 | | Puys | 1 | +--------------+------+
La 'count(*)' permet de comptabiliser le nombre de lignes. Il existe d'autres fonctions que vous trouverez sur le site officiel.
les plus connues sont 'sum()' pour comptabiliser un colonne numérique, 'concat()' pour concaténer des chaines de caratères, 'now()' pour obtenir la date et l'heure du jour, 'substr()' pour extraire une sous-chaine de caractères ...
Elle s'utilise à l'identique de la clause 'Where', sauf qu'elle s'applique sur le 'Group by'. Reprenons notre exemple précédent. Nous désirons obtenir uniquement les lignes ayant 'nbre' supérieure ou égale à '4'.
select repos, count(*) as 'nbre' from ecrivains group by repos having nbre >= 4 order by repos asc;
Le 'Where' s'applique sur les lignes venant de la table, tandis que le 'Having' s'applique sur le résultat du select, en association du 'Group by', sur des regroupement de résultats. Voici le résultat de la requête.
+-------+------+ | repos | nbre | +-------+------+ | Paris | 4 | +-------+------+
Remarque : dans cet exemple, j'ai renommé la colonne 'count(*)' en 'nbre' et j'ai appliqué le 'Having' sur ce nom. Si je n'avais pas renommé cette colonne, le having aurait dû s'écire ainsi :
select repos, count(*) from ecrivains group by repos having count(*) >= 4 order by repos asc;
Il est donc préférable de nommer les colonnes quand celles-ci sont des fonctions. D'autres explication sur le site officiel.
Il n'est pas nécessaire d'utiliser le 'Group by' pour faire apparaitre une et une seul occurrence. On peut sélectionner juste la première occurrence, par l'adjonction de 'distinct' devant la colonne.
select distinct repos from ecrivains;
Sans distinct, 'Paris' serait apparu quatre fois. Avec 'Group by', il y aurait eu une étape supplémentaire, qui aurait allongé le temps du traitement. Avec la clause 'distinct' les occurrences multiples sont supprimées.
+--------------+ | repos | +--------------+ | Paris | | Marly-le-Roi | | Puys | | Croisset | | Marseille | | Nohant-Vic | +--------------+
On peut limiter le nombre de lignes à la sortie d'un select par l'intermédiaire de la clause 'Limit n' où 'n' est le nombre d'occurrence à afficher en sortie.
select * from ecrivains limit 5;
La table ecrivains à neuf occurrences. Nous désirons afficher les cinq premières lignes.
+------+------------+-----------+------------+--------------------+------------+--------------+ | clef | nom | prenom | naissance | lieu | mort | repos | +------+------------+-----------+------------+--------------------+------------+--------------+ | Bal | Balzac | Honoré | 1799-05-20 | Tours | 1850-08-18 | Paris | | Duf | Dumas Fils | Alexandre | 1824-07-27 | Paris | 1895-11-27 | Marly-le-Roi | | Dup | Dumas Père | Alexandre | 1802-07-24 | Villiers-Cotterêts | 1870-12-05 | Puys | | Fla | Flaubert | Gustave | 1821-12-12 | Rouen | 1880-05-08 | Croisset | | Hug | Hugo | Victor | 1802-01-26 | Besancon | 1885-05-22 | Paris | +------+------------+-----------+------------+--------------------+------------+--------------+
De même, on peut afficher cinq lignes, en rejetant les quatre premières occurrences de la table ecrivains.
select * from ecrivains limit 4, 5;
En cas d'une grosse volumétrie, vous pouvez afficher par groupe de cinq lignes, comme dans notre exemple.
+------+----------+--------+------------+----------------------+------------+------------+ | clef | nom | prenom | naissance | lieu | mort | repos | +------+----------+--------+------------+----------------------+------------+------------+ | Hug | Hugo | Victor | 1802-01-26 | Besancon | 1885-05-22 | Paris | | Rim | Rimbaud | Arthur | 1854-10-20 | Charleville-Mézières | 1891-11-10 | Marseille | | San | Sand | George | 1804-07-01 | Paris | 1876-06-08 | Nohant-Vic | | Ver | Verlaine | Paul | 1844-03-30 | Metz | 1896-01-08 | Paris | | Zol | Zola | Emile | 1840-04-02 | Paris | 1902-09-29 | Paris | +------+----------+--------+------------+----------------------+------------+------------+
La ligne 'Hugo' est la dernière ligne du tableau dans le premier affichage. Et elle est aussi la première ligne du tableau dans le second affichage. En position relative, c'est la cinquième ligne dans la table des écrivains.
On peut encadrer les résultats d'une colonne donnée, en spécifiant la borne inférieure et supérieure. Les bornes sont incluses.
select * from libres where prix between 80.00 and 90.50;
Dans notre exemple, nous sélectionnons les prix compris entre 80,00€ et 90.50€.
+------+------------------------+----------+--------+-------+ | clef | titre | parution | genre | prix | +------+------------------------+----------+--------+-------+ | Dup | Les Trois Mousquetaire | 1844 | Roman | 80.00 | | Dup | Vingt ans après | 1845 | Roman | 80.00 | | Ver | Poèmes Saturniens | 1866 | Poésie | 90.50 | +------+------------------------+----------+--------+-------+
Je ne connaissais pas cette clause. La clause 'With rollup' permet d'obtenir un total général sur une colonne numérique. Dans cet exemple, nous totalisons les prix pour chaque clef unique.
select clef, sum(prix) as total from livres group by clef with rollup;
Une nouvelle ligne apparait (la ligne en rouge), dont la clef est null.
+------+---------+
| clef | total |
+------+---------+
| Bal | 100.00 |
| Duf | 110.00 |
| Dup | 160.00 |
| Fla | 99.50 |
| Hug | 347.00 |
| Ste | 209.00 |
| Ver | 90.50 |
| NULL | 1116.00 |
+------+---------+
En fait, cette clause ne sert à rien, car nous pouvons obtenir le total de la colonne 'prix', en utilisant la fonction 'sum()', comme si après :
select NULL as clef, sum(prix) as total from livres;
Et voici le résultat :
+------+---------+ | clef | total | +------+---------+ | NULL | 1116.00 | +------+---------+
Nous pouvons tester la valeur particulière 'NULL' dans un select comme ci-après :
select * from ecrivains where lieu is NULL;
Et nous obtenons :
+------+----------+--------+------------+------+------------+-------+ | clef | nom | prenom | naissance | lieu | mort | repos | +------+----------+--------+------------+------+------------+-------+ | Ver | Verlaine | Paul | 1844-03-30 | NULL | 1896-01-08 | Paris | +------+----------+--------+------------+------+------------+-------+
Ce chapitre est particulier car nous allons traiter du lien, que l'on nomme jointure, entre deux ou plusieurs tables.
Il s'agit de la jointure sans restriction qui donne une table résultante d'un total de ligne équivalent au nombre de lignes de la première table multiplié par le nombre de ligne de la seconde table.
select * from ecrivains, responsables;
Dans cet exemple, la table résultante est de 9 X 3 = 27 lignes. Aucune clef n'est commune entre ces deux tables. Chaque ligne de la table ecrivains est dupliquée trois fois, puisqu'il y a trois ligne dans la table des responsables.
+------+------------+-----------+------------+----------------------+------------+--------------+---------+--------+ | clef | nom | prenom | naissance | lieu | mort | repos | genre | chef | +------+------------+-----------+------------+----------------------+------------+--------------+---------+--------+ | Bal | Balzac | Honoré | 1799-05-20 | Tours | 1850-08-18 | Paris | Poésie | Alain | | Bal | Balzac | Honoré | 1799-05-20 | Tours | 1850-08-18 | Paris | Roman | Jean | | Bal | Balzac | Honoré | 1799-05-20 | Tours | 1850-08-18 | Paris | Science | albert | | Duf | Dumas Fils | Alexandre | 1824-07-27 | Paris | 1895-11-27 | Marly-le-Roi | Poésie | Alain | | Duf | Dumas Fils | Alexandre | 1824-07-27 | Paris | 1895-11-27 | Marly-le-Roi | Roman | Jean | | Duf | Dumas Fils | Alexandre | 1824-07-27 | Paris | 1895-11-27 | Marly-le-Roi | Science | albert | | Dup | Dumas Père | Alexandre | 1802-07-24 | Villiers-Cotterêts | 1870-12-05 | Puys | Poésie | Alain | | Dup | Dumas Père | Alexandre | 1802-07-24 | Villiers-Cotterêts | 1870-12-05 | Puys | Roman | Jean | | Dup | Dumas Père | Alexandre | 1802-07-24 | Villiers-Cotterêts | 1870-12-05 | Puys | Science | albert | | Fla | Flaubert | Gustave | 1821-12-12 | Rouen | 1880-05-08 | Croisset | Poésie | Alain | | Fla | Flaubert | Gustave | 1821-12-12 | Rouen | 1880-05-08 | Croisset | Roman | Jean | | Fla | Flaubert | Gustave | 1821-12-12 | Rouen | 1880-05-08 | Croisset | Science | albert | | Hug | Hugo | Victor | 1802-01-26 | Besancon | 1885-05-22 | Paris | Poésie | Alain | | Hug | Hugo | Victor | 1802-01-26 | Besancon | 1885-05-22 | Paris | Roman | Jean | | Hug | Hugo | Victor | 1802-01-26 | Besancon | 1885-05-22 | Paris | Science | albert | | Rim | Rimbaud | Arthur | 1854-10-20 | Charleville-Mézières | 1891-11-10 | Marseille | Poésie | Alain | | Rim | Rimbaud | Arthur | 1854-10-20 | Charleville-Mézières | 1891-11-10 | Marseille | Roman | Jean | | Rim | Rimbaud | Arthur | 1854-10-20 | Charleville-Mézières | 1891-11-10 | Marseille | Science | albert | | San | Sand | George | 1804-07-01 | Paris | 1876-06-08 | Nohant-Vic | Poésie | Alain | | San | Sand | George | 1804-07-01 | Paris | 1876-06-08 | Nohant-Vic | Roman | Jean | | San | Sand | George | 1804-07-01 | Paris | 1876-06-08 | Nohant-Vic | Science | albert | | Ver | Verlaine | Paul | 1844-03-30 | NULL | 1896-01-08 | Paris | Poésie | Alain | | Ver | Verlaine | Paul | 1844-03-30 | NULL | 1896-01-08 | Paris | Roman | Jean | | Ver | Verlaine | Paul | 1844-03-30 | NULL | 1896-01-08 | Paris | Science | albert | | Zol | Zola | Emile | 1840-04-02 | Paris | 1902-09-29 | Paris | Poésie | Alain | | Zol | Zola | Emile | 1840-04-02 | Paris | 1902-09-29 | Paris | Roman | Jean | | Zol | Zola | Emile | 1840-04-02 | Paris | 1902-09-29 | Paris | Science | albert | +------+------------+-----------+------------+----------------------+------------+--------------+---------+--------+
Le principe de la jointure repose sur le produit cartésien de deux ou plusieurs tables, avec une ou plusieurs restrictions, qui ne sont pas uniquement des colonnes communes à ces tables.
Pour illustrer les jointures sur deux tables, nous utiliserons les diagrammes de Venn. Voici l'intersection entre deux tables :
La table A sera celle des Ecrivains et la table B, celle des livres. Dans cet exemple, nous utilisons la jointure dite naturelle.
Select tb1.clef, tb1.nom, tb1.prenom,
tb2.titre, tb2.parution, tb2.genre, tb2.prix
from `ecrivain` as tb1,
`livres` as tb2
where tb2.clef = tb1.clef;
Voici le résultat obtenu.
+------+------------+-----------+------------------------+----------+---------+--------+ | clef | nom | prenom | titre | parution | genre | prix | +------+------------+-----------+------------------------+----------+---------+--------+ | Bal | Balzac | Honoré | Eugénie Grandet | 1833 | Roman | 100.00 | | Bal | Balzac | Honoré | Le Père Goriot | 1834 | Roman | 0.00 | | Duf | Dumas Fils | Alexandre | La Dame Aux Camélias | 1852 | Théatre | 110.00 | | Dup | Dumas Père | Alexandre | Les Trois Mousquetaire | 1844 | Roman | 80.00 | | Dup | Dumas Père | Alexandre | Vingt ans après | 1845 | Roman | 80.00 | | Fla | Flaubert | Gustave | Madame Bovary | 1857 | Roman | 99.50 | | Hug | Hugo | Victor | Hernani | 1830 | Théatre | 120.00 | | Hug | Hugo | Victor | Les Contemplations | 1856 | Poésie | 78.50 | | Hug | Hugo | Victor | Les Misérables | 1862 | Roman | 148.50 | | Ver | Verlaine | Paul | Poèmes Saturniens | 1866 | Poésie | 90.50 | +------+------------+-----------+------------------------+----------+---------+--------+
Bien que fonctionnant correctement, cette forme d'écriture de la requête est devenue obsolète.
Pour ce faire, nous utilions le join de type 'inner', c'est-à-dire l'intersection comme ci-après :
select tb1.clef, tb1.nom, tb1.prenom,
tb2.titre, tb2.parution, tb2.genre, tb2.prix
from `ecrivains` as tb1
inner join `livres` as tb2
on tb2.clef = tb1.clef
Le résultat produit est à l'identique de la jointure dite naturelle. Dorénavant, pour une intersection, nous utiliserons la jointure de type 'inner'.
Voici le diagramme de Venn de la jointure gauche :
La jointure gauche consiste à afficher toutes les lignes de la première table, même si aucune correspondance existe dans la seconde table.
Les lignes communes entre les deux tables sont à l'identique de l'intersection que nous venons de voir au paragraphe précédent.
Les lignes absentes de la seconde table sont remplacées par la valeur 'NULL'.
Pour ce faire, nous utilisons le join de type 'left outer', comme ci-après :
--------------
select tb1.clef, tb1.nom, tb1.prenom,
tb2.titre, tb2.parution, tb2.genre, tb2.prix
from ecrivains as tb1
left outer join livres as tb2
on tb2.clef = tb1.clef
--------------
+------+------------+-----------+------------------------+----------+---------+--------+
| clef | nom | prenom | titre | parution | genre | prix |
+------+------------+-----------+------------------------+----------+---------+--------+
| Bal | Balzac | Honoré | Le Père Goriot | 1834 | Roman | 0.00 |
| Bal | Balzac | Honoré | Eugénie Grandet | 1833 | Roman | 100.00 |
| Duf | Dumas Fils | Alexandre | La Dame Aux Camélias | 1852 | Théatre | 110.00 |
| Dup | Dumas Père | Alexandre | Vingt ans après | 1845 | Roman | 80.00 |
| Dup | Dumas Père | Alexandre | Les Trois Mousquetaire | 1844 | Roman | 80.00 |
| Fla | Flaubert | Gustave | Madame Bovary | 1857 | Roman | 99.50 |
| Hug | Hugo | Victor | Les Misérables | 1862 | Roman | 148.50 |
| Hug | Hugo | Victor | Les Contemplations | 1856 | Poésie | 78.50 |
| Hug | Hugo | Victor | Hernani | 1830 | Théatre | 120.00 |
| Rim | Rimbaud | Arthur | NULL | NULL | NULL | NULL |
| San | Sand | George | NULL | NULL | NULL | NULL |
| Ver | Verlaine | Paul | Poèmes Saturniens | 1866 | Poésie | 90.50 |
| Zol | Zola | Emile | NULL | NULL | NULL | NULL |
+------+------------+-----------+------------------------+----------+---------+--------+
Dans cet exemple, nous retrouvons bien la sélection des lignes de l'intersection. Trois lignes sont en suppléments, celle des auteurs qui n'ont pas de correspondances dans la table des 'livres'. De cela, les colonnes 'titres', 'parution, 'genre ' et 'prix' sont à 'NULL'.
Voici le diagramme de Venn de la jointure droite :
Une jointure droite sur les tables 'A' et 'B', c'est en fait une jointure gauche sur les tables 'B' et 'A'. Il suffit d'inverser la première table avec la seconde.
--------------
select tb1.clef, tb1.nom, tb1.prenom,
tb2.titre, tb2.parution, tb2.genre, tb2.prix
from `livres` as tb2
right outer join `ecrivains` as tb1
on tb2.clef = tb1.clef
--------------
+------+------------+-----------+------------------------+----------+---------+--------+
| clef | nom | prenom | titre | parution | genre | prix |
+------+------------+-----------+------------------------+----------+---------+--------+
| Bal | Balzac | Honoré | Le Père Goriot | 1834 | Roman | 0.00 |
| Bal | Balzac | Honoré | Eugénie Grandet | 1833 | Roman | 100.00 |
| Duf | Dumas Fils | Alexandre | La Dame Aux Camélias | 1852 | Théatre | 110.00 |
| Dup | Dumas Père | Alexandre | Vingt ans après | 1845 | Roman | 80.00 |
| Dup | Dumas Père | Alexandre | Les Trois Mousquetaire | 1844 | Roman | 80.00 |
| Fla | Flaubert | Gustave | Madame Bovary | 1857 | Roman | 99.50 |
| Hug | Hugo | Victor | Les Misérables | 1862 | Roman | 148.50 |
| Hug | Hugo | Victor | Les Contemplations | 1856 | Poésie | 78.50 |
| Hug | Hugo | Victor | Hernani | 1830 | Théatre | 120.00 |
| Rim | Rimbaud | Arthur | NULL | NULL | NULL | NULL |
| San | Sand | George | NULL | NULL | NULL | NULL |
| Ver | Verlaine | Paul | Poèmes Saturniens | 1866 | Poésie | 90.50 |
| Zol | Zola | Emile | NULL | NULL | NULL | NULL |
+------+------------+-----------+------------------------+----------+---------+--------+
Mais si vous n'inversez pas vos deux tables, vous n'obtiendrez pas le même résultats. Voici la preuve :
--------------
select tb1.clef, tb1.nom, tb1.prenom,
tb2.titre, tb2.parution, tb2.genre, tb2.prix
from `ecrivains` as tb1
right outer join `livres` as tb2
on tb2.clef = tb1.clef
--------------
+------+------------+-----------+------------------------+----------+---------+--------+
| clef | nom | prenom | titre | parution | genre | prix |
+------+------------+-----------+------------------------+----------+---------+--------+
| Bal | Balzac | Honoré | Eugénie Grandet | 1833 | Roman | 100.00 |
| Bal | Balzac | Honoré | Le Père Goriot | 1834 | Roman | 0.00 |
| Duf | Dumas Fils | Alexandre | La Dame Aux Camélias | 1852 | Théatre | 110.00 |
| Dup | Dumas Père | Alexandre | Les Trois Mousquetaire | 1844 | Roman | 80.00 |
| Dup | Dumas Père | Alexandre | Vingt ans après | 1845 | Roman | 80.00 |
| Fla | Flaubert | Gustave | Madame Bovary | 1857 | Roman | 99.50 |
| Hug | Hugo | Victor | Hernani | 1830 | Théatre | 120.00 |
| Hug | Hugo | Victor | Les Contemplations | 1856 | Poésie | 78.50 |
| Hug | Hugo | Victor | Les Misérables | 1862 | Roman | 148.50 |
| NULL | NULL | NULL | Le Rouge et le Noir | 1831 | Roman | 98.50 |
| NULL | NULL | NULL | La Chartreuse de Parme | 1839 | Roman | 110.50 |
| Ver | Verlaine | Paul | Poèmes Saturniens | 1866 | Poésie | 90.50 |
+------+------------+-----------+------------------------+----------+---------+--------+
L'explication de cette différence vient du fait que l'on part de la première table et que l'on recherche toutes les lignes en lien dans la seconde table.
Or toutes les lignes présentent dans la seconde table, qui ne sont pas en lien vers la première, ne seront pas affichées. D'où la non symétrie entre la jointure gauche avec la jointure droite, sauf si l'on inverse les tables, comme dans notre exemple.
Voici le diagramme de Venn de la jointure totale :
En MySql, cette écriture n'existe pas mais on peut quand même la produire, en faisant l'union d'une jointure gauche avec une jounture droite, comme ci-après :
--------------
select tb1.clef, tb1.nom, tb1.prenom,
tb2.titre, tb2.parution, tb2.genre, tb2.prix
from `ecrivains` as tb1
left outer join `livres` as tb2
on tb2.clef = tb1.clef
union
select tb1.clef, tb1.nom, tb1.prenom,
tb2.titre, tb2.parution, tb2.genre, tb2.prix
from `ecrivains` as tb1
right outer join `livres` as tb2
on tb2.clef = tb1.clef
--------------
+------+------------+-----------+------------------------+----------+---------+--------+
| clef | nom | prenom | titre | parution | genre | prix |
+------+------------+-----------+------------------------+----------+---------+--------+
| Bal | Balzac | Honoré | Le Père Goriot | 1834 | Roman | 0.00 |
| Bal | Balzac | Honoré | Eugénie Grandet | 1833 | Roman | 100.00 |
| Duf | Dumas Fils | Alexandre | La Dame Aux Camélias | 1852 | Théatre | 110.00 |
| Dup | Dumas Père | Alexandre | Vingt ans après | 1845 | Roman | 80.00 |
| Dup | Dumas Père | Alexandre | Les Trois Mousquetaire | 1844 | Roman | 80.00 |
| Fla | Flaubert | Gustave | Madame Bovary | 1857 | Roman | 99.50 |
| Hug | Hugo | Victor | Les Misérables | 1862 | Roman | 148.50 |
| Hug | Hugo | Victor | Les Contemplations | 1856 | Poésie | 78.50 |
| Hug | Hugo | Victor | Hernani | 1830 | Théatre | 120.00 |
| Rim | Rimbaud | Arthur | NULL | NULL | NULL | NULL |
| San | Sand | George | NULL | NULL | NULL | NULL |
| Ver | Verlaine | Paul | Poèmes Saturniens | 1866 | Poésie | 90.50 |
| Zol | Zola | Emile | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | Le Rouge et le Noir | 1831 | Roman | 98.50 |
| NULL | NULL | NULL | La Chartreuse de Parme | 1839 | Roman | 110.50 |
+------+------------+-----------+------------------------+----------+---------+--------+
Une sous-requête est une requête imbriquée dans une requête dite principale. Elle est fréquemment utilisée avec les clauses 'exists' ou 'in' et plus rarement avec les clauses 'some', 'any' et 'all'.
Il existe deux formes de sous-requêtes :
la sous-requête simple ou non corrélée.
Il n'existe aucune jointure entre la requête principale et la sous-requête. Le lien se fait essentiellement par un ensemble de valeur, comme dans le cas de la clause 'in'.
la sous-requête corrélée.
Une sous-requête corrélée est la mise en relation de deux tables, comme dans le cas de la jointure.
Sous MySql, la sous-requête est d'une performance totalement dégradée. Nous devons prendre l'habitude de toujours transformer la sous-requête en jointure gauche la plus part du temps, si cela est possible.
Voici le diagramme de Venn de la différence gauche :
Pour obtenir une différence, nous sommes obligé de tester la non existence vis-à-vis de la table en jointure. Tester l'exitance d'une relation entre la requête principale et la sous-requête, par l'intermédiaire de la clause 'where', se nomme une 'corrélation'.
Voici la sous-requête corrélée :
-------------- select tb1.clef, tb1.nom, tb1.prenom from `ecrivains` as tb1 where not exists (select 1 from `livres` as tb2 where tb2.clef = tb1.clef) -------------- +------+---------+--------+ | clef | nom | prenom | +------+---------+--------+ | Rim | Rimbaud | Arthur | | San | Sand | George | | Zol | Zola | Emile | +------+---------+--------+
Bien que le résultat produit soit tout à fait correcte, elle demeure moins performante. Nous préférerons la jointure de type gauche que voici :
-------------- select tb1.clef, tb1.nom, tb1.prenom from `ecrivains` as tb1 left outer join `livres` as tb2 on tb2.clef = tb1.clef where tb2.clef is null -------------- +------+---------+--------+ | clef | nom | prenom | +------+---------+--------+ | Rim | Rimbaud | Arthur | | San | Sand | George | | Zol | Zola | Emile | +------+---------+--------+
Comme on le constate, les deux requêtes produisent le même résultat. Une sous-requête qu'elle soit corrélée ou pas est bien plus couteuse en terme de performance qu'une jointure. On privilégie alors la jointure plutôt que la sous-requête.
Voici le diagramme de Venn de la différence droite :
Une solution consiste à inverser la première table avec la seconde afin de retomber sur la différence gauche, comme ci-après :
-------------- select tb2.clef, tb2.titre, tb2.parution from `livres` as tb2 where not exists (select 1 from `ecrivains` as tb1 where tb1.clef = tb2.clef) -------------- +------+------------------------+----------+ | clef | titre | parution | +------+------------------------+----------+ | Ste | Le Rouge et le Noir | 1831 | | Ste | La Chartreuse de Parme | 1839 | +------+------------------------+----------+
L'autre solution consiste à utiliser la jointure droite, comme ci-après :
-------------- select tb2.clef, tb2.titre, tb2.parution from `ecrivains` as tb1 right outer join `livres` as tb2 on tb2.clef = tb1.clef where tb1.clef is null -------------- +------+------------------------+----------+ | clef | titre | parution | +------+------------------------+----------+ | Ste | Le Rouge et le Noir | 1831 | | Ste | La Chartreuse de Parme | 1839 | +------+------------------------+----------+
Je ferrai la même remarque que précédemment. Il est préférable pour des questions de performances d'utiliser la jointure droite, plutôt que la sous-requête corrélée.
Ce didacticiel est juste un aperçu de ce que l'on peut faire avec les requêtes pour sélectionner des ensembles de valeurs. Pour approfondir le sujet, il est nécessaire de parcourir le manuel de référence de MySql.