Version en ligne

Tutoriel : Introduction aux jointures SQL

Table des matières

Introduction aux jointures SQL
Pré-requis : les bases de données relationnelles
Les jointures, quel intérêt ?
Les jointures internes
Les jointures externes
Restons lisibles !
Pour aller plus loin

Introduction aux jointures SQL

Pré-requis : les bases de données relationnelles

Ce tutoriel a pour but de vous faire découvrir les jointures en SQL. Pour le comprendre, il est nécessaire d'avoir lu la partie Les bases de données du tutoriel officiel de PHP/MySQL.

Pré-requis : les bases de données relationnelles

Les jointures, quel intérêt ?

Avant d'utiliser les jointures, il faut que votre base de données relationnelle soit correctement conçue. Vous apprendrez dans cette partie quelques règles de base (loin d'être exhaustives) dans la conception des bases de données.

Pour ce faire, utilisons un exemple.

Citation

Vous êtes informaticien dans une agence de presse et vous devez stocker les articles des journalistes de l'agence dans une base de données relationnelle. Vous devez stocker pour chaque article son titre, son contenu et sa date, ainsi que le nom, le prénom et l'adresse e-mail de l'auteur. Plusieurs journaux achètent des articles à l'agence de presse en question. Celle-ci voudrait savoir quels journaux ont acheté quels articles. L'agence désire par ailleurs connaître, en plus de leur nom, l'adresse des journaux.

Ces règles n'étant pas faciles à comprendre de prime abord, appliquons-les à notre exemple.

On aurait a priori tendance à créer une table "articles" ayant la structure suivante (notez l'absence de caractères spéciaux, d'espaces et d'accents dans les noms des champs) :

articles

id_article

titre_article

nom_auteur

prenom_auteur

email_auteur

texte_article

date_article

Ce qui donne, avec quelques données (exemple) :

id_article

titre_article

nom_auteur

prenom_auteur

email_auteur

texte_article

date_article

1

Wall Street repasse dans le vert

Dupont

Jacques

[email protected]

blabla

26/01/2010

2

La Fed garde son taux directeur inchangé

Dubois

Marcel

[email protected]

blibli

27/01/2010

3

Des pensionnés très occupés !

Dupont

Jacques

[email protected]

bloblo

28/01/2010

4

L'entretien du réseau routier laisse à désirer

Leroy

Nicole

[email protected]

blublu

28/01/2010

On remarque cependant qu’il y a répétition des informations sur l'auteur. En effet, un auteur peut avoir écrit plusieurs articles !

On va donc créer une table "auteurs" en relation avec la table "articles" pour éviter d'avoir une répétition inutile de toutes les informations sur les auteurs. Cela permet en pratique, si vous voulez modifier un élément (par exemple l'adresse e-mail de l'auteur), de ne devoir le faire qu’une seule fois !

La structure de la table auteurs est la suivante :

auteurs

id_auteur

nom_auteur

prenom_auteur

email_auteur

Ce qui donne en données :

id_auteur

nom_auteur

prenom_auteur

email_auteur

1

Dubois

Marcel

[email protected]

2

Dupont

Jacques

[email protected]

3

Leroy

Nicole

[email protected]

La nouvelle structure de la table "articles" est la suivante :

articles

id_article

titre_article

id_auteur

texte_article

date_article

Ce qui donne en données :

id_article

titre_article

id_auteur

texte_article

date_article

1

Wall Street repasse dans le vert

2

blabla

26/01/2010

2

La Fed garde son taux directeur inchangé

1

blibli

27/01/2010

3

Des pensionnés très occupés !

2

bloblo

28/01/2010

4

L'entretien du réseau routier laisse à désirer

3

blublu

28/01/2010

On a donc créé une relation numérique entre deux tables pour éviter la répétition d'informations. On comprend l'importance du champ "id" de chaque table qui doit absolument prendre une valeur différente/unique pour chaque ligne de la table.

Le champ de type "id" (première colonne), qui contient un identifiant unique pour chaque élément (ligne) de la table, est appelé clé primaire (Primary Key = PK).

Dans la table articles, on a un autre type de champ "id", qui peut en effet prendre plusieurs fois la même valeur. C'est ce qu'on appelle une clé étrangère (Foreign Key = FK). Il correspond à la clé primaire d'une autre table et permet de créer une relation avec celle-ci.

Dans notre cas, le champ (clé étrangère) "id_auteur" de la table articles correspond au champ (clé primaire) "id_auteur" de la table auteurs. On nomme généralement de la même façon la clé primaire et la clé étrangère relative. La relation PK – FK est appelée relation 1 – N.

Mise en pratique :

Reprenons notre exemple. En plus des tables articles et auteurs, créons la table "journaux".

journaux

id_journal

nom_journal

adresse_journal

id_journal

nom_journal

adresse_journal

1

Le Monde

Paris

2

Le Figaro

Paris

3

Le Soir

Bruxelles

4

Libération

Paris

On veut stocker les articles achetés par ces journaux à l'agence de presse. Un journal peut avoir acheté plusieurs articles, et un article peut avoir été acheté par plusieurs journaux. On a donc une relation N – N.

Il ne nous est pas possible de stocker plusieurs articles pour un même journal (on a une seule ligne par journal dans la table journaux), il n'est pas non plus possible de faire l'inverse (on ne peut pas stocker plusieurs journaux pour un même article, on a également une seule ligne par article dans la table articles). On est donc obligés de créer une table intermédiaire pour stocker toutes nos relations (sous forme d'id). Grâce à ce subterfuge, on retrouve des relations 1 – N à la place de la relation N – N.

Notre table stockant les relations pourrait ressembler à ceci (données aléatoires) :

jonction_articles_journaux

id_jonction_art_journ

id_article

id_journal

id_jonction_art_journ

id_article

id_journal

1

1

2

2

1

4

3

2

2

4

3

1

5

3

2

6

3

3

7

4

1

8

4

3

Dans notre exemple, ça aurait pu donner ceci :

id_article

titre_article

id_auteur

1

Wall Street repasse dans le vert

2

2

La Fed garde son taux directeur inchangé

1

3

Des pensionnés très occupés !

2

4

L'entretien du réseau routier laisse à désirer

3

id_article

texte_article

date_article

1

blabla

26/01/2010

2

blibli

27/01/2010

3

bloblo

28/01/2010

4

blublu

28/01/2010

Il s'agit en fait d'une relation entre clés primaires (PK – PK). Chaque élément ayant sa correspondance dans l'autre table, on peut les regrouper dans une seule table !

Pour terminer, voici la structure de la base de données, avec ses relations. Sous Microsoft Access, les relations 1 – N sont notées 1 –

Image utilisateur

.

Image utilisateur

Notez que chaque champ de table a diverses propriétés, notamment un type (textuel, numérique, date, booléen, ...) ou une taille (nombre de caractères). Nous n'aborderons pas ces détails ici.


Les jointures, quel intérêt ?

Les jointures, quel intérêt ?

Pré-requis : les bases de données relationnelles Les jointures internes

Encore un truc inutile pour que mon code paraisse plus savant ?

Pas du tout !

Pour le comprendre, reprenons l'exemple de l'agence de presse, et concentrons-nous sur la relation entre la table "articles" et la table "auteurs". Nous avons donc une relation entre les deux tables via le champ "id_auteur".

Dans notre script, nous désirons afficher une liste des articles avec, en plus du titre de l'article, le nom de l'auteur.

Un codeur ne connaissant pas les jointures va procéder comme suit :

<?
$req_articles = mysql_query('SELECT `titre_article`,`id_auteur` FROM `articles` ');
while ($data_articles = mysql_fetch_array($req_articles))
{
    $req_auteurs = mysql_query('SELECT `nom_auteur` FROM `auteurs` WHERE `id_auteur` = "'.$data_articles['id_auteur'].'" ');
    $data_auteurs = mysql_fetch_array($req_auteurs);

    echo $data_articles['titre_article'].' rédigé par '.$data_auteurs['nom_auteur'].'<br />';
}
?>

Bref, il sélectionne l'id de l'auteur dans la table "articles", puis l'utilise dans une seconde requête sql qui cherche l'auteur correspondant dans la table "auteurs".

Grâce aux jointures, vous apprendrez à sélectionner toutes les informations dont vous avez besoin en une seule requête !


Pré-requis : les bases de données relationnelles Les jointures internes

Les jointures internes

Les jointures, quel intérêt ? Les jointures externes

Nous commencerons par l'étude de l'ancienne syntaxe des jointures internes, durant laquelle nous aborderons plusieurs notions utiles pour la suite du tutoriel. Nous passerons ensuite à l'étude de la nouvelle syntaxe, qu'il est désormais préférable d'utiliser.

Ancienne syntaxe

Requêtes multi-tables

Considérons un exemple simplifié : nous avons 2 tables "clients" et "commandes".

Structure :

clients

id_client

nom_client

commandes

id_commande

id_client

date_commande

Nous avons conçu notre base de données avec une relation 1 - N entre les 2 tables (via le champ "id_client"). Les clients peuvent en effet avoir passé plusieurs commandes.

Données :

id_client

nom_client

1

Jacques Dupont

2

Marcel Dubois

3

Nathalie Leroy

4

Marc Degraux

5

Julie Duchemin

id_commande

id_client

date_commande

1

4

26/01/2010

2

1

26/01/2010

3

1

28/01/2010

4

3

28/01/2010

5

4

28/01/2010

6

3

29/01/2010

7

5

29/01/2010

8

4

30/01/2010

9

1

01/02/2010

10

4

01/02/2010

11

5

01/02/2010

12

3

01/02/2010

N'essayez pas de chercher une logique dans les id_client, ceci est un exemple aléatoire, chaque client peut commander à n'importe quel moment (et même plusieurs fois le même jour). On notera que jusqu'à présent Marcel Dubois (id_client = 2) n'a pas passé de commande.

Effectuons maintenant une requête multi-tables, en l'occurence la sélection des données de nos 2 tables en même temps (on va donc joindre nos 2 tables).

SELECT * FROM `clients`,`commandes`

Vu que nous n'avons pas spécifié de critère de jointure dans notre requête (c'est-à-dire la manière dont on doit joindre les 2 tables), nous obtenons comme résultat l'ensemble des relations/combinaisons possibles entre les deux tables.

Ainsi, chaque ligne de la table "clients" est associée à chaque ligne de la table "commandes". En tout dans notre exemple on obtient donc comme résultat 60 lignes (5 lignes de la table clients multipliées par 12 lignes de la table commandes) qui sont les 60 relations/combinaisons possibles entre les lignes de nos 2 tables.

En données cela donne :

Image utilisateur(cliquez pour agrandir)

Utilisation du WHERE

Heureusement notre exemple comporte peu de données. Mais si vous travaillez avec beaucoup de données, l'absence de critère de jointure peut faire souffrir votre serveur pour, au final, vous retrouver avec un résultat peu utile. Nous allons voir comment définir un critère de jointure.

Un des critères de jointure auquel on pense est évidemment la relation que nous avons définie entre nos deux tables lors de la conception de la base de données, à savoir la relation via le champ "id_client".

Notre requête SQL avec critère de jointure se construit comme suit :

SELECT * FROM `clients`,`commandes` WHERE `clients`.`id_client` = `commandes`.`id_client`

On obtient comme résultat :

Image utilisateur

On a effectué ce qu'on appelle une jointure interne entre nos deux tables.

On constate qu'il ne reste que 12 lignes sur 60 une fois qu'on a introduit notre critère de jointure.

Notons notamment la disparition des lignes contenant "Marcel Dubois" (id_client = 2). Cela s'explique par le fait que Marcel Dubois n'a passé aucune commande, il n'est donc pas présent dans la table commandes.

Nouvelle syntaxe

Afin d'améliorer la lisibilité des requêtes (notamment bien séparer ce qui relève du filtrage et ce qui relève de la jointure), et dans une certaine mesure de les optimiser, une nouvelle syntaxe a été introduite pour les jointures.

Notre requête :

SELECT * 
FROM `clients`,`commandes`
WHERE `clients`.`id_client` = `commandes`.`id_client`

Donne en nouvelle syntaxe :

SELECT * 
FROM `clients`
INNER JOIN `commandes`
ON `clients`.`id_client` = `commandes`.`id_client`

Bref, il n'y a plus qu'une seule table dans le FROM, et pour chaque table que l'on désire joindre on doit utiliser INNER JOIN suivi du nom de la table, puis ON suivi du critère de jointure.

Exemple de double jointure avec critères de sélection :

SELECT * 
FROM `clients`
JOIN `commandes`
ON `clients`.`id_client` = `commandes`.`id_client`
JOIN `produits`
ON `commandes`.`id_commande` = `produits`.`id_commande`
WHERE `clients`.`code_postal` = "75000"
ORDER BY `commandes`.`date_commande` ASC

Les jointures, quel intérêt ? Les jointures externes

Les jointures externes

Les jointures internes Restons lisibles !

LEFT JOIN

Exemple :

SELECT *
FROM TableGauche
LEFT JOIN TableDroite
ON critère de jointure

Le LEFT JOIN (ou LEFT OUTER JOIN) implique que l'on sélectionne toutes les lignes respectant le critère de jointure, puis on ajoute toutes les lignes de la table "TableGauche" qui ont été rejetées car elles ne respectaient pas le critère de jointure.

Appliquons le LEFT JOIN à l'exemple utilisé pour les jointures internes :

SELECT * 
FROM `clients`
LEFT JOIN `commandes`
ON `clients`.`id_client` = `commandes`.`id_client`

En données, cela donne :

Image utilisateur

Bref, on a le même résultat qu'avec INNER JOIN, auquel on a juste ajouté les éléments de la table de gauche (en l'occurence "clients") qui ne respectaient pas le critère de jointure.
On voit donc réapparaître Marcel Dubois (id_client = 2) qui n'avait passé aucune commande, et n'était donc pas présent dans la table "commandes", et ne respectait donc pas au moins une fois le critère de jointure (ce qui l'avait exclu des résultats de la jointure interne).

Exemple :

SELECT * 
FROM `tablea` 
LEFT JOIN `tableb` 
USING (`champ1`,`champ2`,`champ3`)

est équivalent à :

SELECT * 
FROM `tablea`
LEFT JOIN `tableb`
ON `tablea`.`champ1` = `tableb`.`champ1` AND `tablea`.`champ2` = `tableb`.`champ2` AND `tablea`.`champ3`= `tableb`.`champ3`

RIGHT JOIN

Le RIGHT JOIN (ou RIGHT OUTER JOIN) implique que l'on sélectionne toutes les lignes respectant le critère de jointure, puis on ajoute toutes les lignes de la table "TableDroite" qui ont été rejetées car elles ne respectaient pas le critère de jointure.

FULL JOIN

Le FULL JOIN (ou FULL OUTER JOIN) implique que l'on sélectionne toutes les lignes respectant le critère de jointure, puis on ajoute toutes les lignes de la table "TableGauche" et de la table "TableDroite" qui ont été rejetées car elles ne respectaient pas le critère de jointure.

A noter que le FULL JOIN n'existe pour le moment pas sous MySQL.


Les jointures internes Restons lisibles !

Restons lisibles !

Les jointures externes Pour aller plus loin

Les Alias

Pour comprendre l'utilité des alias, prenons une requête multi-tables :

SELECT * 
FROM `clients`,`commandes` 
WHERE `clients`.`id_client` = `commandes`.`id_client`

Cette requête-ci est facile à lire. Cependant, certaines requêtes peuvent vite devenir assez longues lorsque l'on travaille avec plus de deux tables. Pour améliorer la lisibilité de la requête, il existe ce qu'on appelle les alias, qui sont des surnoms que l'on donne aux tables.

A noter qu'une fois un alias attribué à une table on peut toujours utiliser son nom complet, les deux fonctionnent.

Pour attribuer un alias à une table, il suffit simplement de l'ajouter dans le FROM juste après le nom de la table (avec un espace entre le nom complet de la table et son alias). Le but étant d'alléger le code, les alias comptent généralement peu de caractères.

Avec notre exemple, cela pourrait donner :

SELECT * 
FROM `clients` CL,`commandes` COM 
WHERE CL.`id_client` = COM.`id_client`

Même principe avec la nouvelle syntaxe :

SELECT * 
FROM `clients` CL
JOIN `commandes` COM 
ON CL.`id_client` = COM.`id_client`

Dans les requêtes multi-tables, il peut arriver que deux champs de tables différentes portent le même nom. Cela ne pose pas de problème dans la requête elle-même étant donné que l'on précise à chaque fois la table à laquelle appartient le champ, mais cela peut par contre être problématique dans l'exploitation PHP du résultat de la requête.

Exemple :

SELECT A.`quantite`,B.`quantite` 
FROM `tablea` A, `tableb` B

Dans cet exemple, nous avons 2 tables ("tablea" et "tableb") qui contiennent deux champs portant le même nom ("quantite").

Lorsque nous utilisons notre habituel mysql_fetch_array (ou mysql_fetch_object) pour récupérer les données issues de la requête, nous ne sommes pas en mesure (sans diminuer la lisibilité du code) de différentier les 2 champs.

Pour remédier à ce problème, il suffit d'attribuer un alias au champ dans la requête :

SELECT A.`quantite`,B.`quantite` AS `quantite_bis`
FROM `tablea` A, `tableb` B

Ainsi, pour faire appel au champ "quantite" de la table "tableb", on utilisera désormais son alias :

<?
$req = mysql_query('SELECT A.`quantite`,B.`quantite` AS `quantite_bis` FROM `tablea` A, `tableb` B');
while ($data = mysql_fetch_array($req))
{
echo $data['quantite']; //affiche le contenu du champ "quantite" de la table "tablea"

echo $data['quantite_bis']; //affiche le contenu du champ "quantite" de la table "tableb"
}
?>

Les jointures externes Pour aller plus loin

Pour aller plus loin

Restons lisibles !

Conditions de jointure

Dans les conditions de jointure on peut utiliser une égalité, mais également d'autres opérateurs de comparaison :

Opérateur

Signification

>

supérieur

>=

supérieur ou égal

<

inférieur

<=

inférieur ou égal

<>

différent de

IN

dans un ensemble

LIKE

correspondance partielle

BETWEEN .. AND ..

entre deux valeurs

EXISTS

dans une table

Source

La jointure croisée

La jointure croisée est simplement la nouvelle syntaxe pour les requêtes multi-tables sans critère de sélection WHERE que nous avons abordées au tout début de la partie sur les jointures internes.

SELECT * FROM `table1`,`table2`

devient :

SELECT * FROM `table1` CROSS JOIN `table2`

La jointure naturelle

Elle permet d'éviter de devoir spécifier le critère de jointure. Le compilateur SQL se charge de rechercher dans les 2 tables les champs qui ont un nom identique.

SELECT * 
FROM `clients` 
NATURAL JOIN `commandes`

équivaut donc à :

SELECT * 
FROM `clients` 
JOIN `commandes`
ON `clients`.`id_client` = `commandes`.`id_client`

Auto-jointures

Il est possible de joindre une table à elle-même. Pour ce faire, il conviendra d'utiliser les alias.

Exemple :

SELECT *
FROM `tablea` T1
JOIN `tablea` T2
ON T1.champ = T2.champ

Quelques références

Voilà, j'espère que ce tutoriel vous sera utile et vous a donné envie d'en savoir plus sur les jointures. De tout ceci, retenez une chose : exploitez un maximum les possibilités offertes par SQL avant d'avoir recours à PHP, cela rendra vos scripts d'autant plus efficaces. Je vous invite d'ailleurs à lire les autres tutoriels SQL présents sur le Site du Zéro ! ;)


Restons lisibles !