Imaginez avoir un outil capable de détecter automatiquement les problèmes de performance de JPA et Hibernate. Hypersistence Optimizer est cet outil !
Introduction
Dans cet article, nous allons voir comment fonctionne l’opérateur SQL EXISTS et quand vous devez l’utiliser.
Bien que l’opérateur EXISTS soit disponible depuis SQL:86, la toute première édition de la norme SQL, j’ai constaté qu’il y a encore beaucoup de développeurs d’applications qui ne réalisent pas à quel point les expressions de sous-requêtes SQL sont vraiment puissantes lorsqu’il s’agit de filtrer une table donnée en fonction d’une condition évaluée sur une table différente.
Modèle de table de la base de données
Supposons que nous avons les deux tables suivantes dans notre base de données, qui forment une relation de table un à plusieurs. La table student est le parent, et la student_grade est la table enfant puisqu’elle possède une student_id colonne de clé étrangère référençant la id colonne de clé primaire dans la student table.
La table student contient les deux enregistrements suivants :
| id | first_name | last_name | admission_score ||----|------------|-----------|-----------------|| 1 | Alice | Smith | 8.95 || 2 | Bob | Johnson | 8.75 |
Et, la table student_grade stocke les notes obtenues par les étudiants :
| id | class_name | grade | student_id ||----|------------|-------|------------|| 1 | Math | 10 | 1 || 2 | Math | 9.5 | 1 || 3 | Math | 9.75 | 1 || 4 | Science | 9.5 | 1 || 5 | Science | 9 | 1 || 6 | Science | 9.25 | 1 || 7 | Math | 8.5 | 2 || 8 | Math | 9.5 | 2 || 9 | Math | 9 | 2 || 10 | Science | 10 | 2 || 11 | Science | 9.4 | 2 |
SQL EXISTS
Disons que nous voulons obtenir tous les étudiants qui ont reçu une note de 10 en cours de mathématiques.
Si nous ne sommes intéressés que par l’identifiant de l’élève, alors nous pouvons exécuter une requête comme celle-ci :
SELECT student_grade.student_idFROM student_gradeWHERE student_grade.grade = 10 AND student_grade.class_name = 'Math'ORDER BY student_grade.student_id
Mais, l’application est intéressée par l’affichage du nom complet d’un élève, et pas seulement de l’identifiant, donc nous avons besoin des infos de la table student également.
Afin de filtrer les enregistrements student qui ont une note de 10 en mathématiques, nous pouvons utiliser l’opérateur SQL EXISTS, comme ceci :
SELECT id, first_name, last_nameFROM studentWHERE EXISTS ( SELECT 1 FROM student_grade WHERE student_grade.student_id = student.id AND student_grade.grade = 10 AND student_grade.class_name = 'Math')ORDER BY id
Lorsque nous exécutons la requête ci-dessus, nous pouvons voir que seule la ligne Alice est sélectionnée :
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
La requête externe sélectionne les colonnes du rang student que nous souhaitons renvoyer au client. Cependant, la clause WHERE utilise l’opérateur EXISTS avec une sous-requête interne associée.
L’opérateur EXISTS renvoie true si la sous-requête renvoie au moins un enregistrement et false si aucun rang n’est sélectionné. Le moteur de base de données n’a pas besoin d’exécuter entièrement la sous-requête. Si un seul enregistrement est mis en correspondance, l’opérateur EXISTS renvoie true, et l’autre ligne de requête associée est sélectionnée.
La sous-requête interne est corrélée car la colonne student_id de la table student_grade est mise en correspondance avec la colonne id de la table student externe.
SQL NOT EXISTS
Considérons que nous voulons sélectionner tous les étudiants qui n’ont pas de note inférieure à 9. Pour cela, nous pouvons utiliser NOT EXISTS, qui annule la logique de l’opérateur EXISTS.
Par conséquent, l’opérateur NOT EXISTS renvoie true si la sous-requête sous-jacente ne renvoie aucun enregistrement. Cependant, si un seul enregistrement est mis en correspondance par la sous-requête interne, l’opérateur NOT EXISTS renvoie false, et l’exécution de la sous-requête peut être arrêtée.
Pour faire correspondre tous les enregistrements student qui n’ont pas de student_grade associé avec une valeur inférieure à 9, nous pouvons exécuter la requête SQL suivante :
SELECT id, first_name, last_nameFROM studentWHERE NOT EXISTS ( SELECT 1 FROM student_grade WHERE student_grade.student_id = student.id AND student_grade.grade < 9)ORDER BY id
Lors de l’exécution de la requête ci-dessus, nous pouvons constater que seul l’enregistrement Alice est mis en correspondance :
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
Cool, non ?
Ateliers en ligne
Si vous avez apprécié cet article, je parie que vous allez adorer mon prochain atelier en ligne de 4 jours !
- Haute…Performance Java Persistence Online Workshop (4 heures x 4 jours) entre le 19 et le 22 avril
Conclusion
L’avantage d’utiliser les opérateurs SQL EXISTS et NOT EXISTS est que l’exécution de la sous-requête interne peut être arrêtée tant qu’un enregistrement correspondant est trouvé.
Si la sous-requête nécessite de balayer un grand volume d’enregistrements, l’arrêt de l’exécution de la sous-requête dès qu’un seul enregistrement est apparié peut considérablement accélérer le temps de réponse global de la requête.
.