NOT IN et NOT EXISTS

[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:

---

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:

---

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.

Citation:

---

Envoyé par rsc

[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:

---

1 2	SELECT id_entreprise FROM travaux

---

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

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

Citation:

---

Envoyé par dariyoosh

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:

---

1 2	SELECT id_entreprise FROM travaux

---

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

---

Effectivement.

Effectivement

Code:

---

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é. 8O

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:

---

WHERE colonne NOT IN (a,b)

---

est traduit en

Code:

---

WHERE colonne <> a AND colonne <> b

---

Si maintenant vous collez un NULL là-dedans :

Code:

---

WHERE colonne NOT IN (a, null)

---

est traduit en

Code:

---

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.

Citation:

---

Envoyé par rsc

Effectivement

Code:

---

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é. 8O

---

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

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

Citation:

---

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:

---

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:

---

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:

---

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:

---

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:

---

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

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

:ccool: 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 !

Bonjour,

Citation:

---

Envoyé par rsc

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.

Citation:

---

Envoyé par pachot

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. ;)