[SQL] Les jointures (niveau débutant)

20 août 2006, 10:17

La base fondamentale sur les jointures en SQL

Qu'est-ce qu'une jointure ?

Dans un schéma*, tu as plusieurs tables. Certaines tables comportent des données reliées aux données d'autres tables, le plus souvent parce que le nombre des informations diffère. Prenons un exemple.

Supposons une table personnes : dans cette table, tu vas avoir un identifiant en clé primaire, mais également un nom et un prénom. Mais serait-il opportun de stocker un numéro de téléphone dans cette table ? Non, parce que selon la personne, tu peux ne pas avoir de numéro du tout ou bien en avoir trois, par exemple un numéro de domicile, un autre de portable, et un troisième de bureau. Comment procéder ? En créant une seconde table telephones : dans cette table, tu auras un identifiant, le numéro de téléphone, le type de numéro (domicile, portable, bureau, fax, autres...) et une clé étrangère : l'identifiant de la table personne. Ceci nous donne le schéma suivant :

Dans cette illustration, on observe à droite des champs identifiant pers_id de la table personnes et tel_id de la table telephones la mention "PK" qu'il faut traduire par "Primary Key" ou, "Clé primaire" : c'est l'identifiant unique de chaque ligne. Mais dans la table telephones, on retrouve le champ pers_id avec la mention "FK" : traduire par "Foreign Key" ou "Clé étrangère" : c'est le lien entre nos deux tables.

Ainsi, lorsque la personne s'inscrit, elle peut avoir autant de numéros de téléphones que nécessaire ou ne pas en avoir. C'est la souplesse du système. Maintenant, as-tu besoin d'avoir dans la table personnes le nombre de numéros qu'a une personne ? Non, tu peux l'obtenir par calcul dans une requête avec jointure. La jointure va établir le lien entre chaque ligne de la table personnes et les lignes de la table telephones où on trouvera l'identifiant correspondant.

Pour l'exercice, créons nos deux tables :

Code : Tout sélectionner

CREATE TABLE `personnes` (
  `pers_id` int(11) unsigned NOT NULL auto_increment,
  `pers_nom` varchar(32) NOT NULL,
  `pers_prenom` varchar(32) NOT NULL,
  PRIMARY KEY  (`pers_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `telephones` (
  `tel_id` int(11) NOT NULL auto_increment,
  `pers_id` int(11) unsigned NOT NULL,
  `tel_num` char(12) NOT NULL,
  `tel_type` enum('domicile','portable','bureau','fax') NOT NULL default 'domicile',
  PRIMARY KEY  (`tel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Ajoutons quelques données;

Code : Tout sélectionner

INSERT INTO `personnes`(`pers_prenom`, `pers_nom`)
VALUES('Jean', 'Peuplus'),
('Augustin', 'Connu'),
('Pierre', 'Affeu');

INSERT INTO `telephones`(`pers_id`, `tel_num`, `tel_type`)
VALUES (1, '0145678910', 'domicile'),
(1, '0678901234', 'portable'),
(1, '0123456789', 'bureau'),
(3, '0654321098', 'portable');

À ce stade, on voir rapidement que Jean Peuplus dispose de trois numéros, Pierre Affeu en a un seul, et Augustin Connu n'est pas joignable par téléphone.

Effectuer une jointure, consiste à relier les deux tables par l'élément commun aux deux : ici, l'identifiant de la table personnes se retrouve également dans la table telephones. Dans une requête SELECT, je vais indiquer les deux tables dans la clause FROM et je vais indiquer le critère de jointure dans la clause WHERE :

Code : Tout sélectionner

SELECT `pers_prenom`, `pers_nom`, `tel_num`, `tel_type`
FROM `personnes` AS p, `telephones` AS t
WHERE p.pers_id = t.pers_id;

Et je vais obtenir :

Code : Tout sélectionner

+-------------+----------+------------+----------+
| pers_prenom | pers_nom | tel_num    | tel_type |
+-------------+----------+------------+----------+
| Jean        | Peuplus  | 0145678910 | domicile |
| Jean        | Peuplus  | 0678901234 | portable |
| Jean        | Peuplus  | 0123456789 | bureau   |
| Pierre      | Affeu    | 0654321098 | portable |
+-------------+----------+------------+----------+

Voilà, c'est pas plus sorcier que ça.

Mais il existe une autre syntaxe en remplacement de la clause WHERE : JOIN. Pour obtenir exactement le même résultat, je vais ré-écrire ma requête comme suit :

Code : Tout sélectionner

SELECT `pers_prenom`, `pers_nom`, `tel_num`, `tel_type`
FROM `personnes` AS p 
    JOIN `telephones` AS t
    ON p.pers_id = t.pers_id;

Et le résultat sera exactement le même.

Maintenant, supposons que je veuille seulement connaitre le nombre de numéros disponible pour chaque personne, je me fous de ces numéros, je veux juste leur nombre : Là, ce sera un brin plus complexe parce que je veux également les personnes qui n'ont pas de numéros. Et pour corser un peu, je veux en premier ceux qui ont le plus de numéros : on va faire ce qu'on appelle une jointure gauche :

Code : Tout sélectionner

SELECT `pers_prenom` AS 'Prénom', `pers_nom` AS 'Nom', COUNT(`tel_id`) AS 'NbNums'
FROM `personnes` AS p
  LEFT JOIN `telephones` AS t
    ON p.pers_id = t.pers_id
GROUP BY `pers_nom`, `pers_prenom`
ORDER BY COUNT(`tel_id`) DESC;

Et on va obtenir :

Code : Tout sélectionner

+----------+---------+--------+
| Prénom   | Nom     | NbNums |
+----------+---------+--------+
| Jean     | Peuplus |      3 |
| Pierre   | Affeu   |      1 |
| Augustin | Connu   |      0 |
+----------+---------+--------+

Et comme tu vois, je n'ai stocké nulle part le nombre de numéros, je l'ai obtenu par requête. C'est souple et rapide et ça laisse la liberté à chaque personne d'ajouter ou de retirer un de ces numéros sans que ton application doive déclencher une mise à jour automatique d'un champ particulier.

Les jointures peuvent être autrement plus complexes et sur un nombre supérieur de table. Ce court tuto n'a pas pour objet d'aller plus profondément dans l'exploration du sujet qui pourrait quasiment faire l'objet d'un livre à lui tout seul.

* On utilisera le terme "schema" plutôt que "base", "base" étant plus approprié pour désigner l'ensemble des schémas.

[SQL] Les jointures (niveau débutant)

Qui est en ligne