Comment effectuer une jointure de tables dans une base de données avec la clause SQL JOIN ?

En SQL, pour bénéficier de la puissance d’un système de gestion de base de données, il faut parfois combiner différentes tables de données afin d’obtenir un résultat. Pour effectuer cette combinaison, il faut faire de la jointure à l’aide des commandes SQL JOIN. Dans cet article, vous allez découvrir comment faire et optimiser la jointure en SQL.

Généralités

En général, la jointure est le fait d’associer des lignes de deux différentes tables afin d’obtenir une information. Par exemple, en considérant deux tables, une table utilisateurs et une table adresse. En utilisant la jointure, on peut avoir l’adresse et les informations de l’utilisateur en une seule requête.

Un autre exemple serait sur un site web. Imaginez que le site possède une table d’articles où se trouve le titre, le contenu et la date de publication et une autre table pour les rédacteurs où se trouve le nom, prénom, etc. des rédacteurs. Avec une seule requête, vous pouvez afficher un article avec le nom du rédacteur.

Principe de fonctionnement de SQL JOIN

Le fonctionnement de base de SQL JOIN peut être expliqué grâce aux sous-opérations de base de données. Le fonctionnement de SQL-JOIN est axé sur les deux opérations d’algèbres relationnelles qui sont :

• Le produit cartésien ;
• La sélection.

Les différents types de jointure

Pour illustrer ces types de jointure, considérons les tables suivantes :

Nous avons tout d’abord la table ville :

Et la table email :

INNER JOIN

Cette jointure est interne et retourne un résultat quand la condition est vraie dans les deux tables. 

D’après le schéma ci-dessus, INNER JOIN va réunir les tuples des 2 tables si la condition est vérifiée dans les deux tables. Le type de condition est varié, mais typiquement, elle peut être l’égalité d’un attribut en commun. Il est donc nécessaire de préciser les attributs que vous voulez utiliser pour faire la jointure.  Pour préciser cela, il faut utiliser le mot ON suivi de l’égalité que vous voulez. 

C’est la requête SQL qui prend le moins d’informations, puisqu’ elle ne considère que les lignes des deux tables qui vérifient la condition.

Exécutons la requête suivante afin d’illustrer cela :

SELECT `ville`.`id_ville`, nom, prenom, ville
FROM ville
INNER JOIN email
ON `ville`.`id_ville` = `email`.`id_email`

Le résultat de cette commande est le suivant :

CROSS JOIN

Cette jointure permet de croiser deux tables, c’est-à-dire la jonction de chaque ligne de la première table avec la deuxième. Autrement dit, il s’agit d’effectuer le produit cartésien des deux tables en question. Par exemple, pour notre table ville qui contient 8 enregistrements et la table email qui en contient 5, le résultat de ce type de jointure sera 40 lignes de données. En réalité, cela va retourner chaque ligne de la première table associée avec chaque ligne de la deuxième.

Sa syntaxe est la suivante :

SELECT * FROM A
CROSS JOIN B

LEFT JOIN

Cette jointure est externe et retourne les enregistrements de la table de gauche, quel que soit l’état de la condition (vérifiée ou non). En cas d’absence de correspondance, la valeur par défaut est NULL. Il faut aussi préciser les attributs à utiliser pour effectuer la jointure et spécifier les tables concernées dans LEFT JOIN.

On va maintenant lancer la commande LEFT JOIN avec les tables utilisateur et email. Ici, email est la table de gauche :

SELECT `email`.`id_email`, nom, prenom, ville
FROM email
LEFT JOIN ville
ON `email`.`id_email` = `ville`.`id_ville`

Le résultat obtenu est le suivant :

Si vous souhaitez en apprendre davantage sur LEFT JOIN, nous en avons fait tout un article au cours de cette série sur SQL.

RIGHT JOIN

Cette jointure est également externe, mais elle retourne cette fois-ci les enregistrements de la table de droite, quel que soit l’état de la condition (vérifiée ou non). Comme le cas des autres commandes, il faut aussi renseigner les attributs à utiliser pour la jointure.

Pour ce cas, on applique la RIGHT JOIN en fonction de la table ville. Le résultat attendu est les lignes de la table ville qui possèdent une correspondance dans la table email. On va donc pour cela exécuter la commande suivante :

SELECT `email`.`id_email`, nom, prenom, ville
FROM email
RIGHT JOIN ville
ON `email`.`id_email` = `ville`.`id_ville`

Et voici le résultat :

FULL JOIN

Comme pour les deux précédentes commandes, cette jointure est externe et donne un résultat si la condition est vraie dans au moins une des tables.

Dans le cas de FULL JOIN, même si certaines lignes n’ont pas de valeurs correspondantes dans l’une des tables, toutes les informations sont présentes. C’est la jointure qui permet de garder le maximum d’informations.

Reprenons nos tables d’exemple et exécutons la requête suivante :

SELECT `id_ville`, nom, prenom, ville
FROM ville
FULL JOIN email
ON `id_ville` =  `email`.`id_email`

Le résultat obtenu est le suivant :  

SELF JOIN

Il s’agit de la commande qui fait la jointure sur la table elle-même. Oui, cela est possible avec SQL. Cette jointure est différente des autres et très pratique quand une table doit lier des informations avec les enregistrements de la même table. 

La syntaxe de base de cette clause est la suivante :

SELECT `t1`.`nom_colonne1`, `t1`.`nom_colonne2`, `t2`.`nom_colonne1`, `t2`.`nom_colonne2`
FROM `table` as `t1`
LEFT OUTER JOIN `table` as `t2` ON `t2`.`fk_id` = `t1`.`id`

NATURAL JOIN

C’est une jointure naturelle entre deux tables, c’est-à-dire elle donne les résultats de manière naturelle. La seule condition pour que cette commande marche est qu’il faut au moins une colonne avec le même nom dans les deux tables et nul besoin de le spécifier. Par exemple, la requête qui suit :

SELECT `id_ville`, nom, prenom, ville
FROM ville
NATURAL JOIN email

permet d’obtenir le résultat ci-dessous :

UNION JOIN

UNION JOIN est une jointure d’union. Contrairement aux autres types de jointures, cette jointure ne cherche pas la correspondance entre les lignes des tables, mais crée plutôt une table virtuelle qui contient l’union des deux tables sources. La syntaxe de base de cette jointure est la suivante :

SELECT * FROM table1
UNION
SELECT * FROM table2

SQL JOIN avec DELETE

Dans une instruction DELETE, on peut effectuer une opération de jointure avec SQL JOIN. Cette instruction peut prendre 0 ou plusieurs opérations JOIN en même temps. La présence de DELETE permet d’effacer les lignes qui vérifient la condition requise.

Essayons par exemple de supprimer les lignes de la table ville qui n’ont pas de correspondance dans celle d’email :

DELETE
FROM email
RIGHT JOIN ville
ON email.id_email = ville.id_ville
WHERE email.id_email IS NULL;

Utiliser l’auto jointure pour interroger les données hiérarchiques

Une autojointure est le fait d’effectuer la jonction sur la table elle-même.  Cette jointure consiste à comparer les lignes qui se trouvent au sein de cette table et aussi lancer des requêtes sur les données hiérarchiques.

L’autojointure se sert du principe de la jointure gauche ou jointure interne, car la requête utilisée fait référence à la table elle-même. 

Voici la syntaxe de l’autojointure sur une table nommée T :

SELECT
    select_list
FROM
    T t1
[INNER | LEFT]  JOIN T t2 ON
    join_predicate; 

La requête fait deux fois référence à la table T. t1 et t2 sont des alias utilisés dans la requête pour donner deux noms différents à la table T.

Maintenant, considérons la structure de la table suivante : 

Avec l’autojointure, vous pouvez rechercher les clients se trouvant dans la même ville. Pour cela, il faut taper la requête suivante : 

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id
AND c1.city = c2.city
ORDER BY
    city,
    customer_1,
    customer_2;

Une fois la requête exécutée, vous obtenez le résultat suivant :

La requête ne fait pas la comparaison avec le même client grâce à cette instruction : 

c1.customer_id > c2.customer_id

Et la condition suivante correspond à la ville des deux clients :

AND c1.city = c2.city

Optimiser les jointures pour Hadoop

Pour Hadoop, les jointures sont un grand défi à relever, car le cluster est énormément sollicité afin d’arriver à traiter les données nécessaires pour pouvoir joindre plusieurs tables. Pour ce faire, nous avons recours à d’autres types de jointures supplémentaires.

Map Join

Le map join est le fait de joindre plusieurs tables en effectuant qu’une seule action Map (Map-Only-Job) au lieu d’une étape Map Reduce. L’avantage d’utiliser Map Join est qu’il traite avec soin plusieurs tables de tailles différentes.

Skew Join

Généralement, les jointures de deux grandes tables sont réalisées par un traitement MapReduce. Cela débute d’abord par le tri des tables à l’aide de la clé de jointure. Le Skew Join aide à reconnaître le traitement d’une clé massivement représenté par rapport aux autres.

En conclusion, il faut retenir que l’instruction JOIN permet d’effectuer la jonction d’une ou de plusieurs tables. Elle est toujours accompagnée de l’instruction ON et c’est ce qui permet de déterminer les liens entre les tables. En Big Data, notamment avec Hadoop, son rôle est d’éviter que le Reducer en charge de cette action entre en conflit avec le traitement de Map Reduce.

Voilà, c’est ici que s’achève cet article sur le SQL JOIN. Si vous souhaitez augmenter vos compétences en Big Data, nous vous invitons à télécharger cette formation sur Spark avec Scala.