Discussion :

[machghoul]

Bonjour
J’ai deux tables

Produit (id,nom_produit)
Achat(id,id_produit)

Quelle est la requête SQL qui détermine le produit non acheté ?

Exemple

Produit (id,nom_produit)

1-chocolat
2-banane
3-bonbon
4-pain

Achat (id, id_produit)

1-2
2-1
3-1
4-2
5-4
Résultat
Produit.id=3 (produit 3 non acheté)

Cordialement

[sevyc64]

Deux solutions, soit tu utilise une sous requete dans le filtre :

Code :

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

1
2
SELECT * FROM PRODUIT
WHERE ID NOT IN (SELECT DISTINCT ID_PRODUIT FROM ACHAT)

Soit tu utilise l'opérateur ensembliste EXCEPT si ton SGBd le supporte :

Code :

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

1
2
3
4
SELECT * FROM PRODUIT
EXCEPT
SELECT P.* FROM PRODUIT P
INNER JOIN ACHAT A ON P.ID=A.ID_PRODUIT

[machghoul]

bien marché merci sevyc64

[François DORIN]

Bonjour,

Il existe une troisième solution, à base de jointure gauche :

Code SQL :

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

1
2
3
4
 
SELECT P.* FROM PRODUIT AS P
LEFT JOIN ACHAT AS A ON A.produit_id = P.id
WHERE A.id IS NULL

[sevyc64]

Exact.

Reste à voir le plan d’exécution. Peut-être un peu moins performant, ou pas, je sais pas.

[escartefigue]

Il existe une autre solution, souvent la plus performante avec la solution jointure outer proposée par Dorinf :

Code :

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

1
2
3
4
5
6
SELECT *
FROM PRODUIT AS MAIN
WHERE NOT EXISTS
     (SELECT 1
      FROM ACHAT AS SUBQ
      WHERE SUBQ.ID_PRODUIT = MAIN.ID)

Un test d'existence ne devrait jamais utiliser WHERE (NOT) IN (subselect) car cette solution passe par la construction d'une table +/- volumineuse correspondant à la requête imbriquée alors qu'un test d'existence ne construit pas de table et ne transporte qu'un booléen

[sevyc64]

Certes (le NOT IN est très souvent mal aimé).

Je vais peut-être dire une bêtise, mais il me semble que dans le cas du NOT IN, la sous-requête sera exécutée une seule fois pour construire la liste, tandis que dans le cas du EXISTS, la sous-requête sera exécutée pour chacun des enregistrements de la requête principale, non ?

[escartefigue]

L'ensemble n'est construit qu'une fois s'il s'agit d'une liste fermée avec des valeurs en dur, pas dans le cas d'un subselect

[Waldar]

Pour la petite histoire, Oracle DB réécrit les trois requêtes NOT IN, NOT EXISTS et LEFT JOIN IS NULL à peu de chose près de la même façon, comme ça ce débat n'a plus lieu d'exister.

Une note toutefois avec NOT IN, la gestion des nulls.
Si on rajoute une ligne dans achat (6, null), la solution NOT IN ne fonctionne plus.

À noter également qu'écrire un DISTINCT dans une sous-requête IN / NOT IN est inutile.
Si le SGBD est bon il l'ignore, sinon il perd du temps inutilement.

[escartefigue]

Sans doute, mais rien n'indique qu'ici il s'agisse d'Oracle et il est préférable de choisir la solution la plus adéquate, certains SGBD ne disposant pas d'un optimiseur très performant, et ça rend la solution portable à moindre frais

[tatayo]

Bonjour,
Pour donner de l'eau au moulin, je viens de faire le test avec MaxDb avec les 3 versions de la requête, et j'ai 3 plans d'exécution différents:

Code sql :

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

1
2
3
 
select * from ent_cde_frn
where frn_c_code not in (select frn_c_code from fournisseur)

OWNER TABLENAME COLUMN_OR_INDEX STRATEGY
TORPEDO FOURNISSEUR TABLE SCAN
TORPEDO ENT_CDE_FRN TABLE SCAN
QUERYREWRITE - APPLIED RULES:
DistinctPullUp 2
DistinctForSubqueries 1
DistinctPushDownTo 1
DistinctPushDownFrom 1

Code sql :

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

1
2
3
4
5
6
 
explain
select ent_cde_frn.*
from ent_cde_frn
left outer join fournisseur on fournisseur.frn_c_code = ent_cde_frn.frn_c_code
where fournisseur.frn_c_code is null

OWNER TABLENAME COLUMN_OR_INDEX STRATEGY
TORPEDO ENT_CDE_FRN TABLE SCAN
TORPEDO FOURNISSEUR FRN_C_CODE JOIN VIA KEY COLUMN
TABLE HASHED
NO TEMPORARY RESULTS CREATED
RESULT IS COPIED
Et pour finir:

Code sql :

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

1
2
3
4
5
 
explain
select ent_cde_frn.*
from ent_cde_frn
where not exists(select 1 from fournisseur where fournisseur.frn_c_code = ent_cde_frn.frn_c_code)

OWNER TABLENAME COLUMN_OR_INDEX STRATEGY
TORPEDO ENT_CDE_FRN TABLE SCAN
INTERNAL TEMPORARY RESULT EQUAL CONDITION FOR KEY
SOU_C_CODE (USED KEY COLUMN)
TORPEDO FOURNISSEUR EQUAL CONDITION FOR KEY
FRN_C_CODE (USED KEY COLUMN)
RESULT IS COPIED
QUERYREWRITE - APPLIED RULES:
DistinctPullUp 2
DistinctForSubqueries 1
DistinctPushDownTo 1
DistinctPushDownFrom 1
PushDownProjection 1

On peut voir un TableScan de la table fournisseur dans le premier cas, alors que pour les deux autres une jointure est faite sur la colonne FRN_C_CODE (qui est la clé primaire de la table fournisseur).

Ce serait pas mal de faire le même test avec d'autres SGBDs, histoire de comparer leurs comportements.

Tatayo.

[SQLpro]

Pour info c'est un exercice que je donne en cours SQL avancé avec SQL Server chez Orsys (cours PAV). Pour info, les 3 requêtes EXCEPT, NOT IN et NOT EXISTS donnent le même plan construit à base de semi anti jointure gauche...
Mais récrite avec la semi anti jointure gauche cela donne un nouveau plan moins bon !!!

A +

[thierryinfo2012]

pour l'instruction not exists(select ******)

Est ce que c'est vrai que

Code :

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

where not exists(select 1  from  ****

est plus performant que

Code :

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

 where not exists(select *  from  ****

qu méme celle du

Code :

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

where not exists(select NULL  from  ****

[al1_24]

Les bons SGBD ne tiennent pas compte du contenu de la clause SELECT dans la sous-requête utilisée par l'opérateur EXISTS et s'arrêteront à la première ligne trouvée.
D'autres iront exécuter l'intégralité de la sous-requête

[aimer_Delphi]

Bonjour,

Il existe une troisième solution, à base de jointure gauche :

Code SQL :

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

1
2
3
4
 
SELECT P.* FROM PRODUIT AS P
LEFT JOIN ACHAT AS A ON A.produit_id = P.id
WHERE A.id IS NULL

Merci beaucoup.

[escartefigue]

Ce serait pas mal de faire le même test avec d'autres SGBDs, histoire de comparer leurs comportements

J'avais conservé cette judicieuse remarque sous le coude, en attendant d'avoir l'occasion de prototyper sur du DB2, et voici ce qu'il en est
contexte : TAB1 possède environ 30 000 lignes et TAB2 200 000, toutes deux ont un index primaire sur la colonne de jointure, les stats sont à jour

Les 4 requêtés comparées sont

1. NOT IN (subselect)
2. NOT IN (subselect avec distinct)
3. LEFT OUTER JOIN et test de nullité
4. NOT EXISTS (subselect)

Voici ce que donne l'explain :
Pièce jointe 225497
On constate que les 2 premières requêtes utilisent exactement le même plan et que, contrairement aux 2 autres elles requièrent un tri supplémentaire (method=3) sur la table la plus volumineuse
Les requêtes 3 et 4 utilisent la même stratégie, avec des couts estimés plus favorables pour la solution 3 c'est à dire la jointure outer (CPU_MS = millisecondes, CPU_SU=service units)

Cependant, un explain n'est pas une science exacte en particulier les couts estimés qui sont rarement fiables.
Voici donc ce que donne l'exécution des 4 requètes, en temps et service units réellement consommés et là il n'y a pas photo :
Pièce jointe 225498
L'écart MS est non mesurable compte tenu des volumes trop faibles, par contre l'écart service unit est très significatif : on passe de 48000 (exists) à 100 000 (outer) et 176 000 (not in) soit 4 fois plus pour la solution NOT IN !