Discussion :

[rsc]

[Oracle 10.2]
Bonjour ! J'ai un pb de résultats sur une requête SQL :

J'ai une table ENTREPRISE et une table TRAVAUX, avec une intégrité référentielle sur ID_ENTREPRISE dans TRAVAUX.

Je cherche à déterminer quelles sont les entreprises qui n'ont jamais été "utilisées" dans TRAVAUX.

Code :

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

SELECT id_entreprise from entreprise where not exists (select 1 from travaux where id_entreprise = entreprise.id_entreprise);

me renvoie 14 lignes, ce qui est exact.

Code :

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

SELECT id_entreprise from entreprise where id_entreprise not in (select id_entreprise from travaux);

ne me renvoie aucune ligne.

Je ne comprends pas. Pour moi, les 2 requêtes sont sémantiquement identiques, même si elles génèrent des plans d'exécution différents.

Quelqu'un peut-il me dire où est mon erreur ? Merci d'avance.

[dariyoosh]

[Oracle 10.2]
... Pour moi, les 2 requêtes sont sémantiquement identiques ...

FAUX !!!

http://asktom.oracle.com/pls/asktom/...D:442029737684

NOT IN and NOT EXISTS ne sont pas toujours la même chose.

Est-ce que par hasard dans la requête

Code :

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

1
2
 
SELECT id_entreprise FROM travaux

Vous avez au moins une ligne qui renvoie NULL pour la colonne id_entreprise?

[dariyoosh]

Merci aussi de fournir les DDL et DML pour vos tables pour que l'on puisse mieux vous aider.

[rsc]

FAUX !!!

http://asktom.oracle.com/pls/asktom/...D:442029737684

NOT IN and NOT EXISTS ne sont pas toujours la même chose.

Est-ce que par hasard dans la requête

Code :

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

1
2
 
SELECT id_entreprise FROM travaux

Vous avez au moins une ligne qui renvoie NULL pour la colonne id_entreprise?

Effectivement.

[rsc]

Effectivement

Code :

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

SELECT id_entreprise from entreprise where id_entreprise not in (select id_entreprise from travaux where id_entreprise is not null);

marche.

Je conçois que ça améliore le plan d'exécution, mais j'avoue que je ne vois pas vraiment pourquoi c'est indispensable. Et la discussion sur asktom ne m'a pas vraiment éclairé.

[Waldar]

Tout simplement parce que NULL n'est pas une valeur mais plutôt un état, et le fait qu'il apparaisse dans une liste de valeur rend le prédicat faux, car NOT IN se traduit en une succession de <>.

Si on ne prend pas en considération la sous-requête, mais juste des listes :

Code :

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

WHERE colonne NOT IN (a,b)

est traduit en

Code :

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

WHERE colonne <> a AND colonne <> b

Si maintenant vous collez un NULL là-dedans :

Code :

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

WHERE colonne NOT IN (a, null)

est traduit en

Code :

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

WHERE colonne <> a AND colonne <> NULL

Et <> NULL est toujours faux, vous le savez certainement la syntaxe devrait être IS NOT NULL.

Notez qu'avec IN, qui est traduit en une succession de OR n'invalide pas le prédicat en entier.

[dariyoosh]

Effectivement

Code :

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

SELECT id_entreprise from entreprise where id_entreprise not in (select id_entreprise from travaux where id_entreprise is not null);

marche.

Je conçois que ça améliore le plan d'exécution, mais j'avoue que je ne vois pas vraiment pourquoi c'est indispensable. Et la discussion sur asktom ne m'a pas vraiment éclairé.

C'est une question d'Algèbre boolean.

http://www.oracle.com/technetwork/is...ql-097727.html

Nulls make themselves felt in particularly subtle ways in Boolean expressions, such as those you might write for the WHERE clause of a query. Boolean expressions normally result in TRUE or FALSE , but nulls introduce a third possible result of Boolean expressions: UNKNOWN . Note that NULL is not the same as UNKNOWN :

SAL + NULL results in NULL . (This is a scalar expression.)
SAL < NULL results in UNKNOWN . (This is a Boolean expression.)

la clause WHERE n'envoie rien pour la valeur boolean UNKNOWN.


Voici un exemple

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT 'Oui la valeur n''est pas dans la liste' AS result
FROM DUAL
WHERE 'toto' NOT IN ('A', 'B', 'C');
 
 
RESULT
-------------------------------
Oui la valeur n''est pas dans la liste

D'un point de vue relationnel, la clause WHERE ci-dessus fait :

Code :

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

1
2
 
(toto <> 'A') AND (toto <> 'B') AND (toto <> 'C') = TRUE AND TRUE AND TRUE = TRUE

Et donc vous avez le résultat, maintenant si j'ajoute dans la même clause WHERE indiquée ci-dessus la valeur NULL voici ce que j'obtient:

Code :

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

1
2
3
4
5
6
 
SELECT 'Oui la valeur n''est pas dans la liste' AS result
FROM DUAL
WHERE 'toto' NOT IN ('A', 'B', 'C', NULL);
 
no rows selected

Cette fois je n'obtiens rien car d'un point de vue relationnel voici ce qui se passe:

Code :

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

1
2
3
 
(toto <> 'A') AND (toto <> 'B') AND (toto <> 'C') AND (toto <> NULL) = 
TRUE AND TRUE AND TRUE AND UNKNOWN = TRUE AND UNKNOWN = UNKNOWN

Et une clause WHERE avec UNKOWN n'envoie rien et c'est pour cette raison, à chaque fois il y a une requête sous forme

Code :

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

1
2
3
4
 
SELECT ...
FROM ...
WHERE ... NOT IN (<subquery>)

Il faut d'abord être sûr que <subquery> ne contient jamais la valeur NULL.


Cordialement,
Dariyoosh

[dariyoosh]

Et voici la table des opérations Boolean définie par oracle qui résume tout:

http://www.oracle.com/ocom/groups/pu...set/116165.gif

[rsc]

Merci, Waldar, pour l'explication claire ! Moi, je voyais le résultat du SELECT comme un ensemble de données, pas comme une liste, d'où mon incompréhension.

Merci aussi à dariyoosh !

[pachot]

Bonjour,

Moi, je voyais le résultat du SELECT comme un ensemble de données, pas comme une liste, d'où mon incompréhension.

On peut le voir comme un ensemble, mais dans cette ensemble il y a un joker, le null, donc c'est comme si on avait dans l'ensemble toutes les valeurs possibles. Alors du coup il n'y a aucun résultat qui vérifie NOT IN (toutes les valeurs possibles)...
Cordialement,
Franck.

[rsc]

Bonjour,

On peut le voir comme un ensemble, mais dans cette ensemble il y a un joker, le null, donc c'est comme si on avait dans l'ensemble toutes les valeurs possibles. Alors du coup il n'y a aucun résultat qui vérifie NOT IN (toutes les valeurs possibles)...
Cordialement,
Franck.

Par ensemble, je voulais dire "dataset", une sorte de table temporaire avec laquelle aurait été faite une "jointure" de style except, du coup, le null ne gênait pas.