SpatiaLite Cookbook

	Recette #2: Vos premières jointures
Février 2011

Précedent

Table des matières

A partir de maintenant, nous allons effectuer des requêtes sur deux tables et plus... en même temps.
C'est la qu'intervient la clause SQL JOIN.

SELECT *
FROM counties, regions;

county_id	county_name	car_plate_code	region_id	region_id	region_name
1	TORINO	TO	1	1	PIEMONTE
1	TORINO	TO	1	2	VALLE D'AOSTA
1	TORINO	TO	1	3	LOMBARDIA
1	TORINO	TO	1	4	TRENTINO-ALTO ADIGE
1	TORINO	TO	1	5	VENETO
...	...	...	...	...	...

Apparemment cette requête semble marcher sans soucis;
mais un rapide coup d'oeil permet de s'apercevoir de plusieurs soucis:

un nombre surprenant de lignes
Chaque County semble être relié à chaque Region.

A chaque fois que l'on effectue une requête SQL sur deux tables, le produit cartésien des deux tables est calculé.
C.-à-d. que chaque ligne provenant de la première table est associée (JOINed) à l'ensemble des lignes de la deuxième table.
Les résultats de ce type de requêtes sont en général très peu intéressants et surtout très lourds ( nombreuses lignes).

Ainsi, il est clair qu'une condition de jointure doit être spécifiée afin de garder le contrôle sur les résultats, en ne gardant que les lignes d’intérêt.

SELECT *
FROM counties, regions
WHERE counties.region_id = regions.region_id;

Cette requête est exactement la même que la précédente, mais avec cette fois une condition de jointure.
Quelques remarques:

l'utilisation de plusieurs tables mène rapidement à des ambiguïtés:
e.g. ici par exemple, on se retrouve avec deux colonnes region_id, l'une dans la table counties et l'autre dans la table regions.
Afin d'éviter ce problème, une combinaison table/colonne est utilisée :
e.g. counties.region_id identifie la colonne region_id de la table counties d'une manière non ambiguë.
la clause WHERE counties.region_id = regions.region_id impose une condition de jointure appropriée.
Ceci permettra de filtrer le produit cartésien, de façon à ne garder que les lignes intéressantes.

SELECT c.county_id AS county_id,
  c.county_name AS county_name,
  c.car_plate_code AS car_plate_code,
  r.region_id AS region_id,
  r.region_name AS region_name
FROM counties AS c,
  regions AS r
WHERE c.region_id = r.region_id;

county_id	county_name	car_plate_code	region_id	region_name
1	TORINO	TO	1	PIEMONTE
2	VERCELLI	VC	1	PIEMONTE
3	NOVARA	NO	1	PIEMONTE
4	CUNEO	CN	1	PIEMONTE
5	ASTI	AT	1	PIEMONTE
6	ALESSANDRIA	AL	1	PIEMONTE
...	...	...	...	...

Cette table est la même que la précédente, avec quelques améliorations graphiques:

la clause AS permet de définir un alias pour les tables et les colonnes. Les requêtes deviennent alors plus compréhensibles.

SELECT lc.lc_id AS lc_id,
  lc.lc_name AS lc_name,
  lc.population AS population,
  c.county_id AS county_id,
  c.county_name AS county_name,
  c.car_plate_code AS car_plate_code,
  r.region_id AS region_id,
  r.region_name AS region_name
FROM local_councils AS lc,
  counties AS c,
  regions AS r
WHERE lc.county_id = c.county_id
  AND c.region_id = r.region_id;

lc_id	lc_name	population	county_id	county_name	car_plate_code	region_id	region_name
1001	AGLIE'	2574	1	TORINO	TO	1	PIEMONTE
1002	AIRASCA	3554	1	TORINO	TO	1	PIEMONTE
1003	ALA DI STURA	479	1	TORINO	TO	1	PIEMONTE
...	...	...	...	...	...	...	...

Joindre 3 tables (ou plus) n'est pas plus difficile:
il suffit simplement d'ajouter autant de conditions de jointure que nécessaire.

Performance

L’exécution de requêtes complexes impliquant différentes tables peut très vite devenir lent. Surtout lorsque les tables contiennent beaucoup de données. L'explication de ce phénomène est le temps de calcul nécessaire à la réalisation du produit cartésien.

Le comportement standard de SQLite est de scanner entièrement la table à chaque fois: évidemment, répété des milliers de fois, ceci augmente les temps de calcul. Ainsi, le point clef pour améliorer la performance des requêtes est de les optimiser afin d'éviter au maximum de scanner entièrement la table. Ainsi, de façon automatique, si SQLite détecte un INDEX approprié, il va l'utiliser afin de limiter les calculs. C'est là tout l’intérêt des INDEXes. Cependant, en contrepartie, la création d'un index utilise de l'espace disque et peut ralentir les étapes d'insertion/suppression de donner (nécessite de réactualiser simultanément les index). C'est pourquoi, la mise en place d'un index doit être réfléchi et non systématique.

Table des matières

	Author: Alessandro Furieri a.furieri@lqt.it Traduced from English by RIVIERE Romain
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.