JOINdre des tables SQL entre elles

SQL repose sur le concept de bases de données relationnelles : les tableaux peuvent être reliés entre eux et présenter des facettes différentes des mêmes entités.

Ainsi, un individu pourra avoir ses données fiscales enregistrées dans un tableau et ses données de santé dans un autre. Un identifiant individuel, appelé "clé" (key), reliera les tableaux entre eux. De même, une base de données enregistrant les données de consommation électrique journalières pourra être connectée avec une autre recensant les conditions météorologique afin de croiser les données et d'analyser leur relation possible.

La commande JOINpermet de relier entre elles les bases de données en produisant un tableau possédant chacune des colonnes des deux tableeaux de départ. Elle existe en plusieurs parfums, en fonction de la sélection des lignes que l'on souhaite réaliser. Pour la suite des exemples, on a les tableaux suivants :

schools_left

id	left_school
1	Oak Street School
2	Roosevelt High School
5	Washington Middle School
6	Jefferson High School

schools_right

id	right_school
1	Oak Street School
2	Roosevelt High School
3	Morrison Elementary
4	Chase Magnet Acadeny
6	Jefferson High School

JOIN

JOIN produit un nouveau tableau dont les lignes sont une intersection des deux tableaux de départ. Par exemple, si on le tableau 1 a les identifiants de ligne (1,2,3,4,7) et le tableau 2 a (2,3,5,6,7), le tableau d'arrivée aura (2,3,7).

La syntaxe de la commande JOIN est FROM table1 JOIN table2 ON table1.key1_col = table2.key2_col :

SELECT * FROM table a 
	JOIN table 
	ON table_a.key1_col = table_b.key2_col;

Les colonnes-clés sont précisées par table.key, comme on peut le voir. On la désigne aussi par INNER JOIN.

A noter : l'opérateur = n'est pas obligatoire : on peut aussi en utiliser d'autres, comme >= par exemple. Voir la note sur le sujetSELECTionner des données SQL
Introduction
Obtenir l'intégralité du tableau : *
La commande SELECT permet de sélectionner les données d'un tableau. L'instruction la plus simple retournant l'intégralité d'un tableau :

SELECT * FROM my_table;



Le nom du tableau est précisé avec le mot-clé FROM
L'opérateur * retourne tous les éléments disponibles.


Obtenir un sous-ensemble des colonnes du tableau : (col1, col2, ...)
On remplace *par le nom des colonnes qui nous intéresse :

SELECT some_column, another_column, amazing....

Exemple :

SELECT * 
FROM schools_left JOIN schools_right 
ON schools_left.id = schools_right.id;

id	left_school	id	right_school
1	Oak Street School	1	Oak Street School
2	Roosevelt High School	2	Roosevelt High School
6	Jefferson High School	6	Jefferson High School

On le voit, seules les lignes communes aux deux tableaux sont représentées.

LEFT JOIN et RIGHT JOIN

LEFT JOIN et RIGHT JOIN produisent un tableau contenant toutes les lignes d'une table et des lignes vides pour celles qui ne sont pas communes aux deux tableaux. Le tableau de référence (qui restera complet) est désigné par la commande LEFT (le premier tableau) ou RIGHT (le second).

Avec LEFT_JOIN :

SELECT * 
FROM schools_left LEFT JOIN schools_right 
ON schools_left.id = schools_right.id;

id	left_school	id	right_school
1	Oak Street School	1	Oak Street School
2	Roosevelt High School	2	Roosevelt High School
5	Washington Middle School	NULL	NULL
6	Jefferson High School	6	Jefferson High School

Avec RIGHT_JOIN:

id	left_school	id	right_school
1	Oak Street School	1	Oak Street School
2	Roosevelt High School	2	Roosevelt High School
NULL	NULL	3	Morrison Elementary
NULL	NULL	4	Chase Magnet Acadeny
6	Jefferson High School	6	Jefferson High School

FULL OUTER JOIN

FULL OUTER JOIN produit un tableau contenant l'union des lignes de chacun des deux tableaux :

SELECT * 
FROM schools_left FULL OUTER JOIN schools_right 
ON schools_left.id = schools_right.id;

id	left_school	id	right_school
1	Oak Street School	1	Oak Street School
2	Roosevelt High School	2	Roosevelt High School
5	Washington Middle School	NULL	NULL
6	Jefferson High School	6	Jefferson High School
NULL	NULL	4	Chase Magnet Acadeny
NULL	NULL	3	Morrison Elementary

CROSS JOIN

CROSS JOIN est un peu particulier et s'apparente au produit cartésien. Il retourne un tableau présentant toutes les combinaisons possibles de lignes entre les deux tableaux :

SELECT * 
FROM schools_left CROSS JOIN schools_right;

id	left_school	id	right_school
1	Oak Street School	1	Oak Street School
1	Oak Street School	2	Roosevelt High School
1	Oak Street School	3	Morrison Elementary
1	Oak Street School	4	Chase Magnet Acadeny
1	Oak Street School	6	Jefferson High School
2	Roosevelt High School	1	Oak Street School
2	Roosevelt High School	2	Roosevelt High School
2	Roosevelt High School	3	Morrison Elementary
2	Roosevelt High School	4	Chase Magnet Acadeny
2	Roosevelt High School	6	Jefferson High School
5	Washington Middle School	1	Oak Street School
5	Washington Middle School	2	Roosevelt High School
5	Washington Middle School	3	Morrison Elementary
5	Washington Middle School	4	Chase Magnet Acadeny
5	Washington Middle School	6	Jefferson High School
6	Jefferson High School	1	Oak Street School
6	Jefferson High School	2	Roosevelt High School
6	Jefferson High School	3	Morrison Elementary
6	Jefferson High School	4	Chase Magnet Acadeny
6	Jefferson High School	6	Jefferson High School

Manipuler plus finement les jonctions

Sélectionner des colonnes particulières

Les colonnes trouvées dans le tableau d'arrivée sont précisées après SELECT. Pour éviter les ambiguités (par exemple, si les deux tableaux possèdent des colonnes synonymes), il faut préciser le nom_du_tableau.nom_colonne.

En effet, la requête suivante renvoie une erreur :

SELECT id
FROM schools_left LEFT JOIN schools_right
ON schools_left.id = schools_right.id;

La manière correcte :

SELECT schools_left.id,
	schools_left.left_school,
	schools_right.right_school
FROM schools_left LEFT JOIN schools_right
ON schools_left.id schools_right.id;

id	left_school	right_school
1	Oak Street School	Oak Street School
2	Roosevelt High School	Roosevelt High School
5	Washington Middle School
6	Jefferson High School	Jefferson High School

Simplifier les commandes JOIN avec des alias

Changer le nom des tables utilisées

Il est possible de changer le nom de la colonne en utilisant AS: SELECT schools_left.id AS left_id, ...

Changer le nom des tables utilisées

Les requêtes pouvant devenir complexes à lire pour des opérations de grandes taille, on peut définir alors un alias afin de simplifier leur écriture.

Par exemple, la colonne naissances du tableau population_franche_comté_2020_12 se désigne normalement par population_franche_comté_2020_12.naissances. En utilisant AS au moment de nommer la table, on peut désigner la colonne simplement comme pop20_12_fc.naissances.

Un autre exemple :

SELECT lt.id,
	lt.left_school,
	rt.right_school
FROM schools left AS lt LEFT JOIN schools right AS rt
ON lt.id = rt.id;

Joindre plusieurs tableaux

Il suffit de chainer les comandes JOIN. Illustration :

On a les trois tables suivantes que l'on veut fusionner :

schools_left

id	left_school
1	Oak Street School
2	Roosevelt High School
5	Washington Middle School
6	Jefferson High School

schools_enrollment

id	enrollment
1	360
2	1001
5	450
6	927

schools_grades

id	grades
1	K-3
2	9-12
5	6-8
6	9-12

SELECT lt.id, lt.left_school, en.enrollment, gr.grades
FROM schools_left AS lt
    LEFT JOIN schools_enrollment AS en
        ON lt.id = en.id
    LEFT JOIN schools_grades AS gr
        ON lt.id = gr.id;

Résultat :

id	left_school	enrollment	grades
1	Oak Street School	360	K-3
2	Roosevelt High School	1001	9-12
5	Washington Middle School	450	6-8
6	Jefferson High School	927	9-12