Guinguette Marais Poitevin

Je n’oublierai jamais l’une de mes premières missions SQL en tant que jeune développeur junior dans le gouvernement fédéral. Je devais produire un rapport pour l’un de nos clients afin de l’aider à générer certaines statistiques pour le cabinet du ministre. Lorsque les chiffres du rapport différaient largement de leurs propres estimations, ils ont convoqué une réunion entre mon patron et moi. Je n’avais aucune idée de l’endroit où je m’étais trompé, ni même de ce que j’avais fait, jusqu’à ce que ma patronne me lance : « Robert, tu as créé un produit cartésien ». Après qu’elle ait fini de me réprimander devant nos clients, la réunion a été ajournée et je suis parti réécrire ma requête. J’ai fini par trouver ce qu’ils cherchaient, mais ma patronne ne m’a jamais laissé le temps de m’en remettre, et m’en a parlé à chaque réunion par la suite : « Robert, tu te souviens de la fois où tu as créé un produit cartésien… ». J’ai fini par être transféré dans un autre département environ six mois plus tard. Il est vrai que mon patron n’était pas particulièrement indulgent, mais des erreurs de ce genre peuvent vraiment nuire à votre carrière. Aujourd’hui, j’aimerais partager avec vous ce que j’ai appris sur les produits cartésiens au fil des ans, afin que vous puissiez les repérer et les bannir à jamais de vos requêtes SELECT.

Comment générer un produit cartésien

La requête suivante extrait des données de deux tables sans filtre d’aucune sorte. L’omission de la clause WHERE peut être utile dans les situations où vous voulez voir toutes les lignes d’une table mais souhaitez réorganiser ou masquer les colonnes non pertinentes :

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM employees, shops; 

Le problème ici est que la requête sélectionne dans plusieurs tables. Sans aucune jointure de table explicite, nous nous retrouvons avec une sorte de jointure par défaut appelée jointure cartésienne (ou jointure croisée). Le nom de jointure croisée fait référence au fait qu’elle joint chaque ligne de la première table à chaque ligne de la deuxième table. En d’autres termes, les jointures cartésiennes représentent la somme du nombre de colonnes des tables d’entrée plus le produit du nombre de lignes des tables d’entrée.

Vous pouvez voir dans les résultats que chaque ligne de la première table (employés) est renvoyée pour chaque ligne de la deuxième table (magasins). Comme il y a trois lignes dans la table shops, la requête produit trois de chaque ligne de la table employees :

C’est beaucoup de lignes pour deux petites tables. Les résultats croissent de manière exponentielle lorsque davantage de rangées et/ou de tables sont impliquées. En raison de la pression qu’une telle requête exerce sur les ressources du système et du fait que l’ensemble de données résultant contient beaucoup trop d’informations pour que le rédacteur de la requête puisse sélectionner ce qui est intéressant, les jointures cartésiennes sont presque toujours effectuées par accident. Comme nous l’avons appris dans mon propre récit édifiant, il est bon de savoir comment en repérer une avant que vos clients ou votre supérieur ne reviennent vers vous en vous demandant pourquoi il y a tant de lignes en double. En effet, la présence de nombreux doublons, combinée à un ensemble de résultats inhabituellement grand, est un signe révélateur que vous pourriez avoir un produit cartésien sur les bras.

Comment les critères de filtrage peuvent masquer un produit cartésien

Les instructions SELECT qui contiennent une clause WHERE peuvent facilement cacher un produit cartésien car toutes les lignes n’apparaîtront pas en double. Voici une requête d’apparence assez innocente dans laquelle quelqu’un a oublié d’inclure une jointure de table:

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM shops, employeesWHERE shops.shop = 'Zurich';

Puisque la boutique n’apparaît pas dans l’ensemble de résultats, il serait facile d’accepter que la sortie soit exacte. Cependant, nous pouvons facilement vérifier que seuls les deux premiers employés de la liste travaillent à Zurich. Les trois autres semblent avoir échappé à nos critères de filtrage !

L’édition de toutes les colonnes montre plus clairement ce qui se passe. Le filtre n’a en fait renvoyé que le magasin de Zurich. Cependant, sans une jointure de table appropriée, la requête produit un enregistrement pour chaque employé, qu’il soit lié ou non à la boutique de Zurich. Cela est logique si l’on considère que les employés ne sont associés à aucun magasin sans jointure. Par conséquent, le champ shop_id de la table des employés n’a rien à voir avec celui de la table des magasins. Ce que la requête dit, c’est « Récupérez-moi toutes les lignes de la table des magasins dont le nom correspond à ‘Zurich’ et toutes les lignes de la table des employés » :

shop_id	shop	id	id	shop_id_1	gender	name	salaire
	Zurich			m	Jon Simpson
	Zurich			f	Barbara Breitenmoser	(NULL)
	Zurich			f	Kirsten Ruegg
	Zurich			m	Ralph Teller
	Zurich			m	Peter Jonson

De même, le fait de restreindre les résultats de la table des employés ne produit que les lignes de cette table qui correspondent aux critères et toutes les lignes de l’autre table. Voici une requête qui filtre les employés par salaire :

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Monthly Salary' FROM shops, employeesWHERE employees.salary > 5500;

Elle correspond à une ligne de la table des employés, qui est affichée une fois pour chaque ligne de la table des magasins :

name	gender	Salaire mensuel
Kirsten Ruegg	f	5,600.00
Kirsten Ruegg	f	5 600 dollars.00
Kirsten Ruegg	f	5 600 $.00

Encore une fois, l’inclusion de toutes les colonnes confirme cette hypothèse :

id	shop_id	genre	nom	salaire	shop_id_1	shop
		f	Kirsten Ruegg			Zurich
			Kirsten Ruegg			New York
		f	Kirsten Ruegg			Londres

Maintenant ces résultats ne semblent pas immédiatement suspects car, selon les critères et les colonnes affichées, les valeurs dupliquées ne sont pas une occurrence rare :

La requête affiche toujours un employé pour chaque ligne de la table des magasins, mais cette fois, elle affiche un employé pour chaque ligne de la table des magasins ainsi que trois lignes pour l’unique employé correspondant. En d’autres termes, les cinq employés sont affichés pour les magasins correspondants (celui de Zurich) et l’enregistrement de l’employé dont le revenu dépasse 5500 $ (Kristen Ruegg) est répété pour chaque ligne de la table des magasins.

Comme nous l’avons vu aujourd’hui, les produits cartésiens n’ont pas tendance à fournir des informations utiles. Par conséquent, la morale de l’histoire est la suivante : évitez à tout prix les jointures cartésiennes, à moins que vous n’ayez une raison limpide de le faire.

Avoir les bonnes données avec les jointures SQL

 » Voir tous les articles du chroniqueur Rob Gravelle