NOT IN

26 avr. 2005, 10:01

Cette requête est-elle correcte ?

SELECT * FROM table1 t1 WHERE  t1.champ1 NOT IN (SELECT champ2 FROM table2 t2)

edit : message supprimé par erreur par modérateur maladroit (avec toutes mes excuses)

26 avr. 2005, 10:17

par la version 4.1.7 de mysql

oui sans probleme

26 avr. 2005, 10:20

Quelle version ? je crois que ça n'est pris en compte par MySQL qu'à partir de la version 4.1

#

From MySQL 4.1 on, IN() syntax also is used to write certain types of subqueries. See Section 13.1.8.3, “Subqueries with ANY, IN, and SOME”.

#

expr NOT IN (value,...)

This is the same as NOT (expr IN (value,...)).

26 avr. 2005, 10:30

damnded je suis en MySQL 3.23.58 ...

26 avr. 2005, 11:20

Ta requête est syntaxiquement correcte, mais épouvantable en terme de performances !!!

SELECT *
FROM   table1 t1
WHERE  t1.champ1 NOT IN (SELECT champ2
                         FROM table2 t2)

Mieux vaut :

SELECT *
FROM   table1 t1 LEFT JOIN table2 t2 ON t1.champ1 = t2.champ2 
WHERE  t2.champ2 IS NULL

Cette requête est optimisée.

26 avr. 2005, 11:28

merci merci

26 avr. 2005, 15:44

Je profite de ce fil, et de l'intervention appropriée d'albat, pour préciser qu'il y a une solution simple pour contourner les subqueries si on ne dispose pas de la version MySQL appropriée.

Et même si on dispose d'une version les supportant, la doc MySQL précise qu'on a parfois intérêt à utiliser à des jointures gauches pour des raisons de performance.

Une clause LEFT [OUTER] JOIN peut être plus rapide qu'une sous-requête équivalent, car le serveur va pouvoir l'optimiser bien mieux : c'est un fait qui n'est pas spécifique à MySQL. Avant SQL-92, les jointures externes n'existaient pas, et les sous-requêtes étaient la seule méthode pour résoudre certains problèmes. Aujourd'hui, le serveur MySQL et d'autres bases de données modernes offrent toute une gamme de jointures externes.

Pour le problème posé plus haut, la solution d'albat est exactement ce qu'il faut.

Mais parfois, il est impossible d'utiliser une simple jointure gauche car il n'est pas possible de mettre une condition complexe sur la table jointe. Ainsi, pour remplacer la subquery suivante:

Code : Tout sélectionner

SELECT *
FROM table1 t1
WHERE id NOT IN(
  SELECT id FROM t2
  WHERE t2.col3 (BETWEEN 'valeur1' AND 'valeur2')
  AND t2.col4 <= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
)

On serait tenté de faire:

Code : Tout sélectionner

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.col3 (BETWEEN 'valeur1' AND 'valeur2')
AND  t2.col4 <= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
AND t2.id IS NULL

Ceci n'est pas correct et ne donnera pas le résultat escompté.

La solution est de d'abord faire une requête sur la table2 avec sa condition complexe, de stocker le "recordset" obtenu dans une table temporaire (exple: table_temp) et d'ensuite faire la jointure gauche, mais cette fois sur la table temporaire.

Code : Tout sélectionner

# query 1: requête de précaution au cas où...
DROP TABLE IF EXISTS table_temp

# query 2: création de la table temporaire
CREATE TABLE table_temp
SELECT id
FROM table2 t2
WHERE  
AND t2.col3 (BETWEEN 'valeur1' AND 'valeur2')
AND t2.col4 <= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

# query optionnelle: création d'un index temporaire pour accélérer la requête finale
# Très efficace. Je recommande chaudement!
ALTER TABLE table_temp ADD INDEX (col_a_indexer)

# query 3: exécution de la requête recherchée
SELECT *
FROM table1 t1
LEFT JOIN table_temp ON t1.id = table_temp.id
WHERE table_temp.id IS NULL

# query 4: suppression de la table temporaire
DROP table_temp

C'est plus long à écrire qu'à exécuter mais redoutablement efficace (surtout avec la création d'un index temporaire judicieusement choisi). Je fais des requêtes complexes sur un fichier de 800.000 lignes en une fraction de seconde.

On peut aussi créer une table temporaire avec un CREATE TEMPORARY TABLE table_temp dans le query 2, cette table ne sera visible que pour la seule connection en cours (mysql_connect) et sera détruite automatiquement à la fin de la connection (mysql_close ou fin du script). Mais ce type de table pose quelques problèmes (en anglais car il y a quelques "user comments" intéressants qui n'existent pas dans la doc en français)

Pour cette raison je préfère utiliser des tables standards. Mais attention: si on tente de créer une table qui existe déjà, la requête CREATE TABLE va se planter! D'où l'importance de la requête DROP TABLE IF EXISTS. Mais dans ce cas, si un autre utilisateur lance le même script, il y a risque de télescopage. La solution que j'ai adoptée est de créer un nom de table aléatoire, comme par exemple avec ce petit bout de code:

// génération d'un nom de table temporaire aléatoire
  $carAcceptes = 'azertyuiopqsdfghjklmwxcvbnAZERTYUIOPQSDFGHJKLMWXCVBN0123456789'; 
  $max = strlen($carAcceptes)-1;
  $table_temp = 'temp_';
  for($i=0; $i <= 4; $i++) { 
    $table_temp .= $carAcceptes{mt_rand(0, $max)}; 
  }

Ne pas oublier le DROP TABLE du query 4 dans ce cas, sinon la base de données sera vite saturée!

Voilà, j'ai été un petit peu long mais on voit souvent des questions sur les subqueries. J'espère avoir montré comment les éviter quand on n'a pas la bonne version ou bien comment les accélérer sensiblement... en ne les utilisant pas!

NOT IN

Qui est en ligne