Discussion :

[Pascal Lob]

Bonjour,

Pour faire simple j'ai quatre tables dans une base MySql

utilisateur(IdUtilis, NomUtilis, ...)
compte(IdCompte, IdUtilis, ....)
achats(IdAchat, IdCompte, IdProduit, ....)
produit(IdProduit, NomProduit, ....)

Je cherche a retrouver qui n'a pas acheté un produit précis et je n'y arrive pas.

J'arrive bien à ontenir ma liste des utilisateur qui ont acheté

Code :

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

1
2
3
4
SELECT NomUtilis, IdProduit  
FROM utilisateur INNER JOIN compte ON utilisateur.IdUtilis = compte.IdUtilis
INNER JOIN achats ON compte.IdCompte = achats.IdCompte 
WHERE IdProduit = 1

J'ai fait des essais avec des Left Join et des ISNULL(IdProduit) mais ça me donne la liste de ceux qui n'ont rien acheté, je voudrais aussi ceux qui ont acheté des choses mais pas le produit 1

[CinePhil]

Il faut utiliser NOT EXISTS.

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT u.NomUtilis
FROM utilisateur u
INNER JOIN compte c ON u.IdUtilis = c.IdUtilis
WHERE NOT EXISTS
(
	SELECT 1
	FROM achats a
	WHERE a.IdCompte = c.IdCompte
		AND IdProduit = 1
)

Traduction de la requête :
Sélection les utilisateurs pour lesquels il n'existe pas d'achat de leur compte pour le produit 1.

[Pascal Lob]

Merci CinePhil, ça marche nikel

[amir.]

Merci CinePhil, ça marche nikel

Juste pour préciser que ta requête peut également s'écrire sous cette forme (j'avoue, j'ai pas testé).

Code :

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

1
2
3
4
5
6
 
SELECT u.NomUtilis
  FROM utilisateur u
 INNER JOIN compte c ON u.IdUtilis = c.IdUtilis
  LEFT JOIN achats a ON a.idcompte = c.idcompte AND a.idproduit = 1
 WHERE a.idAchat IS NULL;

C'est ce que qu'on appelle parfois une "anti-jointure"?

Aussi, il faut savoir que cette requête ne retournera pas le nom d'un utilisateur si celui-ci n'a pas de compte. Je ne sais pas si c'est possible

Bonne fin de soirée...

[StringBuilder]

La requête peut s'écrire de 25 façon effectivement.

J'ai un faible pour la version LEFT OUTER JOIN, mais la version avec un COUNT(*) et un HAVING est pas mal non plus, car a plus de sens.

Code sql :

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

1
2
3
4
5
6
7
 
SELECT u.NomUtilis
FROM utilisateur u
INNER JOIN compte c ON u.IdUtilis = c.IdUtilis
LEFT OUTER JOIN achats a ON a.idcompte = c.idcompte AND a.idproduit = 1
GROUP BY u.nomutilis
having count(a.id) = 0;

[SQLpro]

EXISTS / NOT EXISTS ira souvent beaucoup plu vite :
1) il n'a pas besoin de "lire" les données, mais de savoir si une ligne correspond
2) dès qu'une ligne est retrouvée le processus s'arrête.

Avec COUNT il faut lire toutes les lignes, pire en jointure externe !

A +

[Pascal Lob]

La requête peut s'écrire de 25 façon effectivement.

J'ai un faible pour la version LEFT OUTER JOIN, mais la version avec un COUNT(*) et un HAVING est pas mal non plus, car a plus de sens.

Exact StringBuilder ça marche tout aussi bien c'est même plus clair à mon esprit.

Juste pour préciser que ta requête peut également s'écrire sous cette forme (j'avoue, j'ai pas testé).

Code :Sélectionner tout - Visualiser dans une fenêtre à part123456
SELECT u.NomUtilis
FROM utilisateur u
INNER JOIN compte c ON u.IdUtilis = c.IdUtilis
LEFT JOIN achats a ON a.idcompte = c.idcompte AND a.idproduit = 1
WHERE a.idAchat IS NULL; C'est ce que qu'on appelle parfois une "anti-jointure"?

Bonne fin de soirée...

Salut Amir.
C'est exactement ce que j'avais fait au départ mais je constate en te lisant que je n'avais pas mis le a.idproduit = 1 dans le ON du left join mais dans le where.
Comme il n'y a pas dans mon cas de différence notable en temps de traitement entre les différentes solutions, je vais donc garder cette solution là finalement qui correspond mieux à ma logique.

Merci à tous

[CinePhil]

Pourtant, SQLPro t'a expliqué que le NOT EXISTS que j'ai proposé est meilleur !

[amir.]

Pourtant, SQLPro t'a expliqué que le NOT EXISTS que j'ai proposé est meilleur !

Je suis pas tout à fait d'accord avec cette affirmation. Cela dépend de plusieurs paramètres (index présent ou non, densité des valeurs, NULL autorisé ou non...) et le meilleur moyen de savoir est de tester. Bien qu'il y a de fortes chances que le NOT EXISTS soit plus performant.

Ceci dit, certains SGBDs (et là je pense à Oracle) sont capable de ré-écrire ta requête d'une manière ou d'une autre s'ils estiment que l'une ou l'autre manière sera plus performante.

Et pour Pascal Lob, si c'est une jointure interne (INNER JOIN), tu peux mettre tes conditions indifféremment dans le WHERE ou dans la clause ON de ta jointure. Le résultat sera identique. Par contre, pour les OUTER JOIN, il faut faire attention. Le résultat ne sera pas identique si tu mets ta condition dans le WHERE ou dans le ON du JOIN.

[CinePhil]

Ceci dit, certains SGBDs (et là je pense à Oracle) sont capable de ré-écrire ta requête d'une manière ou d'une autre s'ils estiment que l'une ou l'autre manière sera plus performante.

Autant lui fournir directement la meilleur requête, tant qu'à faire !

Par contre, pour les OUTER JOIN, il faut faire attention. Le résultat ne sera pas identique si tu mets ta condition dans le WHERE ou dans le ON du JOIN.

Pour plus d'explications sur ce point, voir mon blog.

[amir.]

Autant lui fournir directement la meilleur requête, tant qu'à faire !

Tant qu'à faire )

[Pascal Lob]

Les requêtes imbriquées me semblent moins claire et j'ai peur d'avoir du mal si je dois remettre les doigts dedans dans 6 mois.
Mais tu as raison, autant mettre la plus performante...

[SQLpro]

Ceci dit, certains SGBDs (et là je pense à Oracle) sont capable de ré-écrire ta requête d'une manière ou d'une autre s'ils estiment que l'une ou l'autre manière sera plus performante.

Ceci a des limites. En effet un NOT IN, un NOT EXISTS et une non jointure n'expriment pas du tout la même chose sur le plan de l'algèbre relationnelle du fait de la présence des NULL. En effet dans le NOT IN la présence du NULL rend le prédicat systématiquement faux alors qu'avec NOT EXISTS on teste des lignes...
La réécriture n'est dons pas possible à ce niveau et les récritures possible par équivalence mathématiques sont effectivement assez rare...

A +

[StringBuilder]

EXISTS / NOT EXISTS ira souvent beaucoup plu vite :
1) il n'a pas besoin de "lire" les données, mais de savoir si une ligne correspond
2) dès qu'une ligne est retrouvée le processus s'arrête.

Avec COUNT il faut lire toutes les lignes, pire en jointure externe !

A +

Je dois être débile, à moins que ce ne soit l'optimiseur car si :
- aucune colonne de la table jointe n'est retournée par le select
- que le critère de jointure porte sur un index
- que le count() porte sur le même index

Pourquoi la jointure irait lire les lignes alors qu'elle a 100% des informations nécessaires dans l'index ?

Et à partir du moment où le filtre ne pointera plus uniquement sur l'index, le NOT EXISTS sera lui aussi obligé de lire les lignes pour les filtrer.

Je ne vois donc absolument pas pourquoi la jointure ouverte avec count et having serait plus lente que le not exists.

[punkoff]

Dans le cas d'un exists / not exists l'optimiseur va normallement effectuer un semi-join ou semi-anti-join.


ceci permet quand on a des relations tab1-0,n-----Asso----0,1-tab2 de sortir de la condition assez rapidement.


Avec count(*) + condition dessus on est obliger de faire entièrement la jointure puis de vérifier les conditions associées (typiquement > 0 ou = 0).

[SQLpro]

Je dois être débile, à moins que ce ne soit l'optimiseur car si :
- aucune colonne de la table jointe n'est retournée par le select
- que le critère de jointure porte sur un index
- que le count() porte sur le même index

Pourquoi la jointure irait lire les lignes alors qu'elle a 100% des informations nécessaires dans l'index ?

Et à partir du moment où le filtre ne pointera plus uniquement sur l'index, le NOT EXISTS sera lui aussi obligé de lire les lignes pour les filtrer.

Je ne vois donc absolument pas pourquoi la jointure ouverte avec count et having serait plus lente que le not exists.

Je comprend mieux maintenant les critiques faites sur GENERIX quand on voit votre incompréhension du fonctionnement des SGBD relationnels ! Ca fait peur de savoir que vous en être le chef de projet et c'est pas un bonne publicité !

A lire : http://www.developpez.net/forums/d62...e/#post6953085

A +