une requête SQL me pose problème : pourtant pas compliqué ?

[freelance28]

Bonjour

J'ai 3 tables :

- Client(id_client, client_nom, ...)

- Option(id_option, option_nom,...)

- Liaison (id_liaison, liaison_id_client, liaison_id_option)

La table Liaison lie donc les clients aux différentes options possibles.

Imaginons quelques enregistrements dans ma table Liaison:

- (1,1,1)

- (2,1,3)

- (3,2,1)

Le client 1 a donc les options 1 et 3 tandis que le client 2 à uniquement l'option 1

Je cherche à faire une seule requête qui me permettrait d'avoir tous les id_client ayant par exemple les options 1 ET 3.

Si je fais : SELECT id_client FROM Liaison WHERE liaison_id_option IN (1,3) : ça me retourne les clients ayant l'option 1 OU 3.

Si je fais : SELECT id_client FROM Liaison WHERE liaison_id_option ='1' AND liaison_id_option='3' : ça ne me retourne évidemment rien.

Il faut sans doute utiliser les jointures ? Vous avez une idée svp ?

Merci

Modifié 10 Février 2009 par freelance28

[jcaron]

SELECT l1.id_client FROM Liaison l1,Liaison l2 WHERE l1.liaison_id_option ='1' AND l2.liaison_id_option='3' AND l1.liaison_id_client=l2.liaison_id_client

Jacques.

[petit-ourson]

Et avec un inner join, c'est quand même plus beau :

SELECT l1.id_client FROM Liaison l1 INNER JOIN Liaison l2 ON l1.liaison_id_client=l2.liaison_id_client WHERE l1.liaison_id_option ='1' AND l2.liaison_id_option='3'

Par contre niveau performance, je ne connais pas la différence. A priori aucune (dans ce cas là).

[jcaron]

Vu que c'est exactement la même chose, aucune différence.

Jacques.

[petit-ourson]

J'ai entendu dire que certains SGBD faisaient des optimisation lors du JOIN. Intox ?

Je demanderai à mon DBA préféré, mais en ce moment il est au soleil sous les tropiques.

En ce qui concerne MySQL en tout cas, il est clair que cela change rien (bien que si on fait un second inner join à la suite, pas sur que l'on gagne pas en performance). J'essaierai d'y penser et de poser la question au mec de Sun-MySQL qui doit passer pour auditer ma base de données.

Dans tous les cas (enfin c'est mon avis), WHERE devrait uniquement servir à définir des critère de sélection alors que ON sert uniquement a définir des critères de jointure mais là ... je suis peut être un peu trop scolaire.

[Portekoi]

Vu que c'est exactement la même chose, aucune différence.

Jacques.

Euh tu peux faire Paris Marseille en bus (7-8H) et Paris Marseille en TGV(3H00), le résultat sera le même mais pas les conditions de voyage.

Dans la mesure du possible, utilisez toujours un opérateur de jointure normalisé Sql2 (mot clef JOIN).

En effet :

* Les jointures faites dans la clause WHERE (ancienne syntaxe de 1986 !) ne permettent pas de faire la distinction de prime abord entre ce qui relève du filtrage et ce qui relève de la jointure.

* Il est à priori absurde de vouloir filtrer dans le WHERE (ce qui restreint les données du résultat) et de vouloir "élargir" ce résultat par une jointure dans la même clause WHERE de filtrage.

* La lisibilité des requêtes est plus grande en utilisant la syntaxe à base de JOIN, en isolant ce qui est du filtrage et de la jointure, mais aussi en isolant avec clarté chaque condition de jointures entre chaque couples de table.

* L'optimisation d'exécution de la requête est souvent plus pointue du fait de l'utilisation du JOIN.

* Lorsque l'on utilise l'ancienne syntaxe et que l'on supprime la clause WHERE à des fins de tests, le moteur SQL réalise le produit cartésiens des tables ce qui revient la plupart du temps à mettre à genoux le serveur !

Source : http://sqlpro.developpez.com/cours/sqlaz/jointures/#LII-B

Donc non, cela ne revient pas du tout au même.

Faire une "jointure" dans la clause where signifie que tu ne te préoccupes aucunement de la jointure mais uniquement du filtrage pour ensuite le "re-filtrer".

Portekoi

[freelance28]

Merci beaucoup pour vos réponses.

Sachant que j'ai simplifié la requête qui ressemblerait plutot à tous les id_client qui ont les options 1,2,3, ... 20 (une vingtaine maxi), vous préconisez plutôt le JOIN ou le WHERE ?

Merci encore à tous !

[Portekoi]

Ne confonds pas Jointure et Filtrage

Pour relier 2 ou n tables, il faut utiliser l'expression "Join" : Inner join, Left join Rigth join etc.

Pour filtrer des résultats, il faut utiliser le Where donc la requête de petit-ourson.

[freelance28]

Merci pour vos explications, ça fonctionne bien.

Longue vie au Hub !

[petit-ourson]

Donc non, cela ne revient pas du tout au même.

Faire une "jointure" dans la clause where signifie que tu ne te préoccupes aucunement de la jointure mais uniquement du filtrage pour ensuite le "re-filtrer".

Le problème c'est que MySQL a optimisé les requêtes si on n'utilise pas de JOIN, donc maintenant les utilisateurs font n'importe comment...

[Portekoi]

Tu as une source car cela m'étonnerait...

De plus, dans le cas d'une égalité en terme de ressource entre la gestion des deux méthodes (ce qui me surprendrait), il faut utiliser les normes actuelles.

[jcaron]

Je pense qu'aucun SGBDR sérieux de nos jours ne fait la moindre différence (en termes d'optimisation) entre un JOIN explicite et une liste de tables avec un WHERE. Par exemple avec postgresql:

ad=# create table test1 (a integer,b integer);
CREATE TABLE
ad=# create table test2 (b integer, c integer);
CREATE TABLE
ad=# explain select * from test1,test2 where test1.b=test2.b;
							 QUERY PLAN							  
---------------------------------------------------------------------
 Merge Join  (cost=270.68..562.65 rows=18818 width=16)
   Merge Cond: (test1.b = test2.b)
   ->  Sort  (cost=135.34..140.19 rows=1940 width=8)
		 Sort Key: test1.b
		 ->  Seq Scan on test1  (cost=0.00..29.40 rows=1940 width=8)
   ->  Sort  (cost=135.34..140.19 rows=1940 width=8)
		 Sort Key: test2.b
		 ->  Seq Scan on test2  (cost=0.00..29.40 rows=1940 width=8)
(8 lignes)

ad=# explain select * from test1 join test2 on (test1.b=test2.b);
							 QUERY PLAN							  
---------------------------------------------------------------------
 Merge Join  (cost=270.68..562.65 rows=18818 width=16)
   Merge Cond: (test1.b = test2.b)
   ->  Sort  (cost=135.34..140.19 rows=1940 width=8)
		 Sort Key: test1.b
		 ->  Seq Scan on test1  (cost=0.00..29.40 rows=1940 width=8)
   ->  Sort  (cost=135.34..140.19 rows=1940 width=8)
		 Sort Key: test2.b
		 ->  Seq Scan on test2  (cost=0.00..29.40 rows=1940 width=8)
(8 lignes)

D'ailleurs la doc de postgresql nous dit:

CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same

result as you get from listing the two items at the top level of FROM,

but restricted by the join condition (if any). CROSS JOIN is equiva-

lent to INNER JOIN ON (TRUE), that is, no rows are removed by qualifi-

cation. These join types are just a notational convenience, since they

do nothing you couldn't do with plain FROM and WHERE.

FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2. It is also equivalent to FROM T1 INNER JOIN T2 ON TRUE (see below).

Dans certains cas limités, il est possible d'utiliser des JOINs explicites pour "forcer" l'ordre des jointures (voir http://www.postgresql.org/docs/8.2/interac...icit-joins.html et http://www.postgresql.org/docs/8.2/interac...-COLLAPSE-LIMIT - ce dernier nous dit d'ailleurs que par défaut, un join explicite sera bel et bien converti en une liste de stables...), mais ça reste du cas particulier.

Maintenant, le débat reste ouvert sur le fait que l'une des notations soit plus lisible que l'autre. Normalement la notation avec les JOINs explicites devrait l'être, mais dans la pratique, surtout si on a beaucoup de tables à joindre, c'est rarement vrai dans mon expérience.

Jacques.

[Portekoi]

Re,

Je ne connais pas ton passif et je n'ai pas à le connaitre.

Je n'ai travaillé que très temporairement sur du postgresql et je connais très mal ce SGBD.

Par contre, travaillant tous les jours sur DB2 et SQL Server, je peux affirmer que sur des millions d'enregistrements (transaction bancaire), faire une jointure dites "Externe" relève plus du kamikaze que tu programmeur.

Les normes sont là pour, non seulement aider le développeur mais aussi pour aplanir les syntaxes entre SGBD.

Que postgresql ne fasse pas la différence, je dirais que c'est vraiment dommage. Ce serait comme continuer de programmer en PHP en gardant la GD 1.0.

Les évolutions de normes ou de langage ne sont pas nos ennemies et, dans le cadre de la norme SQL2, nous facilite la vie.

Dire que d'utiliser le JOIN sur une requête utilisant un grand nombre de tables est "rarement" fait, cela relève un peu de la "fainéantise" au sens du programmeur qui ne veut pas trop se fouler quoi que pour moi, ta méthode est bien plus compliquée car avec un Join, tu tapes les jointures au fil de l'eau et non, d'abord les tables et ensuite les liaisons.

Chacun fait comme il l'entend naturellement et je n'ai de leçon à donner à personne.

Portekoi

[Portekoi]

J'ai trouvé ceci dans la doc mais pas sur que cela réponde à la thématique :

Explicit inner join syntax (INNER JOIN, CROSS JOIN, or unadorned JOIN) is semantically the same as listing the input relations in FROM, so it does not need to constrain the join order. But it is possible to instruct the PostgreSQL query planner to treat explicit inner JOINs as constraining the join order anyway. For example, these three queries are logically equivalent:

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;

SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

But if we tell the planner to honor the JOIN order, the second and third take less time to plan than the first.

http://www.postgresql.org/docs/8.1/interac...icit-joins.html

[petit-ourson]

De plus, dans le cas d'une égalité en terme de ressource entre la gestion des deux méthodes (ce qui me surprendrait), il faut utiliser les normes actuelles.

Je n'ai pas dit qu'il fallait le faire sans INNER JOIN et même sous MySQL ;o)

Non pas de source officielle juste des tests que j'ai pu faire sur ma base de données avec une table de ~5 574 003 lignes et de ~35 621 lignes d'un autre côté.

Et en regardant le fonctionnement avec la commande EXPLAIN sous MySQL, j'obtiens la même chose (donc comme sous postgresql). Reste à savoir si c'est la même chose avec 5 ou 6 jointures (ou plus) dans la même requête

Je pourrai facilement avoir l'info pour Oracle même si je sais d'avance que ça va me couter cher de poser une question sur une requête mal écrite lol

[Kioob]

Je suppose qu'en utilisant explicitement des "JOIN" le travail de l'optimiseur est simplifié, mais qu'il y a de grandes chances pour que le "query plan" soit identique.

D'un point de vue pratique je préconise l'utilisation du JOIN uniquement parce que j'ai remarqué que les développeurs oublient moins souvent les clauses de jointure avec cette syntaxe (oui j'en avais marre des produits cartésiens corrigés à coup de distinct...).

[jcaron]

Par contre, travaillant tous les jours sur DB2 et SQL Server, je peux affirmer que sur des millions d'enregistrements (transaction bancaire), faire une jointure dites "Externe" relève plus du kamikaze que tu programmeur.

Les tables sur lesquelles je travaille ont plus souvent de l'ordre du milliard d'enregistrements, et on effectue quelques millions d'opérations sur chacune par jour, avec de nombreux selects avec des joins implicites sur plusieurs tables. Et ça marche plutôt bien :-)

Ensuite, une jointure "externe" (un "OUTER JOIN") ça n'a rien à voir avec une jointure implicite, qui est forcément une jointure interne. Et si on sait ce qu'on fait, ça n'a rien de kamikaze (même si un FULL OUTER JOIN reste d'une utilité très limitée à mon avis, un LEFT/RIGHT JOIN qui est un type de OUTER JOIN est fréquemment utilisé, et c'est le genre de cas où la syntaxe explicite est effectivement obligatoire).

Les normes sont là pour, non seulement aider le développeur mais aussi pour aplanir les syntaxes entre SGBD.

A ma connaissance, la norme SQL n'interdit pas le "comma join", et je pense que tous les SGBD l'acceptent sans souci. Ceci dit je n'ai pas la norme SQL sous la main, mais la doc de SQL server nous dit:

Dans la norme ISO, les jointures internes peuvent être spécifiées au choix dans une clause FROM ou dans une clause WHERE. Il s'agit du seul type de jointure pris en charge par ISO dans la clause WHERE.

Que postgresql ne fasse pas la différence, je dirais que c'est vraiment dommage.

Vu que sémantiquement, dans la spec, c'est la même chose, je ne vois pas comment un SGBD pourrait faire une différence...

Dire que d'utiliser le JOIN sur une requête utilisant un grand nombre de tables est "rarement" fait(...)

Ce n'est pas ce que j'ai dit. J'ai dit que dans mon expérience, c'était rarement plus lisible. Mais c'est probablement juste une question d'habitude.

Tout ceci étant dit, je suis totalement pour les standards, mais à ma connaissance le standard ne dit pas "on DOIT faire des joins explicites", mais on PEUT (probablement on DEVRAIT, mais je n'ai pas le texte d'aucune des -nombreuses- versions du standard). Et pour en revenir au point de départ, même si pour des raisons de lisibilité on devrait utiliser des joins explicites, ce que je tiens surtout à démentir c'est que des joins explicites seraient mieux optimisés que des joins implicites, vu que ce n'est absolument pas le cas, puisque c'est la même chose.

Jacques.

[Kioob]

un LEFT/RIGHT JOIN qui est un type de OUTER JOIN est fréquemment utilisé, et c'est le genre de cas où la syntaxe explicite est effectivement obligatoire).

Histoire de chipoter : sous Oracle ça n'est pas obligatoire (cf les + au milieu du WHERE) ; la syntaxe n'a pas été reprise ailleurs ?

[Portekoi]

Encore une fois, non, les normes ne sont pas là que pour éclaicir le code mais bien aussi pour optimiser les ressources.

Si sur internet, on trouve des articles indiquant clairement qu'il est mieux d'utiliser les JOIN, peut être y a t il une raison... ?

Les tables sur lesquelles je travaille ont plus souvent de l'ordre du milliard d'enregistrements, et on effectue quelques millions d'opérations sur chacune par jour, avec de nombreux selects avec des joins implicites sur plusieurs tables. Et ça marche plutôt bien :-)

Ensuite, une jointure "externe" (un "OUTER JOIN") ça n'a rien à voir avec une jointure implicite, qui est forcément une jointure interne. Et si on sait ce qu'on fait, ça n'a rien de kamikaze (même si un FULL OUTER JOIN reste d'une utilité très limitée à mon avis, un LEFT/RIGHT JOIN qui est un type de OUTER JOIN est fréquemment utilisé, et c'est le genre de cas où la syntaxe explicite est effectivement obligatoire).

Je ne sais pas dans quoi vous travaillez mais même en ayant travaillé pour RVI, une banque et Ipsos, j'ai rarement croisé une table contenant un milliard d'enregistrements. Ca doit être impressionnant et raison de plus pour optimiser à fond les requêtes.

Si un site comme développez dit explicitement :

Dans la mesure du possible, utilisez toujours un opérateur de jointure normalisé Sql2 (mot clef JOIN).

En effet :

* Les jointures faites dans la clause WHERE (ancienne syntaxe de 1986 !) ne permettent pas de faire la distinction de prime abord entre ce qui relève du filtrage et ce qui relève de la jointure.

* Il est à priori absurde de vouloir filtrer dans le WHERE (ce qui restreint les données du résultat) et de vouloir "élargir" ce résultat par une jointure dans la même clause WHERE de filtrage.

* La lisibilité des requêtes est plus grande en utilisant la syntaxe à base de JOIN, en isolant ce qui est du filtrage et de la jointure, mais aussi en isolant avec clarté chaque condition de jointures entre chaque couples de table.

* L'optimisation d'exécution de la requête est souvent plus pointue du fait de l'utilisation du JOIN.

* Lorsque l'on utilise l'ancienne syntaxe et que l'on supprime la clause WHERE à des fins de tests, le moteur SQL réalise le produit cartésiens des tables ce qui revient la plupart du temps à mettre à genoux le serveur !

Il ne faut pas venir ajouter derrière :

Et pour en revenir au point de départ, même si pour des raisons de lisibilité on devrait utiliser des joins explicites, ce que je tiens surtout à démentir c'est que des joins explicites seraient mieux optimisés que des joins implicites, vu que ce n'est absolument pas le cas, puisque c'est la même chose.

Réduire les normes SQL à une manière de rendre lisible une requête, c'est un peu réducteur à mon gout et démontre, pour moi, que vous n'avez pas vraiment compris l'essentiel de ces standards.

Et je reprends mon exemple du Paris-Marseille. Vous pouvez y aller en Bus, en voiture ou en TGV, vous arriverez toujours à Marseille.

A ma connaissance, la norme SQL n'interdit pas le "comma join", et je pense que tous les SGBD l'acceptent sans souci.

Merci de ne pas déformer mes propos. Je n'ai jamais dit qu'il y aurait un problème de compatibilité. J'ai dit que les normes permettaient de passer d'un système à un autre sans trop être perdu. L'algorithme permet de passer d'un langage à un autre. Tout ce qui change, c'est la syntaxe mais la logique de programmation reste la même. Que cela soit en Transac, en Php, en Asp, en Dotnet et j'en passe, l'algorithme s'appliquera à tous ces langages.

Vu que sémantiquement, dans la spec, c'est la même chose, je ne vois pas comment un SGBD pourrait faire une différence...

Même remarque : Que postgre ne fasse pas la différence entre les différentes jointures, c'est bien dommage, dans la mesure ou cela ne force pas les développeurs à évoluer, ni le moteur à progresser en terme d'optimisation.

Tout ceci étant dit, je suis totalement pour les standards, mais à ma connaissance le standard ne dit pas "on DOIT faire des joins explicites", mais on PEUT (probablement on DEVRAIT, mais je n'ai pas le texte d'aucune des -nombreuses- versions du standard). Et pour en revenir au point de départ, même si pour des raisons de lisibilité on devrait utiliser des joins explicites, ce que je tiens surtout à démentir c'est que des joins explicites seraient mieux optimisés que des joins implicites, vu que ce n'est absolument pas le cas, puisque c'est la même chose.

Non, je ne suis pas d'accord. Voir mes arguments ci-dessus, cela ne sert à rien de répéter 10 fois la même chose.

Les normes sont là pour faire évoluer les hommes mais aussi les machines.

Portekoi

PS Je viens de voir que vous étiez chez Oxado. Je comprends mieux la table approchant le "milliard d'enreg" mais c'est pas une raison