Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
1610 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Quelle requete pour une clef absente ?

 



 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

Quelle requete pour une clef absente ?

n°1495534
carabin
Posté le 22-12-2006 à 23:21:17  profilanswer
 

Bonsoir,  :hello:  
 
Désolé si le titre n'est pas clair, mais j'ai pas trouvé mieux  :heink:  
 
J'ai une requète a faire sous mysql (en PHP, mais mo problème n'est pas du coté de PHP).
En fait, je n'arrive pas a trouver comment je devrais la forumuler. J'explique :
 
J'ai une table reg_users qui avec des champs username, email, password, date, etc ... plus ub champs user_id.
 
J'ai une deuxième table adv_auth, lui aussi avec un champ user_id. Dans cette seconde table ne sont referencés qu'une partie des membres de la première table.
 
Pour trouver les membres la table 1 qui sont references dans la table 2, pas de problème, je sais faire :  
 
SELECT * FROM reg_users ru, adv_auth aa WHERE ru.user_id = aa.user_id;
 
Par contre, pour faire l'inverse, selectionner les membres de la tables 1 qui ne sont PAS referencés dans la table 2 je sais pas comment m'y prendre.
 
En gros ca signifierais ceci :
 
SELECT * FROM reg_users ru, adv_auth aa WHERE (ru.user_id est absent de adv_auth)
 
 
Bon j'ai déja essayé avec ru.user <> aa.user , mais (evidemment)   :sweat:  ca ne donne pas le résultat escompté !!
 
Si quelqu'un as une solution, merci d'avance   :jap:

mood
Publicité
Posté le 22-12-2006 à 23:21:17  profilanswer
 

n°1495544
MagicBuzz
Posté le 22-12-2006 à 23:45:54  profilanswer
 

2 solutions :
 
la "old shool" :


select *
from reg_users left outer join adv_auth on adv_auth.user_id = reg_users.user_id
where adv_auth.user_id is null


 
la solution que je préfère (car plus lisible), mais qui nécessite mysql >= 4.1
 


select *
from reg_users
where not exists (select null from adv_auth where adv_auth.user_id = reg_users.userd_id)


 
niveau perfs, c'est kif kif


Message édité par MagicBuzz le 22-12-2006 à 23:46:53
n°1495546
MagicBuzz
Posté le 22-12-2006 à 23:49:16  profilanswer
 

il existe aussi une 3° solution bien gore à souhait, et qui ne doit pas être supportée par mysql de toute façon ;)
 


select *
from reg_users
difference
select reg_users.*
from reg_users inner join adv_auth on adv_auth.user_id = reg_users.user_id

n°1495547
MagicBuzz
Posté le 22-12-2006 à 23:50:52  profilanswer
 

j'oubliais la 4° solution :
 


select *
from reg_users
where user_id not in (select user_id from adv_auth)


 
sauf que là, je te bute si tu l'utilises

Message cité 2 fois
Message édité par MagicBuzz le 22-12-2006 à 23:51:16
n°1495663
carabin
Posté le 23-12-2006 à 14:49:39  profilanswer
 

Merci MagigBuzz,
 
Comme je suis pas amateur de gore, et que je tiens a ma petite vie (après tout j'en ai qu'une seule), je vais donc prendre l'une des deux premières.
 
Mais comme je sais pas encore sur quel version de mysql ca tourneras, je vais prendre la "old school" (je pourrais toujours changer si la version le permets)
 
Par contre, l'inconvénient, comme j'aime bien savoir ce que je fais, c'est que je vais devoir me plonger dans ces histoire de outer join (et les inner join aussi, tant qu'a faire,  même si je l'utilise pas)
 
En tout cas, encore une fois merci, c'est super sympa (et super rapide). J'essyae ca et je vous tient au courant !

n°1495678
MagicBuzz
Posté le 23-12-2006 à 17:36:20  profilanswer
 

http://forum.hardware.fr/hfr/Progr [...] 6416_1.htm
 
De mémoire j'ai expliqué dans ce topic les différents types de jointure et leur utilité.
Cela ne te dispense évidement pas de prendre un bouquin pour approfondir, mais disons que ça va débroussailler le terrain ;)
 
Genre, tu utilises déjà les INNER JOIN, c'est juste que tu ne les écrit pas explicitement ;)

n°1495685
Taz
bisounours-codeur
Posté le 23-12-2006 à 18:34:48  profilanswer
 

MagicBuzz a écrit :

j'oubliais la 4° solution :
 


select *
from reg_users
where user_id not in (select user_id from adv_auth)


 
sauf que là, je te bute si tu l'utilises


t'as qu'a changer de SGBD plutôt    [:spamafote]

n°1495686
Taz
bisounours-codeur
Posté le 23-12-2006 à 18:37:50  profilanswer
 

Il n'y a même pas photo. Avec mon cher postgres, et suite à un bugreport que j'ai fait (et je suis sans doute pas le seul), les clauses IN sont graves optimisées.

 
Code :
  1. benoit=> explain analyse select * from reg_users u where not exists (select * from adv_auth a where a.uid = u.uid);
  2.                                                   QUERY PLAN
  3. --------------------------------------------------------------------------------------------------------------
  4. Seq Scan on reg_users u  (cost=0.00..3994.60 rows=580 width=36) (actual time=0.076..0.116 rows=2 loops=1)
  5.    Filter: (NOT (subplan))
  6.    SubPlan
  7.      ->  Seq Scan on adv_auth a  (cost=0.00..34.25 rows=10 width=8) (actual time=0.013..0.013 rows=0 loops=4)
  8.            Filter: (uid = $0)
  9. Total runtime: 0.257 ms
  10. (6 rows)
  11. benoit=> explain analyse select * from reg_users where uid not in (select uid from adv_auth);
  12.                                                   QUERY PLAN
  13. --------------------------------------------------------------------------------------------------------------
  14. Seq Scan on reg_users  (cost=34.25..58.75 rows=580 width=36) (actual time=0.135..0.148 rows=2 loops=1)
  15.    Filter: (NOT (hashed subplan))
  16.    SubPlan
  17.      ->  Seq Scan on adv_auth  (cost=0.00..29.40 rows=1940 width=4) (actual time=0.013..0.021 rows=2 loops=1)
  18. Total runtime: 0.332 ms
  19. (5 rows)
 

la deuxième méthode (ta quatrième) est bien meilleure !

Message cité 1 fois
Message édité par Taz le 23-12-2006 à 18:38:30
n°1495692
MagicBuzz
Posté le 23-12-2006 à 19:07:13  profilanswer
 

En fait, j'avais pas parlé des jointures...
 
Chose corrigée. Enjoy.
http://forum.hardware.fr/hfr/Progr [...] m#t1495691

n°1495693
MagicBuzz
Posté le 23-12-2006 à 19:09:00  profilanswer
 

Taz a écrit :

Il n'y a même pas photo. Avec mon cher postgres, et suite à un bugreport que j'ai fait (et je suis sans doute pas le seul), les clauses IN sont graves optimisées.
 

Code :
  1. benoit=> explain analyse select * from reg_users u where not exists (select * from adv_auth a where a.uid = u.uid);
  2.                                                   QUERY PLAN
  3. --------------------------------------------------------------------------------------------------------------
  4. Seq Scan on reg_users u  (cost=0.00..3994.60 rows=580 width=36) (actual time=0.076..0.116 rows=2 loops=1)
  5.    Filter: (NOT (subplan))
  6.    SubPlan
  7.      ->  Seq Scan on adv_auth a  (cost=0.00..34.25 rows=10 width=8) (actual time=0.013..0.013 rows=0 loops=4)
  8.            Filter: (uid = $0)
  9. Total runtime: 0.257 ms
  10. (6 rows)
  11. benoit=> explain analyse select * from reg_users where uid not in (select uid from adv_auth);
  12.                                                   QUERY PLAN
  13. --------------------------------------------------------------------------------------------------------------
  14. Seq Scan on reg_users  (cost=34.25..58.75 rows=580 width=36) (actual time=0.135..0.148 rows=2 loops=1)
  15.    Filter: (NOT (hashed subplan))
  16.    SubPlan
  17.      ->  Seq Scan on adv_auth  (cost=0.00..29.40 rows=1940 width=4) (actual time=0.013..0.021 rows=2 loops=1)
  18. Total runtime: 0.332 ms
  19. (5 rows)


 
la deuxième méthode (ta quatrième) est bien meilleure !


je rêve ou :
1/ le NOT IN est plus lent que le NOT EXISTS d'après ton post (surtout que je ne vois pas comment un NOT (hashed subplan) pourrait être plus rapide qu'un NOT (subplan)
2/ t'as un bug des cavernes dans ton PostGre puisque le nombre de lignes retrouvées est différent
 
a noter d'ailleurs qu'on ne fait jamais de select * dans un "exists" puisque les données retournée ne sont pas utilsées. on utilise toujours une constante (1 ou null par exemple)
 
:heink:
 
ensuite, je ne vois pas comment la première pourraît être plus lente que le NOT IN ou le NOT EXIST puisque c'est clairement la plus optimisée de toutes (aucune lecture arbitraire, et aucune optimisation nécessaire)


Message édité par MagicBuzz le 23-12-2006 à 19:13:20
mood
Publicité
Posté le 23-12-2006 à 19:09:00  profilanswer
 

n°1495698
Taz
bisounours-codeur
Posté le 23-12-2006 à 19:36:41  profilanswer
 

1) tu rêves. Le not in est plus rapide.
2) là tu rêves, le nombre de résultats est 2 dans les deux cas.
 
En fait je crois surtout que tu ne sais pas lire un explain postgres.
OSEF des timings sur des exemples, ce qui compte c'est le nombre d'itérations. Le not exists est en N, le not in en 1.
La dernière ligne, c'est juste le nombre de lignes du explain.

n°1495708
MagicBuzz
Posté le 23-12-2006 à 20:46:41  profilanswer
 

ah, ok, je viens de piger l'affichage de ton explain.
 
je viens de faire des test sous Oracle 10g R2 et contre toute attente, c'est en effet à peut près les mêmes temps, avec même parfois un léger mieux avec le NOT IN (environ 1% d'écart sur deux tables de 1 million de lignes environ).
c'est en tout ça nouveau dans Oracle 10g R2, parce qu'avec la 9i c'était le meilleur moyen de foutre la base par terre, comme c'est toujours le cas avec pas mal de SGBD.
 
en refaisant le test sous SQL Server 2005, là où c'est fun c'est que les 3 requêtes (left join, exists et in) ont le même plan d'exécution (mise à part la left join qui a un filtre un plus, qui correspond à la conservation des null uniquement à la fin)
 
le exists et le in sont donc équivalents sous SQL Server 2005
 
ceci dit, j'aimerais voir ce que ça donne avec mysql ou autres, parceque dans les versions précédentes de sql server et oracle (ainsi que postgre sql puisque tu dis que c'est suite à une relivraison que ça va bien). ça a jusqu'à présent toujours été plus lent avec le in. perso, je ne l'utiliserait pas les yeux fermés. de toute façon, sémantiquement parlant, le in ne sert pas à tester l'existance d'une valeur dans une autre table mais bon, si ça te chante d'écrire tes requêtes à l'encontre de la sémantie puis venir râler parcequ'on utilise une balise XHTML à mauvais escient libre à toi [:magicbuzz]
 
(ici un cas un peu plus complexe : pour toutes les commandes de la base, recherche les produits de la commande 1 qui n'ont pas été commandés pour chaque commande -à la base, je cherchais un cas qui marche pas avec IN, mais j'ai pas trouvé ^^)


select e2.id, c1.pro_id
from cde e1 cross join cde e2
inner join cdp c1 on c1.id = e1.id
where e1.id = 1
and not exists
(
  select null
  from cdp c2
  where c2.id = e2.id  
  and c2.pro_id = c1.pro_id
)
 
select e2.id, c1.pro_id
from cde e1 cross join cde e2
inner join cdp c1 on c1.id = e1.id
where e1.id = 1
and c1.pro_id not in
(
  select c2.pro_id
  from cdp c2
  where c2.id = e2.id  
)


 
http://img297.imageshack.us/img297/2359/planic1.png


Message édité par MagicBuzz le 23-12-2006 à 21:52:41
n°1495727
Taz
bisounours-codeur
Posté le 23-12-2006 à 22:16:16  profilanswer
 

OK, sauf que sous postgres et dans ce cas très précis, le NOT IN et le NOT EXISTS ne sont pas du tout équivalent. L'un a une complexité 1 et l'autre linéaire. Postgres écrase cette nested loop et c'est du bonheur.
 

Citation :

a noter d'ailleurs qu'on ne fait jamais de select * dans un "exists" puisque les données retournée ne sont pas utilsées. on utilise toujours une constante (1 ou null par exemple)

wof, je ne sais pas ce que SQL dit dessus. Ça ne porte pas à conséquence avec un vrai SGBD. Maintenant c'est certain d'avec une implémentation naïve incapable d'optimiser des sous-expressions ...
 
Bref je passais juste faire de la pub Postgres :P

n°1495730
MagicBuzz
Posté le 23-12-2006 à 22:23:55  profilanswer
 

(soit dit en passant, postgre est un des sgbd les plus lents qui existe :whistle: par contre il supporte tout sql92, c'est l'inverse de ses concurrents quoi :D)

n°1495743
Taz
bisounours-codeur
Posté le 23-12-2006 à 23:30:11  profilanswer
 

(cela dit en passant c'est absolument faux)

n°1495800
polo021
Posté le 24-12-2006 à 14:09:35  profilanswer
 

MagicBuzz a écrit :

j'oubliais la 4° solution :
 


select *
from reg_users
where user_id not in (select user_id from adv_auth)


 
sauf que là, je te bute si tu l'utilises


je ne connais pas comment cela fonctionne sous les autres sgdb mais sous db2, le not exists est infiniment plus radide que le not in (déconseillé aussi)

n°1495895
MagicBuzz
Posté le 25-12-2006 à 13:57:29  profilanswer
 

polo021 a écrit :

je ne connais pas comment cela fonctionne sous les autres sgdb mais sous db2, le not exists est infiniment plus radide que le not in (déconseillé aussi)


le IN a toujours été fortement déconseillé sur tous les SGBD. qu'aujourd'hui quelques SGBD sâchent le gérer de façon plus performante qu'avant n'est en aucun cas une raison pour privilégier cette syntaxe, qui reste très lente (voir source de plantage, sous Oracle <= 8i par exemple) sur la plus part des SGBD et sur l'intégralité des anciennes versions.

n°1498542
carabin
Posté le 04-01-2007 à 00:39:27  profilanswer
 

MagicBuzz a écrit :

http://forum.hardware.fr/hfr/Progr [...] 6416_1.htm
 
De mémoire j'ai expliqué dans ce topic les différents types de jointure et leur utilité.
Cela ne te dispense évidement pas de prendre un bouquin pour approfondir, mais disons que ça va débroussailler le terrain ;)
 
Genre, tu utilises déjà les INNER JOIN, c'est juste que tu ne les écrit pas explicitement ;)


 
Reprise du collier et comme promis des nouvelles. Alors merci MagicBuzz, ca marche a merveille, c'est exactement ce que je cherchais. (je vais grader la "old sholl' car comme dit je ne suis pas sur de quelle version de mysql feras tourner le bidule). Quand au perfs, ce n'est pas critique vu qu'il s'agit d'une requete faite par l'administrateur du site, donc pas souvent, et par un seule personne a la fois.
 
Quand a ton tuto, alors la je dit "respect monsieur", quand j'ai vu ca j'ai  :sweat: tellement ca m'as semblé simple a comprendre. Tu n'as pas envie de sortir un livre ? (si oui, préviens moi)
 
 
Je vois ce que tu veut dire avec INNER JOIN, -> c'est la même chose que quand j'ecris WHERE ru.user_id = aa.user_id, c'est ca ? En fait, un JOIN, ce serait déja en soi-même comme une sorte de sous requete ?
 
Par contre une question que je me pose (même si je n'en ai pas l'utilité imédiate, c'est juste par simple curiosité intellectuelle) Est ce qu'on peut combiner plusieurs JOIN (de differentes sortes) et/ou plusieurs tables dans une requete ? et si oui, on fait comment, on utilise des parentheses ?
 
Derniere question, pour faire comme il se doit, puisque j'ai la réponse a ma demande, comment je mets le [RESOLU] sur mon titre ?
 
Et merci pour tout  :jap:  
 


Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Quelle requete pour une clef absente ?

 

Sujets relatifs
[MySQL / PHP] problème avec une requète je sèche !Requete min sur une liste
Question sur requêteRequête *entre deux dates
[SQL/Access] problème pour une requête en apparence assez simpleEst il possible d'importer un "etat" ou "requète" d'access sous VB6 ??
tester la valeur d'une requete[SQL] Petit coup de main dans une requête svp!
Nombre de requete sql par page?[Oracle ASM] Problème d'optimisation de requête suite à migration
Plus de sujets relatifs à : Quelle requete pour une clef absente ?


Copyright © 1997-2022 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR