SQL JOIN : des requêtes sur plusieurs tables de données
SQL JOIN : des requêtes sur plusieurs tables de données
SQL JOIN est une opération dans les bases de données relationnelles qui permet d’effectuer des requêtes sur plusieurs tables de bases de données. Les JOINs combinent les données stockées dans différentes tables et les exécutent sous forme filtrée dans une table de résultats.
Le principe SQL JOIN est basé sur l’opération d’algèbre relationnelle du même nom : il s’agit d’une une combinaison de produit cartésien et de sélection. L’utilisateur détermine les données des tables de sortie qui doivent être transférées à la table des résultats en sélectionnant un type de JOIN et en définissant une condition de sélection.
Nous vous présenterons les bases mathématiques de SQL JOINs, comparerons différents types de JOINs et vous montrerons comment implémenter des JOINs dans des requêtes de base de données via SQL à l’aide d’exemples pratiques.
Notre article sur SQL JOIN nécessite la connaissance de certains concepts du modèle de base de données relationnelle pour être compris, notamment en ce qui concerne les relations, les tuples, les attributs ou les clés. Pour en savoir plus, vous pouvez consulter notre article d’introduction aux fondamentaux des bases de données relationnelles.
Comment fonctionnent les SQL JOINs ?
Le principe de base de SQL-JOIN peut être illustré en faisant dériver l’opération de la base de données de ses sous-opérations. Chaque JOIN est basé sur les opérations d’algèbre relationnelle suivantes :
- Le produit cartésien
- La sélection
Le produit cartésien
Le produit cartésien (ou produit croisé) est une opération de la théorie des ensembles dans laquelle deux ou plusieurs ensembles sont liés ensemble. Dans le modèle de base de données relationnelle, le produit cartésien est utilisé pour relier des ensembles de n-tuples sous forme de tables. Le résultat de cette opération est à nouveau un ensemble de tuples ordonnés, où chaque tuple est constitué d’un élément de chaque ensemble initial.
Dans l’algèbre relationnelle, le signe de multiplication (×) est utilisé comme opérateur du produit cartésien.
Un exemple pour illustrer :
Le produit cartésien A × B des deux ensembles A = {x, y, z} et B = {1, 2, 3} est :
A × B = {(x,1), (x,2), (x,3), (y,1), (y,2), (y,3), (z,1), (z,2), (z,3)}
Le graphique suivant permet d’y voir clair :
La séquence de formation des couples doit être respectée. Par exemple, le produit cartésien A × B ne correspond pas au même ensemble que le produit cartésien B × A.
A × B = {(x,1), (x,2), (x,3), (y,1), (y,2), (y,3), (z,1), (z,2), (z,3)}
B × A = {(1,x), (1,y), (1,z), (2,x), (2,y), (2,z), (3,x), (3,y), (3,z)}
Dans la terminologie SQL, une opération dans laquelle le produit cartésien est formé à partir de deux tables de base de données est appelée CROSS JOIN. Dans la pratique, les CROSS JOINs sont rarement utilisés en raison de l’ensemble des résultats non filtrés.
La sélection
La sélection est une opération d’algèbre relationnelle qui permet de sélectionner des tuples spécifiques à partir d’un ensemble initial et de les sortir comme un ensemble de résultats. Une comparaison des expressions peut être utilisée pour déterminer quels tuples sont inclus dans l’ensemble de résultats. Le résultat de la sélection est donc un ensemble de tuples qui remplissent la condition de sélection définie dans la comparaison des expressions. La lettre grecque Sigma (σ) est utilisée comme opérateur. L’opération est notée comme suit :
σF (R)
Le caractère de remplissage "F" correspond à la comparaison des expressions, une formule de prédicats logiques qui définissent la condition de sélection. R représente l’ensemble de données à sélectionner. Vous pouvez également utiliser la notation linéaire R[F].
Pour formuler les conditions de sélection, vous pouvez avoir recours aux opérateurs de comparaison habituels : par exemple, égal à (=), supérieur à (>) ou inférieur à (<).
Voici une explication de la sélection à l’aide d’un exemple que nous avons déjà introduit dans notre texte sur les fondamentaux des modèles de base de données relationnelle. Le tableau suivant présente des données fictives qu’une entreprise aurait pu saisir sur ses salariés. Pour chaque salarié, le matricule (e_id), l’identité (nom, prenom), le numéro d’assurance sociale (numsecu), l’adresse (rue, nr, code-postal, ville) et la voiture de fonction affectée (car_id) sont indiqués.
Table : salaries
| e_id | nom | prénom | numsecu | rue | nr | code_postal | ville | car_id |
| 1 | Dupond | Jacques | 25 120512 S 477 | Rue du soleil | 1 | 11111 | Lacité | 3 |
| 2 | Martin | Jean | 25 100615 M 694 | Rue des champs | 2 | 22222 | Laville | 1 |
| 3 | Petit | Hélène | 25 091225 M 463 | Rue des étoiles | 3 | 33333 | Levillage | 1 |
| 4 | Lefevre | Lisa | 25 170839 K 783 | Rue de la mer | 4 | 44444 | Laforêt | 2 |
Si nous voulons sélectionner le tableau « salaries » de telle sorte que seuls les salariés conduisant la voiture de fonction car_id 1 soient affichés, nous pouvons procéder comme suit :
Σcar_id=1(salaries)
On récupère simplement les tuples où la valeur de la colonne car_id est égale à 1.
Le résultat correspond au tableau suivant :
Table : salaries (sélection)
| e_id | nom | prénom | numsecu | rue | nr | code-postal | ville | car_id |
| 2 | Martin | Jean | 25 100615 M 694 | Rue des champs | 2 | 22222 | Laville | 1 |
| 3 | Petit | Hélène | 25 091225 M 463 | Rue des étoiles | 3 | 33333 | Levillage | 1 |
Dans le langage de base de données SQL, les conditions de sélection sont définies à l’aide de la commande WHERE.
SELECT * FROM salaries WHERE salaries.car_id = 1;Si un tuple remplit la condition car_id = 1, les valeurs de toutes les colonnes doivent être éditées pour ce tuple.
L’astérisque (*) représente toutes les colonnes d’une table dans le SQL Syntax.
Combinaison du produit cartésien et de la sélection
Tous les types de JOIN usuels combinent le produit cartésien avec une condition de sélection. Afin d’expliquer le fonctionnement d’une telle base de données, nous réduisons le tableau « salaries » à quatre colonnes par souci de clarté. En outre, nous vous présentons le tableau « car », dans lequel vous trouverez des informations détaillées sur les types de véhicules détenus par l’entreprise.
Les deux tables sont liées l’une à l’autre par une relation de clé étrangère. La clé primaire de la table « car » (car_id) agit comme une clé étrangère dans la table « salaries ».
Table : salaries
| e_id | nom | prénom | car_id |
| 1 | Dupond | Jacques | 3 |
| 2 | Martin | Jean | 1 |
| 3 | Petit | Hélène | 1 |
| 4 | Lefevre | Lisa | 2 |
Tableau : car
| car_id | marque | modèle | immatriculation | construction | département |
| 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 2 | Opel | Astra | B PO 654 | 2010 | 41 |
| 3 | BMW | X6 | B MW 780 | 2017 | 37 |
L’externalisation des informations de différentes tables de base de données est un concept essentiel à connaître sur les modèles de base de données relationnelle. Ses avantages et sa mise en œuvre sont détaillés dans notre article sur la normalisation des bases de données relationnelles.
Si vous voulez fusionner les deux tables et sélectionner les tuples pertinents à votre projet en même temps, vous pouvez combiner les opérations de base de données précédemment introduites :
σcar_id=car_id(salaries × car)
Tout d’abord, le produit cartésien salaries × car est formé. Le résultat (intermédiaire) est un CROSS JOIN - un tableau de résultats dans lequel chaque tuple de la table "salaries" est combiné avec chaque tuple de la table "car".
Table : produit cartésien « salaries » × « car »
| e_id | nom | prénom | salaries.car_id | car.car_id | marque | modèle | immatriculation | construction | dép. |
| 1 | Dupond | Jacques | 3 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 1 | Dupond | Jacques | 3 | 2 | Opel | Astra | B PO 654 | 2010 | 41 |
| 1 | Dupond | Jacques | 3 | 3 | BMW | X6 | B MW 780 | 2017 | 37 |
| 2 | Martin | Jean | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 2 | Martin | Jean | 1 | 2 | Opel | Astra | B PO 654 | 2010 | 41 |
| 2 | Martin | Jean | 1 | 3 | BMW | X6 | B MW 780 | 2017 | 37 |
| 3 | Petit | Hélène | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 3 | Petit | Hélène | 1 | 2 | Opel | Astra | B PO 654 | 2010 | 41 |
| 3 | Petit | Hélène | 1 | 3 | BMW | X6 | B MW 780 | 2017 | 37 |
| 4 | Lefevre | Lisa | 2 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 4 | Lefevre | Lisa | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 41 |
| 4 | Lefevre | Lisa | 2 | 3 | BMW | X6 | B MW 780 | 2017 | 37 |
Ensuite, seuls les tuples pour lesquels le car_id de la table "car" correspond au car_id de la table "salaries" sont sélectionnés. La condition de sélection est que la clé étrangère de la table "salaries" corresponde à la clé primaire de la table "car".
Le résultat (final) est une table qui fusionne les deux tables sans redondance.
Table : JOIN sur « salaries » et « car »
| e_id | nom | prénom | salaries.car_id | car.car_id | marque | modèle | immatriculation | construction | dép. |
| 1 | Dupond | Jacques | 3 | 3 | BMW | X6 | B MW 780 | 2017 | 37 |
| 2 | Martin | Jean | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 3 | Petit | Hélène | 1 | 1 | VW | Caddy | B KH 778 | 2016 | 75 |
| 4 | Lefevre | Lisa | 2 | 2 | Opel | Astra | B PO 654 | 2010 | 41 |
Avec la combinaison d’un produit cartésien et la sélection ultérieure, les JOINs combinent les deux opérations en une seule. Le symbole bowtie (⋈) est utilisé comme opérateur.
Voici l’application :
σcar_id=car_id(salaries × car) := salaries⋈car_id=car_idcar
L’opération σcar_id=car_id(salaries × car) correspond à un JOIN via les tables "salaries" et "car" avec la condition car_id=car_id.
Transférée à la syntaxe SQL, l’opération ci-dessus correspondrait à l’instruction suivante :
SELECT * FROM salaries INNER JOIN car ON salaries.car_id = car.car_id;L’INNER JOIN est l’un des JOIN utilisés les plus importants pour les requêtes dans les bases de données. Parfois, cependant, des types de JOIN spéciaux sont nécessaires pour obtenir le résultat désiré.
Les types de SQL JOIN
Dans le modèle de base de données relationnelle, différents types de SQL JOIN sont utilisés. Ils permettent d’exécuter des requêtes en groupant des tables de base de données. La condition préalable est que les tables sélectionnées soient liées les unes aux autres à l’aide de relations clés étrangères.
Les types de JOIN les plus importants sont les suivants :
- INNER JOINs : un INNER JOIN est une forme filtrée du CROSS JOIN dans laquelle seuls les tuples des deux tables de sortie qui remplissent la condition de sélection définie par l’utilisateur sont fusionnés dans le jeu de résultats.
- OUTER JOINs : le OUTER JOIN est une extension de l’INNER JOIN. L’ensemble de résultats d’un OUTER JOIN contient les tuples des deux tables de sortie qui remplissent la condition de sélection définie par l’utilisateur, ainsi que tous les tuples restants de la première table, de la deuxième table, ou des deux tables. Les OUTER JOINS sont par conséquent implémentés comme LEFT OUTER JOIN, RIGHT OUTER JOIN ou FULL OUTER JOIN.
Les différences entre les INNER JOINs et les différentes variantes des OUTER JOINS peuvent être illustrées par un schéma représentant les ensembles. Le graphique suivant illustre les types de JOIN présentés :
Indépendamment de la distinction entre INNER JOIN et OUTER JOIN, les SQL JOIN peuvent également être classés comme les types de JOIN suivants :
- EQUI JOIN
- NON EQUI JOIN
- SELF JOIN
Les INNER JOINs et OUTER JOINs peuvent être implémentés comme EQUI JOINs et NON EQUI JOINs. Tous les exemples de JOIN présentés jusqu’à présent sont des EQUI JOINs. Les EQUI JOINs se caractérisent par le fait qu’ils n’autorisent que le signe égal comme opérateur de comparaison (=).
La condition de sélection d’un EQUI JOIN est donc toujours l’égalité des valeurs des colonnes.
En principe, cependant, les JOINS (ainsi que la sélection en algèbre relationnelle) ne sont pas limités à l’égalité des colonnes. Les opérateurs de comparaison possibles sont :
| Opérateur de comparaison | Signification |
| = | égal |
| < | Inférieur à |
| > | Supérieur à |
| ≤ | Inférieur ou égal |
| ≥ | Supérieur ou égal |
| <> | différent |
| != | différent |
Depuis SQL-92 le langage de base de données offre avec USING un raccourci pour EQUI JOINS. Toutefois, cela suppose que les colonnes concernées par la manipulation portent le même nom, ce qui n’est pas toujours le cas.
L’exemple suivant montre deux instructions SQL différentes qui mènent au même résultat. Dans la première instruction, l’opération JOIN est explicitement définie par le mot-clé ON. Pour la deuxième, nous utilisons un raccourci avec USING.
SELECT * FROM salaries INNER JOIN car ON salaries.car_id = car.car_id;SELECT * FROM salaries INNER JOIN car USING car_id;NON EQUI JOINs, en revanche, excluent les opérations basées sur l’égalité des colonnes. Ainsi, toutes les opérations de comparaison sont autorisées à l’exception du signe égal (=).
Puisque les relations dans les bases de données relationnelles sont généralement définies par l’égalité des clés primaires et étrangères, NON EQUI JOIN est d’importance secondaire dans le modèle de base de données relationnelle. D’autant plus que, comme pour les CROSS JOINs, ils conduisent souvent à un grand nombre d’ensembles de données de résultats.
Enfin, un SELF JOIN est une forme spéciale de SQL-JOIN dans laquelle une table de base de données est liée à elle-même. En principe, n’importe quel type de JOIN peut être exécuté comme un SELF JOIN.
Si deux tables sont liées par des colonnes du même nom, on parle de NATURAL JOIN. Un NATURAL JOIN est implémenté par défaut comme INNER JOIN avec le mot-clé du même nom. Les NATURAL JOIN ne sont toutefois pas fixés à ce type de JOIN. On peut également avoir un NATURAL LEFT OUTER JOIN ou NATURAL RIGHT OUTER JOIN.
Comme les NATURAL JOINs sont liés par des colonnes du même nom, les valeurs respectives ne sont pas éditées deux fois dans le jeu de résultats, mais sont fusionnées pour former une colonne commune. Des exemples de NATURAL JOINs sont disponibles dans les articles sur les INNER JOINs et OUTER JOINs.