Stellen Sie sich vor, Sie hätten ein Tool, das automatisch JPA- und Hibernate-Performance-Probleme erkennen kann. Hypersistence Optimizer ist dieses Tool!
Einführung
In diesem Artikel werden wir sehen, wie der SQL-Operator EXISTS funktioniert und wann Sie ihn verwenden sollten.
Obwohl der EXISTS-Operator seit SQL:86, der allerersten Ausgabe des SQL-Standards, zur Verfügung steht, habe ich festgestellt, dass es immer noch viele Anwendungsentwickler gibt, die nicht wissen, wie mächtig SQL-Subquery-Ausdrücke wirklich sind, wenn es darum geht, eine bestimmte Tabelle auf der Grundlage einer Bedingung zu filtern, die in einer anderen Tabelle ausgewertet wurde.
Datenbanktabellenmodell
Angenommen, wir haben die folgenden zwei Tabellen in unserer Datenbank, die eine eins-zu-viele-Tabellenbeziehung bilden. Die student-Tabelle ist die übergeordnete, und die student_grade ist die untergeordnete Tabelle, da sie eine student_id Fremdschlüsselspalte hat, die auf die id Primärschlüsselspalte in der student Tabelle verweist.
Die Tabelle student enthält die folgenden zwei Datensätze:
| id | first_name | last_name | admission_score ||----|------------|-----------|-----------------|| 1 | Alice | Smith | 8.95 || 2 | Bob | Johnson | 8.75 |
Und die Tabelle student_grade speichert die Noten, die die Schüler erhalten haben:
| 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
Angenommen, wir wollen alle Schüler erhalten, die im Matheunterricht die Note 10 erhalten haben.
Wenn wir nur an der Kennung des Schülers interessiert sind, können wir eine Abfrage wie diese ausführen:
SELECT student_grade.student_idFROM student_gradeWHERE student_grade.grade = 10 AND student_grade.class_name = 'Math'ORDER BY student_grade.student_id
Aber die Anwendung ist daran interessiert, den vollständigen Namen eines Schülers anzuzeigen, nicht nur die Kennung, also benötigen wir auch Informationen aus der Tabelle student.
Um die student-Datensätze zu filtern, die eine 10 in Mathe haben, können wir den SQL-Operator EXISTS verwenden, etwa so:
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
Wenn wir die obige Abfrage ausführen, können wir sehen, dass nur die Zeile Alice ausgewählt wird:
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
Die äußere Abfrage wählt die student Zeilenspalten aus, die wir an den Client zurückgeben möchten. Die WHERE-Klausel verwendet jedoch den EXISTS-Operator mit einer zugehörigen inneren Unterabfrage.
Der EXISTS-Operator gibt true zurück, wenn die Unterabfrage mindestens einen Datensatz zurückgibt und false, wenn keine Zeile ausgewählt wurde. Die Datenbank-Engine muss die Subquery nicht vollständig ausführen. Wenn ein einzelner Datensatz gefunden wird, gibt der EXISTS-Operator true zurück, und die zugehörige andere Abfragezeile wird ausgewählt.
Die innere Unterabfrage ist korreliert, da die student_id-Spalte der student_grade-Tabelle mit der id-Spalte der äußeren student-Tabelle abgeglichen wird.
SQL NOT EXISTS
Angenommen, wir wollen alle Schüler auswählen, die keine schlechtere Note als 9 haben. Dazu können wir NOT EXISTS verwenden, das die Logik des EXISTS-Operators negiert.
Der NOT EXISTS-Operator gibt also true zurück, wenn die zugrundeliegende Subquery keinen Datensatz liefert. Wenn jedoch ein einzelner Datensatz von der inneren Subquery gefunden wird, gibt der NOT EXISTS-Operator false zurück, und die Ausführung der Subquery kann beendet werden.
Um alle student-Datensätze zu finden, die kein zugehöriges student_grade mit einem Wert kleiner als 9 haben, können wir die folgende SQL-Abfrage ausführen:
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
Wenn wir die obige Abfrage ausführen, können wir sehen, dass nur der Datensatz von Alice übereinstimmt:
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
Cool, oder?
Online-Workshops
Wenn Ihnen dieser Artikel gefallen hat, wette ich, dass Sie meinen kommenden 4-tägigen Online-Workshop lieben werden!
- HighPerformance Java Persistence Online Workshop (4 Stunden x 4 Tage) zwischen dem 19. und 22. April
Fazit
Der Vorteil der Verwendung der SQL-Operatoren EXISTS und NOT EXISTS ist, dass die Ausführung der inneren Subquery angehalten werden kann, solange ein passender Datensatz gefunden wird.
Wenn die Subquery eine große Anzahl von Datensätzen durchsuchen muss, kann das Anhalten der Subquery-Ausführung, sobald ein einzelner Datensatz gefunden wird, die Gesamtantwortzeit der Abfrage erheblich beschleunigen.
