Immaginate di avere uno strumento che possa rilevare automaticamente i problemi di performance di JPA e Hibernate. Hypersistence Optimizer è quello strumento!
Introduzione
In questo articolo, vedremo come funziona l’operatore SQL EXISTS e quando si dovrebbe usare.
Anche se l’operatore EXISTS è stato disponibile fin da SQL:86, la primissima edizione dello standard SQL, ho scoperto che ci sono ancora molti sviluppatori di applicazioni che non si rendono conto di quanto siano potenti le espressioni di subquery SQL quando si tratta di filtrare una data tabella sulla base di una condizione valutata su un’altra tabella.
Modello di tabella del database
Prevediamo di avere le seguenti due tabelle nel nostro database, che formano una relazione uno-a-molti. La tabella student è il genitore, e la student_grade è la tabella figlia poiché ha una colonna student_id Foreign Key che fa riferimento alla colonna id Primary Key nella tabella student.
La tabella student contiene i seguenti due record:
| id | first_name | last_name | admission_score ||----|------------|-----------|-----------------|| 1 | Alice | Smith | 8.95 || 2 | Bob | Johnson | 8.75 |
E, la tabella student_grade memorizza i voti ricevuti dagli studenti:
| 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
Diciamo che vogliamo ottenere tutti gli studenti che hanno ricevuto un voto 10 in matematica.
Se siamo interessati solo all’identificatore dello studente, allora possiamo eseguire una query come questa:
SELECT student_grade.student_idFROM student_gradeWHERE student_grade.grade = 10 AND student_grade.class_name = 'Math'ORDER BY student_grade.student_id
Ma l’applicazione è interessata a visualizzare il nome completo di uno studente, non solo l’identificatore, quindi abbiamo bisogno di informazioni anche dalla tabella student.
Per filtrare i student record che hanno un voto 10 in matematica, possiamo usare l’operatore SQL EXISTS, come questo:
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
Eseguendo la query sopra, possiamo vedere che viene selezionata solo la riga Alice:
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
La query esterna seleziona le colonne student che ci interessa restituire al client. Tuttavia, la clausola WHERE utilizza l’operatore EXISTS con una sottoquery interna associata.
L’operatore EXISTS restituisce true se la sottoquery restituisce almeno un record e false se nessuna riga è selezionata. Il motore del database non deve eseguire interamente la subquery. Se viene trovato un singolo record, l’operatore EXISTS restituisce true, e l’altra riga associata alla query viene selezionata.
La subquery interna è correlata perché la colonna student_id della tabella student_grade è abbinata alla colonna id della tabella esterna student.
SQL NOT EXISTS
consideriamo di voler selezionare tutti gli studenti che non hanno un voto inferiore a 9. Per questo, possiamo usare NOT EXISTS, che nega la logica dell’operatore EXISTS.
Quindi, l’operatore NOT EXISTS restituisce true se la subquery sottostante non restituisce alcun record. Tuttavia, se un singolo record viene trovato dalla subquery interna, l’operatore NOT EXISTS restituisce false, e l’esecuzione della subquery può essere fermata.
Per abbinare tutti i record student che non hanno un student_grade associato con un valore inferiore a 9, possiamo eseguire la seguente query SQL:
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
Eseguendo la query di cui sopra, possiamo vedere che solo il record di Alice ha un riscontro:
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
Fico, no?
Workshop online
Se ti è piaciuto questo articolo, scommetto che ti piacerà il mio prossimo workshop online di 4 giorni!
- Alta-Performance Java Persistence Online Workshop (4 ore x 4 giorni) tra il 19 e il 22 aprile
Conclusione
Il vantaggio di usare gli operatori SQL EXISTS e NOT EXISTS è che l’esecuzione della subquery interna può essere fermata finché viene trovato un record corrispondente.
Se la subquery richiede la scansione di un grande volume di record, fermare l’esecuzione della subquery non appena un singolo record viene trovato può accelerare notevolmente il tempo di risposta complessivo della query.
