SQL SELECT : l'opérateur ensembliste IN, NOT IN.
Réponse AJAX

IN (liste de valeurs)

IN est un opérateur qui vérifie si une valeur fait partie d'un ensemble de valeurs, c'est l'équivalent de l'opérateur mathématique que l'on note ∈.

Il fonctionne ainsi : valeur IN (ensemble de valeurs), il renvoie un booléen (vrai ou faux). La valeur à gauche de l'opérateur IN et les valeurs à droite de l'opérateur IN doivent être du même type.

Par exemple, quels élèves ont une adresse électronique chez Microsoft ?

SELECT CONCAT(usr_first_name, ' ', usr_name) as Eleve, usr_email
FROM user
WHERE SUBSTRING(usr_email, INSTR(usr_email,'@') + 1) IN ('hotmail.fr', 'live.fr', 'hotmail.com') ;
Eleveusr_email
Romain Bonvalotnikanoru_du_84@hotmail.com
Florent Luckluckflorent@hotmail.fr
Johan Moraleschampionut@hotmail.com
Cédric Torresced.torres@hotmail.fr
Romain Dumaszenenbru@hotmail.com
Nicolas Bessactarba_supreme84@hotmail.com
Yann Triboulatdj_farfade@hotmail.com
Arnaud Taelmanshinryu_666@hotmail.com
Thomas Vivienle_bastidon_chartrain@hotmail.com
......
exécuté en 0.003 sec.

Exemple fictif : quels produits sont de catégorie "Bureautique" ou "SGBDR" ? Ce qui simplifie la formulation suivante, complètement équivalente :

Toutes les questions sont basées sur le schéma relationnel suivant. Vous devrez écrire les requêtes SQL qui donnent le résultat demandé.

Seules l'adresse du client et la ville du client peuvent avoir la valeur NULL, tous les autres champs sont forcément renseignés.

Donnez les clients qui ont été facturés le 2013-06-11, le 2013-06-12 ou le 2013-06-14. Le résultat attendu est :
reponse 1

Votre réponse n°1

Donnez la liste des factures des clients 41, 42, 43 Le résultat attendu est :
reponse 2

Votre réponse n°2

IN (requête)

La liste de valeurs peut être fournie par une requête. Par exemple, quels sont les élèves qui ont des réponses fausses aux TP dynamiques :

SELECT CONCAT(usr_first_name, ' ', usr_name) as Eleve
FROM user
WHERE usr_id IN (SELECT tp_usr_id FROM cours_tp_rep WHERE tp_rep_validee = 2) ;
Eleve
Ysser Detest
Alexa Strozzi
Vincent Soubrat
Guillaume Guibal
Baptiste Buttigieg
Steven Jeanne
Jérémy Speidel
Tristan Spinnewyn
Yvan De Min
...
exécuté en 0.003 sec.

On constate que dans ce cas, le IN remplace une jointure. En effet, la requête précédente aurait pu s'écrire :

SELECT CONCAT(usr_first_name, ' ', usr_name) as Eleve
FROM user, cours_tp_rep
WHERE usr_id = tp_usr_id
AND tp_rep_validee = 2 ;
Eleve
Ysser Detest
Adrien Guillermet
Jorys Pascal
Benjamin Petit
Maxime Reynaud
Hugo Benmeziane
Yohan Buttigieg
Thomas Collard
Thomas Collard
...
exécuté en 0.013 sec.

Il reste à rajouter la clause DISTINCT pour obtenir exactement la même réponse. En effet, la première version (avec IN) ne multiplie pas les réponses, alors que la deuxième (avec la jointure) oui.

SELECT DISTINCT CONCAT(usr_first_name, ' ', usr_name) as Eleve
FROM user, cours_tp_rep
WHERE usr_id = tp_usr_id
AND tp_rep_validee = 2 ;
Eleve
Ysser Detest
Adrien Guillermet
Jorys Pascal
Benjamin Petit
Maxime Reynaud
Hugo Benmeziane
Yohan Buttigieg
Thomas Collard
Hugo De Cavel
...
exécuté en 0.015 sec.

Grâce à une jointure, donnez la liste des clients qui ont des factures supérieures ou égales à 1000 euros. Le résultat attendu est :
reponse 3

Votre réponse n°3

Faites la même chose, mais en utilisant le IN au lieu d'utiliser une jointure. Vous devez obtenir exactement le même résultat.

Votre réponse n°4

NOT IN (liste de valeurs)

Le NOT IN sert à vérifier qu'une valeur donnée n'existe pas dans une liste de valeurs.

Exemple fictif, je veux les programmes télé sauf ceux de Canal+ et de TMC : Ce qui simplifie la formulation suivante, complètement équivalente :

Donnez la liste des clients qui habitent ni Montfavet, ni Avignon. Le résultat attendu est :
reponse 5

Votre réponse n°5

Afficher toutes les factures sauf celles du 2013-06-11, du 2013-06-12 ni du 2013-06-14. Le résultat attendu est :
reponse 6

Votre réponse n°6

NOT IN (requête)

Généralités

Voici la formulation la plus intéressante du IN, car elle permet de nouveaux types de questions à la base de données : qui n'a pas fait ça (au sens de : qui figure dans une table et pas dans une autre). Par exemple :

La formulation de ce genre de requête, très méthodique, pose souvent des problèmes à ceux qui débutent. Prenons l'exemple "Quel élève n'a pas commencé à répondre à ce TP" et voyons la méthode :

On remarque que la requête principale n'a pas besoin de DISTINCT puisqu'elle ne multiplie pas les résultats (on prend des élèves dans la table user). Le DISTINCT dans la requête imbriquée est facultatif (si la liste utilisée par le NOT IN contient plusieurs fois les mêmes valeurs, ça n'a aucune incidence sur l'affichage).

Bien placer chaque condition

Attention à bien placer chaque condition de l'énoncé. Certaines sont dans la requête principale (elles correspondent à ceux qu'on veut afficher), d'autres sont dans la requête imbriquée (elles correspondent à ce que n'ont pas fait ceux qu'on veut afficher).

Par exemple : je veux afficher les élèves de SIO1 qui n'ont pas commencé ce TP :

Cela donne :
SELECT CONCAT(usr_first_name, ' ', usr_name) as Eleve
FROM user
WHERE usr_special & 4
AND usr_id NOT IN (SELECT tp_usr_id FROM cours_tp_rep WHERE tp_id = 59 AND usr_special & 4) ;
Eleve
Ysser Detest
Véran Bourgues
Sofian Bourarach
Otmane El Khadfi
Mike Gallina
Romain Vaudron
Damien AISSAOUI
Jonathan CAPELLI
Zaccharie DI CRISTOFARO
...
exécuté en 0.002 sec.

Donnez la liste des prospects, c'est à dire les clients qui n'ont jamais été facturés. Le résultat attendu est :
reponse 7

Votre réponse n°7

Donnez la liste des clients qui n'ont pas été facturés en 2013 (YEAR(date_facture)=2013). Le résultat attendu est :
reponse 8

Votre réponse n°8

Donnez la liste des clients qui habitent Avignon (ville_client = 'avignon') mais qui n'ont pas été facturés en 2013. Le résultat attendu est : reponse 9

Votre réponse n°9

Lien vers les études de cas

Etude de cas DA, 2011, métropole : Cas Atlantik, dossier 4, question 3 (bateaux pour lesquels aucun affrètement n'a été enregistré au mois de mars 2011) (go).

Etude de cas DA, 2010, métropole : Cas NOIXCOOP, dossier 2, question 4 (variétés d'arbres ne faisant l'objet d'aucun prix de vente) (go).