Select NOT IN

Version imprimable

12/10/2008, 11h35
olibara

Select NOT IN
Bonjour

Je ne parviens pas a trouver une syntaxe fonctionelle pour un NOT IN

But :

je voudrais selectionner tous les éléments d'une tables dont l'ID ne se trouve pas dans une autre table

J'imaginais pouvoir faire
Code:

1 2 3 4 5 select distinct addrid from tours where (addrid NOT in (select addrid from ok))
Mais le resultat est toujours vide
Par contre si je fais
Code:

1 2 3 4 5 select distinct addrid from tours where (addrid IN (select addrid from ok))
Alors j'ai un resultat pertinent mais qui est l'opposé de ce que je voudrais ???
12/10/2008, 21h09
Cybher

salut,

normalement pas de problème avec ta requête.
Tu la passes directement sous MySQL ou à travers un autre langage?
12/10/2008, 22h18
olibara

Salut je l'execute en MySql via NaviCat

Mais ce n'est pas la premiere fois que je constate une anomalie avec NOT IN
Ici c'est flagrant !
12/10/2008, 23h12
Cybher

as tu essayé avec un LEFT JOIN?
http://mysql.developpez.com/faq/?pag..._sous_requetes
13/10/2008, 07h44
olibara

Salut

Oui left join marche bien !
Ce qui confirme que IN ou NOT IN sont tres dangereux a utiliser car ne donnant pas forcément les resultat attendu
13/10/2008, 09h59
Antoun

peux-tu nous dire où se situe l'écart entre NOT IN et LEFT JOIN ?
13/10/2008, 10h48
olibara

Salut

NOT IN me rends NULL
IN me rends les record inaattendus (en negatif)
Left Join me rends les records attendus avec NOT IN
13/10/2008, 11h34
Antoun

Donc, c'est logique ?

Avec NOT IN (NULL), ton résultat est forcément faux, donc tu exclus les NULL. Par contre, avec LEFT JOIN, tu poses au contraire une condition Machin IS NULL, donc les NULL sont inclus.

Salut

Mais non c'est PAS logique voici un décomposé

Code:

1
2
3
4
5
6
-- req1
select distinct 
tours.addrid
from tours
where
(tours.addrid  not  in (select addrid from ok))

Resultat : 0
Attendu : +/ 2256
Devrait etre equivallent a

Code:

1
2
3
4
5
6
-- req2
SELECT DISTINCT
tours.addrID
FROM
tours
Left Join ok ON tours.addrID = ok.Addrid

Resultat : 2256 OK

Code:

1
2
3
4
5
6
-- req3
select distinct 
tours.addrid
from tours
where
(tours.addrid  in (select addrid from ok))

Resultat : 117 OK

Code:

1
2
3
4
5
6
-- req4
SELECT DISTINCT
tours.addrID
FROM
tours
Right Join ok ON tours.addrID = ok.Addrid

Resultat : 118 OK

13/10/2008, 13h33
skuatamad
Salut,
Juste pour illustrer les propos d'Antoun, regarde le test ci-dessous (le with c'est pour générer un jeu de test avec oracle):
Code:

1 2 3 4 5 6 SQL> with t as (select 1 as c from dual union select 2 as c from dual union select 3 as c from dual) select * from t where c not in (2,NULL,3); no rows selected
Donc dans ton exemple il faut rajouter

Code:

WHERE addrid NOT IN (SELECT addrid FROM ok where addrid IS NOT NULL)
13/10/2008, 13h43
Antoun
Donc, tes requêtes 1 et 2 n'ont rien d'équivalent. La 1 se lit "les addrid qui sont dans tours et pas dans ok", tandis que ta requête 2 se lit "tous les addrid qui sont dans tours, qu'ils soient ou non dans OK".

Ta requête 1 est donc l'équivalent logique (aux NULLs près) de :
Code:

1 2 3 4 5 6 -- req2bis SELECT DISTINCT tours.addrID FROM tours LEFT JOIN ok ON tours.addrID = ok.Addrid WHERE ok.Addrid IS NULL
Ta requête 3 se lit "les addrid qui sont dans ok et dans tours".
Ta requête 4 se lit "les addrid qui sont dans ok, qu'ils soient ou non dans tours".
13/10/2008, 14h00
olibara

Salut

Pour la requete 2 tu a raison

Mais pour requete 1 ce n'est pas possible que j'ai un resultat zero records
puisque OK ne contient que 117 ID distinct et que tours en contient 2256 distinct !

Citation:

La 1 se lit "les addrid qui sont dans tours et pas dans ok",
13/10/2008, 14h09
Antoun

Citation:

Envoyé par olibara

Salut

Pour la requete 2 tu a raison

Mais pour requete 1 ce n'est pas possible que j'ai un resultat zero records
puisque OK ne contient que 117 ID distinct et que tours en contient 2256 distinct !

Je ne vois pas où est le problème. Dans tours il y en a 2256, et ils sont tous aussi dans ok.
13/10/2008, 14h29
olibara

Salut Antoun

Je suis peut etre fou :roll:

Mais explique moi comment les 2256 ID distinct de Tours peuvent TOUS se retrouver dans les 117 ID distinct de OK

La je crois que SQL depasse la logique que j'ai appris a l'ecole
13/10/2008, 14h37
skuatamad

Quel est le résultat de la requête req2bis d'Antoun ?
La colonne addrid de la table OK contient elle des NULL ?
13/10/2008, 14h47
olibara

2bis me rends 2139 record ce qui est vraissemblablement la réalité

2256 - 117 = 2139 !!

Non il n'y a pas de null dans OK
13/10/2008, 15h06
Antoun

Citation:

Envoyé par olibara

Salut Antoun

Je suis peut etre fou :roll:

Mais explique moi comment les 2256 ID distinct de Tours peuvent TOUS se retrouver dans les 117 ID distinct de OK

La je crois que SQL depasse la logique que j'ai appris a l'ecole

dit comme ça, évidemment :aie::aie::aie:
13/10/2008, 16h19
olibara

Et donc sur base de cette conclusion on peut se poser des questions sur la confiance a accorder a un select NOT IN sur le resultat d'un select
13/10/2008, 20h10
skuatamad

Citation:

Envoyé par olibara

Et donc sur base de cette conclusion on peut se poser des questions sur la confiance a accorder a un select NOT IN sur le resultat d'un select

Très étrange effectivement :koi:

Je remarque que le résultat de tes requêtes 3 et 4 ne varie que d'une ligne.
Donc soit l'addrid de la table OK en plus est un doublon, soit un NULL, soit un autre id non présent dans tours, ce qui paraît crédible, mais si tu peux apporter des précisions sur cette ligne ce serait sympa.

Je suis vraiment désolé de faire mon St thomas, mais le résultat de la requête correspond vraiment au comportement d'un NULL dans un NOT IN, donc pour l'instant je ne peux pas être d'accord avec ta conclusion.
13/10/2008, 20h46
olibara
Pour skuatamat
Code:

1 2 3 4 5 SELECT DISTINCT tours.addrID FROM tours RIGHT JOIN ok ON tours.addrID = ok.Addrid
Cette requete donne 118 records
Le IN donne 117

Le record en plus c'est évidemment tous les null résultat du Right join qui se sont fait accordéonner par le distinct !

St Thomas est il satisfait par cette explication 8O ;)
13/10/2008, 21h51
skuatamad

Bon ok j'avais zapé le DISTINCT :aie: donc on oublie l'histoire des doublons.
Sinon ça ne veut pas dire qu'il n' y a pas de NULL :)

Mais je vais arrêter d'être chiant :mouarf:, donc oriente toi vers des recherches de bug concernant ta version. En cherchant méga vite fait j'ai trouvé çà :
http://forums.mysql.com/read.php?121,143298,143298

STP tiens nous au courant, ça peut en intéresser plus d'un.
13/10/2008, 23h04
olibara

Salut

Bon pour te débarasser de la petite bete qui te trotte en tete je peux t'assurer qu'il n'y a pas de NULL car je ne permet pas de NULL sur AddrID dans ces tables !

Je viens d'upgrader en 5.1
Meme problème
Petite info je suis en MyIsam et pas InnoDB
13/10/2008, 23h20
olibara

Pour celui qui veut essayer, je fais deux petite tables, une avec 2256 records l'autre avec 117

Et chacun peut essayer dans son environnement !
On verra bien si c'est
- structurel
- ou un bug de version(s)
- ou lié a l'environnement !
13/10/2008, 23h44
Antoun

Citation:

Envoyé par olibara

Pour celui qui veut essayer, je fais deux petite tables, une avec 2256 records l'autre avec 117

je n'aurai pas le temps ce soir, mais je suis preneur !
14/10/2008, 00h20
skuatamad

Citation:

Envoyé par olibara

Bon pour te débarasser de la petite bete qui te trotte en tete je peux t'assurer qu'il n'y a pas de NULL car je ne permet pas de NULL sur AddrID dans ces tables !

Merci, St thomas est satisfait :)
Et +1 pour le jeu de test.
14/10/2008, 16h45
olibara
Ouf !

J'ai ramé une bonne partie de la journée a refaire fonctionner Navicat qui plantait apres l'upgrade MySql 5.1

Il m'aura couté cher ce Not In :aie::aie:

Voici donc deux tables permettant de facilement reproduire l'effet null du not in

Et le query qui va avec
Code:

1 2 3 4 5 select distinct adr.addrid from adr where (adr.addrid not in (select addrid from okx))
14/10/2008, 23h13
Antoun

Merci pour les tables de test !

Pour l'anecdote, il y a bien un NULL sur la première ligne de okx, où addrid n'est pas en NOT NULL.

Sur le fond, il semble bien que tu mettes en évidence un bug du NOT IN, qui persiste encore dans la version 5.1.19.
14/10/2008, 23h26
olibara

Caramba ! :oops:

Tu a raison j'ai laissé les ALLOW NULL dans la table OK, et je n'avais pas vu ce NULL

Sans lui ca marche bien
Donc le bug vient bien de ce null

Citation:

Sur le fond, il semble bien que tu mettes en évidence un bug du NOT IN, qui persiste encore dans la version 5.1.19

Je dirais meme 5.1.28 !!
14/10/2008, 23h32
Antoun

Citation:

Envoyé par olibara

Sans lui ca marche bien
Donc le bug vient bien de ce null

Effectivement. Bravo pour avoir trouvé le contournement, je n'avais même pas pensé à tester sans le NULL :aie:

Tu es mûr pour devenir crashtesteur chez MySQL AB :king:

c'est :resolu: pour toi ?
14/10/2008, 23h52
olibara

Mmmouais !

Résolu en esperant que les Crashmakers de Mysql AB liront un jour ce post :mrgreen:
15/10/2008, 00h00
olibara

En fait dans le cas présent le NULL agit comme un Joker !

C'est la valeur magique qui represente TOUT
J'en connais qui parviendront a debattre une logique sur le sujet :mrgreen::mouarf::bug:
15/10/2008, 01h12
Antoun

Citation:

Envoyé par olibara

Résolu en esperant que les Crashmakers de Mysql AB liront un jour ce post :mrgreen:

Il ne tient qu'à toi d'aller poster le truc sur bugs.mysql.com.

Citation:

Envoyé par olibara

En fait dans le cas présent le NULL agit comme un Joker !

C'est la valeur magique qui represente TOUT
J'en connais qui parviendront a debattre une logique sur le sujet :mrgreen::mouarf::bug:

'farpaitement ! j'étais justement en train de vérifier les tables de vérité dans mon Celko (Data and database concepts in practice, que je recommande à tous) :chin:

Donc, en termes de logique tri-valuée (TRUE / FALSE / UNKNOWN), Valeur IN (NULL) est UNKNOWN, et donc Valeur NOT IN (NULL) devrait être UNKNOWN aussi. Le bug semble indiquer que, dans ce cas, MySQL interprète (à tort) le Valeur NOT IN (NULL) comme TRUE et non UNKNOWN.
15/10/2008, 08h30
olibara

Je t'avais dis que certains parviendront a prétendre que c'est logique !! :(

http://bugs.mysql.com/bug.php?id=40035

http://bugs.mysql.com/bug.php?id=40035
15/10/2008, 08h40
Antoun

Citation:

Envoyé par olibara

Je t'avais dis que certains parviendront a prétendre que c'est logique !! :(

http://bugs.mysql.com/bug.php?id=40035

http://bugs.mysql.com/bug.php?id=40035

Non, le gars se plaint que le résultat est NULL (donc UNKNOWN), alors que c'est logique que ce soit NULL. Ton bug est que ce n'est pas NULL mais TRUE.
15/10/2008, 08h48
olibara
Salut

Bon le gars c'est moi !

J'ai probablement mal exprimé la chose

TableA contient 2000 ID
TableB contient 100 ID

Quand je fais
Code:

1 2 3 4 5 select ID From tableA where ID NOT IN (Select ID from TableB)
Je recoit 1900 ID : normal

Si j'ajoute un ID NULL dans table B et que je fais le meme select, le résultat et NULL : pas de row : NADA : 0 records : rien du tout

Je sais pas comment le dire pour qu'on me comprenne
15/10/2008, 09h17
Antoun

Citation:

Envoyé par olibara

Salut

Bon le gars c'est moi !

:rire:

Enchanté !

Citation:

Envoyé par olibara

J'ai probablement mal exprimé la chose

Non, en fait c'est lui qui a raison et moi qui déconne :aie: :aie: :aie:

Citation:

Envoyé par Antoun

Donc, en termes de logique tri-valuée (TRUE / FALSE / UNKNOWN), Valeur IN (NULL) est UNKNOWN, et donc Valeur NOT IN (NULL) devrait être UNKNOWN aussi.

J'ai bon jusque-là...

Citation:

Envoyé par Antoun

Le bug semble indiquer que, dans ce cas, MySQL interprète (à tort) le Valeur NOT IN (NULL) comme TRUE et non UNKNOWN.

Là par contre, je m'emmêle les pinceaux. Que tu obtiennes un résultat vide indique que le NOT IN n'est pas TRUE (en l'occurrence, qu'il est UNKNOWN).

Dit plus simplement :

Citation:

Envoyé par olibara

En fait dans le cas présent le NULL agit comme un Joker !

C'est la valeur magique qui represente TOUT

Je peux aussi te le dire comme ça :

Le NULL représente l'inconnu. Quand tu demandes "sors-moi les ID qui sont dans NULL", un SGBD te réponds "je n'en sais rien". Quand tu demandes "sors-moi les ID qui ne sont pas dans NULL", il te répond aussi "je n'en sais rien".

Pour plus de détails sur les points vicieux du NULL, cf http://sqlpro.developpez.com/cours/null/. Je vais lui suggérer d'ajouter un paragraphe "NULL et IN" !
15/10/2008, 11h36
skuatamad

Bon j'arrive un peu tard mais j'ai pas trop su exploiter immédiatement les fichiers :aie:.

Juste pour compléter un peu, le comportement de IN et NOT IN n'est pas tout à fait identique fasse au NULL.

IN(NULL) et NOT IN(NULL) ne renvoient rien et c'est normal.
IN(1,NULL,2) marche bien, si ce n'est que les données NULL ne sont pas renvoyées, mais c'est normal.
Par contre NOT IN(1,NULL,2) ne renvoie toujours rien, ce qui peut surprendre mais comme les SGBDs se comportent tous de la même manière ça ne peut donc pas être considéré comme un bug.

Ma question serait plutôt pourquoi IN(1,NULL,2) renvoie 1 et 2, le SGBD pourrait considérer que la présence d'inconnu dans la clause l'empêche de résoudre la requête.
Mais mes connaissances en logique sont plus que limitées... le Celko tu dis, je pense que ça va être mon prochain achat :)
15/10/2008, 11h43
Antoun

Citation:

Envoyé par skuatamad

Ma question serait plutôt pourquoi IN(1,NULL,2) renvoie 1 et 2, le SGBD pourrait considérer que la présence d'inconnu dans la clause l'empêche de résoudre la requête.

Non, il sait qu'il y a 1 et 2, donc le résultat est TRUE quel que soit le contenu du NULL.

Dans le même ordre d'idées, les tables de vérité de la logique tri-valuée (établies par Łukasiewicz) disent que TRUE OR UNKNOW est TRUE.
15/10/2008, 11h52
Antoun

Citation:

Envoyé par skuatamad

Mais mes connaissances en logique sont plus que limitées... le Celko tu dis, je pense que ça va être mon prochain achat :)

Ce bouquin ne contient pas de grande révélation foudroyante, mais il est très bien pour faire le point sur plein de petits détails de ce genre.
15/10/2008, 13h53
skuatamad

Effectivement, j'avais oublié la correspondance en expression logique des clause IN et NOT IN :
x IN(1,NULL,2)<=>x=1 OR x=NULL OR x=2
x NOT IN(1,NULL,2)<=>x!=1 AND x!=NULL AND x!=2
Et forcément avec des AND le résultat est UNKNOWN.

Fuseau horaire GMT +2. Il est actuellement 05h47.