Discussion :

[fsmrel]

Bonjour,


Xo a évoqué il y a déjà un moment l’alternative : Faire les jointures dans la clause WHERE ou avec des JOIN ?

Je dirais pour ma part que la question est à formuler autrement, car on n’effectue pas de jointure dans une clause WHERE, mais seulement une restriction appliquée à un produit cartésien, lui même formulé dans la clause FROM.

Quelques remarques :

1. Il est parfaitement légitime de considérer la jointure comme une primitive, et de suivre la norme en utilisant JOIN. Comme à une clause SELECT ne peut être associée qu’une clause FROM unique, cette dernière peut comporter une liste de jointures (JOIN donc) de tous types (naturelle, thêta, externe, croisée, semi, etc.) : rien à redire. La clause WHERE sert alors seulement pour une salade composée de RESTRICT et de constructions telles que IN et EXISTS, relevant plus ou moins de l’algèbre relationnelle ou du calcul relationnel. JOIN n’entrant pas dans la composition de cette salade, il a le mérite de ne pas contribuer à la rendre indigeste.
   
   
2. Maintenant, si à l’instar de Ted Codd, l’inventeur du Modèle Relationnel de Données, vous considérez la jointure comme n’étant pas une opération primitive (Cf. "Relational completeness of data base sublanguages", March 6, 1972, page 9), mais plutôt comme un RESTRICT appliqué au résultat d’un PRODUIT cartésien, alors dans le contexte de SQL, il est logique de nommer les opérandes du PRODUIT dans la clause FROM et de fournir dans la clause WHERE le prédicat du RESTRICT associé. Les parents de SQL, à savoir Don Chamberlin et al., ont manifestement utilisé la définition de Codd. Je ne cherche pas à justifier leur choix, mais simplement à remettre en perspective les choses dans le contexte de l'époque (plus de trente ans déjà...)
   
   
3. Si IBM avait demandé à Codd de superviser le projet SQL (Codd faisait alors partie du centre de recherche d’IBM à San Jose), SQL ressemblerait aujourd’hui plus à Phoebus qu’à Quasimodo (dont je ne mets pas en cause le bon cœur), mais ceci est une autre histoire, on ne refait pas le passé...

Toujours à l’attention de Xo :

Vous écrivez "Les jointures faites dans la clause WHERE (ancienne syntaxe de 1986 !)..."

Vous pouvez remonter encore au moins de 10 ans dans le temps, pour trouver l’utilisation de WHERE en conjugaison avec la jointure, quand SQL s’appelait encore SEQUEL (nom qu’il a fallu changer pour des problèmes de copyright). L’opérateur JOIN a pour sa part été déjà décrit par Ted Codd en 1969, du temps où celui-ci était chercheur chez IBM ("Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks").


Étant un vieux de la vieille, j'utilise plutôt l'ancienne syntaxe, me contentant d'énumérer les tables à joindre, sagement rangées dans la clause FROM, accompagnées de leur noms de corrélation et je laisse le soin à l'optimiseur de s'accommoder des prédicats de jointure, très facilement repérables dans la clause WHERE, tous bien regroupés dans un coin du saladier. Je suis confus de ne pas être un très bon exemple, mais que voulez vous, après plus de 20 ans de SQL...

[Alain Defrance]

Très intéressant.
J'ai par ailleurs remarqué que ça pose pas mal de problème dans le travail collaboratif où chaqu'un fait à sa manière si le chef de projet n'impose rien.
Les jointures sont souvent sujettes à de longues discussions concernant le rôle d'optimisation et de pertinence de la sémantique des JOIN.

Etant jeune et possédant une expérience réduite dans le développement, et particulièrement dans le SQL, je me suis imposé de suivre les normes les plus récentes a savoir l'utilisation de JOIN. Mais je comprend qu'on puisse garder de mauvaise habitudes. Par ailleurs c'est ce qui est enseigné et les JOIN sont souvent mal connu et mal appliqué des professeurs.

[oadin]

le problème vient de l'optimisation de la requète par le sgbd.

Pour ma part je travaille en sql server, je ne m'avancerai pas sur un autre sgbd, mais la clause on d'un join est largement prioritaire dans l'ordre d'exécution d'une requète sql sur la clause where.

La clause on (du join donc) est numéro 2 alors que le where reste en 3ème position (4ème je sais plus de mémoire après les join).

Donc pour faire un peu de math, si A = 25 enregistrement, B = 10 enregistrement

select * from a join b on A = b(on va faire simple)
va traiter une table de 25 -> table de 10 -> résultat.

select * from a, b where a = 10 and a=b
va traiter table 25 -> table 250 -> table de 10 -> résultat
qqch du genre

[fsmrel]

Bonjour kazou,

je comprend qu'on puisse garder de mauvaise habitudes

Le qualificatif de "mauvaise" n’est pas approprié et la formulation peut être remplacée par celle-ci : " je comprends qu'on puisse garder ses habitudes". En effet, dans ce que j’ai écrit, il s’agit bien d’exprimer une jointure sous la forme d’une restriction appliquée à un produit cartésien, ce qui mathématiquement est tout à fait respectable. Les opérations de restriction et de produit sont ici considérées comme des primitives, tandis que l’on peut désormais préférer l'alternative moderne, consistant à voir la jointure comme devenant à son tour une opération primitive, à la place du produit (considéré alors comme le cas dégénéré de la jointure).

[fsmrel]

Bonjour oadin,

Pour ma part je travaille en sql server... Donc pour faire un peu de math, si A = 25 enregistrement, B = 10 enregistrement

select * from a join b on A = b(on va faire simple)
va traiter une table de 25 -> table de 10 -> résultat.

select * from a, b where a = 10 and a=b
va traiter table 25 -> table 250 -> table de 10 -> résultat

Conceptuellement, ce que vous dites est recevable. Au plan de la réalisation, les choses ne se passent pas ainsi et c’est heureux ! SQL Server a un optimiseur et il traite les deux requêtes très exactement de la même façon.

Puisque vous mentionnez SQL Server, considérons un cas concret. Si j’exécute une 1ere jointure de la façon suivante, entre la table des 37500 communes françaises (Commune) et celle des liens représentant les communes limitrophes (Commune_Commune, 215000 liens) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Select  Distinct a.CommuneNom
From    Commune a Join Commune_Commune b 
        ON   a.CommuneId = b.CommuneId ;

Le temps d’exécution est d’environ une seconde et le plan d’exécution est le suivant :




Maintenant, si j’effectue la jointure avec la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 Select  Distinct a.CommuneNom
From    Commune a   
      , Commune_Commune b
where   a.CommuneId = b.CommuneId ;

Alors le plan d’exécution est le même (ainsi donc que le temps d’exécution) :




Si jamais quelqu’un utilise un SGBDR qui favoriserait une des deux requêtes au détriment de l’autre, je l’engage vivement à se débarrasser de ce "SGBDR" à l'optimiseur pas très performant...

[oadin]

Conceptuellement, ce que vous dites est recevable. Au plan de la réalisation, les choses ne se passent pas ainsi et c’est heureux ! SQL Server a un optimiseur et il traite les deux requêtes très exactement de la même façon.

Sur une requète aussi simpliste heureusement oui sql server a le même plan de travail. Mais je ne suis pas totalement sur que ce soit le même cas lorsque nous avons des requètes qui se compliquent légèrement et qui finissent par dépasser les 150 lignes.

Avec des clauses select imbriquée de ce style-ci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from (select champs1 from (select ch1 from table1) as t) as ta

J'ai déjà vu au travail des requètes avec des select imbriqués dans les from que sql server 2000 ne savait pas traiter pour cause de trop d'imbrication (je n'ai plus le terme exact d'erreur qu'il renvoyait)

Dans des cas ou la requète va fournir des milliers de plan d'exécution, autant également aider l'optimiseur.
Même si l'optimiseur peut faire une partie du travail a notre place, il reste un cout en calcul et compilation de la requête.


Maintenant pour des requètes simples, effectivement c'est principalement un outil de lecture que d'utiliser les join plutot que de les inclure dans des clauses where

[GrandFather]

Bonjour,

Si jamais quelqu’un utilise un SGBDR qui favoriserait une des deux requêtes au détriment de l’autre, je l’engage vivement à se débarrasser de ce "SGBDR" à l'optimiseur pas très performant...

Argh... Me demander de me débarasser de PostgreSQL relève de la cruauté.

Voici l'extrait de la documentation de Postgres traitant de l'optimisation et des clauses JOIN explicites :

Il est possible de contrôler le planificateur de requêtes à un certain point en utilisant une syntaxe JOIN explicite. Pour voir en quoi ceci est important, nous avons besoin de quelques connaissances.
Dans une simple requête de jointure, telle que :
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
le planificateur est libre de joindre les tables données dans n'importe quel ordre. Par exemple, il pourrait générer un plan de requête qui joint A à B en utilisant la condition WHERE a.id = b.id, puis joint C à cette nouvelle table jointe en utilisant l'autre condition WHERE. Ou il pourrait joindre B à C, puis A au résultat de cette jointure précédente. Ou il pourrait joindre A à C puis les joindre avec B mais cela pourrait ne pas être efficace car le produit cartésien complet de A et C devra être formé alors qu'il n'y a pas de condition applicable dans la clause WHERE pour permettre une optimisation de la jointure (toutes les jointures dans l'exécuteur PostgreSQL™ arrivent entre deux tables en entrées donc il est nécessaire de construire le résultat de l'une ou de l'autre de ces façons). Le point important est que ces différentes possibilités de jointures donnent des résultats sémantiquement équivalents mais pourraient avoir des coûts d'exécution grandement différents. Du coup, le planificateur va toutes les explorer pour trouver le plan de requête le plus efficace.
Quand une requête implique seulement deux ou trois tables, il y a peu d'ordres de jointures à préparer. Mais le nombre d'ordres de jointures possibles grandit de façon exponentielle au fur et à mesure que le nombre de tables augmente. Au-delà de dix tables en entrée, il n'est plus possible de faire une recherche exhaustive de toutes les possibilités et même la planification de six ou sept tables pourrait prendre beaucoup de temps. Quand il y a trop de tables en entrée, le planificateur PostgreSQL™ basculera d'une recherche exhaustive à une recherche génétique probabiliste via un nombre limité de possibilités (la limite de bascule est initialisée par le paramètre en exécution geqo_threshold). La recherche génétique prend moins de temps mais elle ne trouvera pas nécessairement le meilleur plan possible.[...]

Pour des requêtes n'impliquant que peu de tables, comme dans votre exemple, le planificateur est à même de trouver l'ordre de jointure optimal ; pour des requêtes plus complexes, il sera nécessaire "d'aider" le planificateur en lui spécifiant l'ordre des jointures à l'aide de JOIN. La question que je me pose est : est-ce une "limitation" inhérente à Postgres, où retrouve-t-on le même comportement sous Oracle et SQL Server ?

[Alain Defrance]

Bonjour kazou,




Le qualificatif de "mauvaise" n’est pas approprié et la formulation peut être remplacée par celle-ci : " je comprends qu'on puisse garder ses habitudes". En effet, dans ce que j’ai écrit, il s’agit bien d’exprimer une jointure sous la forme d’une restriction appliquée à un produit cartésien, ce qui mathématiquement est tout à fait respectable. Les opérations de restriction et de produit sont ici considérées comme des primitives, tandis que l’on peut désormais préférer l'alternative moderne, consistant à voir la jointure comme devenant à son tour une opération primitive, à la place du produit (considéré alors comme le cas dégénéré de la jointure).

Autant pour moi, ne je voulais pas donner une qualification péjorative.
De plus je suis d'accord avec toi.

En revanche j'ai souvent entendu dire que les clauses JOIN permettent au SGBD de s'y retrouver, et de pratique de multiples optimisations lors de l'exécution de la requête. Qu'en est-il réellement ?
La réponse sera différente en fonction des SGBD, de plus certain ne supporte pas la clause JOIN (ce qui fait l'utilisation du FROM + WHERE plus sûr, mais pour les autres qu'en est-il ?

[fsmrel]

Bonsoir,

Pour moi, j'aurai dit que la clause JOIN, c'est la définition de la relation.
Alors que la clause WHERE est une définition de sous-ensemble.

Tout d’abord, pour Codd, une relation est un sous-ensemble (Cf. "A Relational Model of Data for Large Shared Data Banks”). Je traduis ce qu’il a écrit à ce sujet :

"Le terme de relation est utilisé ici dans son acception mathématique. Étant donnés les ensembles S1, S2, ..., Sn (non nécessairement distincts), R est une relation sur ces n ensembles si c’est un ensemble de n-uplets, le 1er élément de chacun d’eux tirant sa valeur de S1, le 2e de S2, et ainsi de suite (de manière plus concise, R est un sous-ensemble du produit cartésien S1 X S2 X ... X Sn). On fera référence à Sj comme étant le jième domaine de R... De manière plus concise, R est un sous-ensemble du produit cartésien S1 X S2 X ... X Sn".

Concernant la clause WHERE et pour changer un peu de référence, je cite Georges Gardarin ("Bases de données. Les systèmes et leurs langages". (Eyrolles, 1988)) :

La restriction de la relation R par une qualification Q est une relation R’ de même schéma, dont les tuples sont ceux de R satisfaisant la qualification Q.

Ainsi, quand vous dites que la clause WHERE est une définition de sous-ensemble, je suppose que cette clause représente pour vous la qualification Q au sens de Gardarin, c'est-à-dire une conjonction de qualifications (ou conditions) élémentaires : Q1 ET Q2 ET ... ET Qn.

Conceptuellement parlant, je pense ne pas trahir la pensée de Don Chamberlin (un des pères de SQL), en disant que le résultat d’un FROM est une valeur de table qui devient un opérande pour WHERE, qui pond à son tour une valeur de table qui servira pour la projection finale (SELECT col1, col2, ...). WHERE est en fait le pendant de l’opérateur RESTRICT (ou θ-RESTRICT, ou θ-SELECT, etc.) de la théorie relationnelle, laquelle adhère totalement au principe de fermeture, selon lequel le résultat d’une opération relationnelle est toujours une relation. Le résultat d’un FROM, d’un WHERE, d’un SELECT n’est pas forcément toujours une relation et c’est bien malheureux car alors la fermeture chancelle, mais ceci est une autre histoire...

[pacmann]

[*]Maintenant, si à l’instar de Ted Codd, l’inventeur du Modèle Relationnel de Données, vous considérez la jointure comme n’étant pas une opération primitive (Cf. "Relational completeness of data base sublanguages", March 6, 1972, page 9), mais plutôt comme un RESTRICT appliqué au résultat d’un PRODUIT cartésien, alors dans le contexte de SQL,...

Tiens, c'est amusant :
Pour moi, j'aurai dit que la clause JOIN, c'est la définition de la relation.
Alors que la clause WHERE est une définition de sous-ensemble

Soient A et B deux ensembles.
On définit R relation sur A, B.
Soient (x,y) € A x B
x R y <=> (Condition JOIN)

Et à partir de R (abus de langage entre la relation et le sous-ensemble du produit qu'elle définit), on définit
R2 cette fois comme sous ensemble (et non comme une relation) par la clause WHERE...
X € R2 <=> X € R et (Condition WHERE)

Bon, mais j'imagine que Codd connaissait mieux le sujet que moi