Base pour les exemples
Pour expliquer les notions en jeu, nous allons utiliser la base du tutoriel du site de SQLite (voir cette page).
La base de données utilisée présente deux tables
La table COMPANY
| id | nom | age | adresse | salaire |
|---|
| 1 | Paul | 32 | California | 20000 |
| 2 | Allen | 25 | Texas | 15000 |
| 3 | Teddy | 23 | Norway | 20000 |
| 4 | Mark | 25 | Rich-Mond | 65000 |
| 5 | David | 27 | Texas | 85000 |
| 6 | Kim | 22 | South-Hall | 45000 |
| 7 | James | 24 | Houston | 10000 |
La table SERVICE
Elle est définie par le code :
CREATE TABLE "SERVICE" (
`id_service` INTEGER NOT NULL,
`denomination` TEXT NOT NULL,
`chef_de_service` INTEGER NOT NULL,
PRIMARY KEY(id_service),
FOREIGN KEY(`chef_de_service`) REFERENCES COMPANY
)
chef_de_service est une clef étrangère faisant référence à l'id de la table COMPANY.
Son contenu est le suivant :
| id_service | denomination | chef_de_service |
| 1 | comptabilité | 4 |
| 2 | facturation | 6 |
| 3 | ingénierie | 7 |
On lit dans cette table que le chef de service du service comptabilité a l'identifiant 4, il s'agit donc de Mark du Rich-Mond.
Source
Vous pouvez télécharger ici cette base pour tester les exemples qui suivent. Prenez le temps de tester chacune des requêtes proposées.
Produit cartésien
Le cross join (produit cartésien) de deux tables est obtenu de la façon suivante :
SELECT * FROM COMPANY cross join SERVICE;
Testez ce code et observez le résultat : chaque ligne de la première table est complétée par chaque ligne de la seconde table.
Si table_1 contient n lignes et table_2 est une table contenant p lignes, l'instruction select * from table_1 cross join table_2 sera donc une table contenant n×p lignes.
Inner join
L'inner-join (jointure interne) de deux tables est une partie du cross-join, obtenue en ne gardant que les lignes pour lesquelles un champ commun aux deux tables présente des valeurs égales.
Dans notre exemple, la commande suivante :
SELECT nom, denomination FROM COMPANY inner join SERVICE on chef_de_service = id;
permet de récupérer le nom des chefs de service.
Remarque : il peut arriver que deux tables aient une colonne portant le même nom. Dans une requête de jointure, il y aura donc une ambiguïté. Cela se règle en préfixant le nom de la colonne par le nom de la table. La requête précédente pourrait ainsi se réécrire comme suit :
SELECT nom, denomination FROM COMPANY inner join SERVICE on SERVICE.chef_de_service = COMPANY.id;
Outer join
L'outer-join (jointure externe) se construit comme l'inner join. Mais les lignes qui ne correspondent pas à la demande sont affichées et complétées par un blanc sur la seconde table.
Un exemple éclaircira ce principe. La commande suivante :
SELECT nom, denomination FROM COMPANY left outer join SERVICE on SERVICE.chef_de_service = COMPANY.id;
permet de récupérer le nom des chefs de service et affiche également par exemple une ligne Paul avec un vide dans la colonne denomination, on sait donc que Paul n'est pas chef d'un service. On construit ainsi une table ayant deux attributs : "nom de l'employé, nom du service", le nom du service n'étant renseigné que si l'employé en est le chef.
Le left du code ci-dessus signifie que ce qui est affiché est la colonne de gauche (et la droite est laissée en blanc lorsque le critère n'est pas satisfait). Il existe aussi sur la plupart des SGBD un right et un full outer join (qui n'existent pas en sqlite).