Discussion :

[olibara]

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 :

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

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 :

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

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 ???

[Cybher]

salut,

normalement pas de problème avec ta requête.
Tu la passes directement sous MySQL ou à travers un autre langage?

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

[Cybher]

as tu essayé avec un LEFT JOIN?
http://mysql.developpez.com/faq/?pag..._sous_requetes

[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

[Antoun]

peux-tu nous dire où se situe l'écart entre NOT IN et LEFT JOIN ?

[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

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

[olibara]

Salut

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

Code :

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

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 :

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

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 :

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

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 :

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

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

Resultat : 118 OK

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

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

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 :

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

WHERE addrid NOT IN (SELECT addrid FROM ok where addrid IS NOT NULL)

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

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

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".

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

La 1 se lit "les addrid qui sont dans tours et pas dans ok",

[Antoun]

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.

[olibara]

Salut Antoun

Je suis peut etre fou

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

[skuatamad]

Quel est le résultat de la requête req2bis d'Antoun ?
La colonne addrid de la table OK contient elle des NULL ?

[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

[Antoun]

Salut Antoun

Je suis peut etre fou

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

[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

[skuatamad]

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

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.

[olibara]

Pour skuatamat

Code :

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

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