Wyobraź sobie, że masz narzędzie, które może automatycznie wykryć problemy z wydajnością JPA i Hibernate. Hypersistence Optimizer jest właśnie takim narzędziem!
Wprowadzenie
W tym artykule zobaczymy jak działa operator SQL EXISTS i kiedy należy go używać.
Mimo, że operator EXISTS jest dostępny od czasów SQL:86, pierwszego wydania standardu SQL, odkryłem, że wciąż jest wielu programistów aplikacji, którzy nie zdają sobie sprawy z tego, jak potężne są wyrażenia podzapytań SQL, jeśli chodzi o filtrowanie danej tabeli na podstawie warunku ocenianego na innej tabeli.
Model tabeli w bazie danych
Załóżmy, że mamy następujące dwie tabele w naszej bazie danych, które tworzą relację jeden do wielu. Tabela student jest tabelą nadrzędną, a student_grade jest tabelą potomną, ponieważ ma kolumnę student_id Klucz obcy odwołującą się do kolumny id Klucz główny w tabeli student.
Tabela student zawiera następujące dwa rekordy:
| id | first_name | last_name | admission_score ||----|------------|-----------|-----------------|| 1 | Alice | Smith | 8.95 || 2 | Bob | Johnson | 8.75 |
And, the student_grade table store the grades the students received:
| 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
Powiedzmy, że chcemy uzyskać wszystkich uczniów, którzy otrzymali ocenę 10 z zajęć z matematyki.
Jeśli interesuje nas tylko identyfikator ucznia, możemy wykonać zapytanie takie jak to:
SELECT student_grade.student_idFROM student_gradeWHERE student_grade.grade = 10 AND student_grade.class_name = 'Math'ORDER BY student_grade.student_id
Ale aplikacja jest zainteresowana wyświetleniem pełnej nazwy ucznia, nie tylko identyfikatora, więc potrzebujemy również informacji z tabeli student.
Aby odfiltrować student rekordy, które mają ocenę 10 z matematyki, możemy użyć operatora EXISTS SQL, jak poniżej:
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
Przy uruchomieniu powyższego zapytania widzimy, że wybrany jest tylko wiersz Alice:
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
Zewnętrzne zapytanie wybiera kolumny wierszy student, których zwróceniem do klienta jesteśmy zainteresowani. Jednak klauzula WHERE wykorzystuje operator EXISTS wraz z powiązanym z nim podzapytaniem wewnętrznym.
Operator EXISTS zwraca true jeśli podzapytanie zwraca przynajmniej jeden rekord oraz false jeśli żaden wiersz nie zostanie wybrany. Silnik bazy danych nie musi uruchamiać podzapytania w całości. Jeśli dopasowany jest pojedynczy rekord, operator EXISTS zwraca true, a powiązany z nim inny wiersz zapytania jest wybierany.
Wewnętrzne podzapytanie jest skorelowane, ponieważ kolumna student_id w tabeli student_grade jest dopasowana do kolumny id w zewnętrznej tabeli student.
SQL NOT EXISTS
Zastanówmy się, czy chcemy wybrać wszystkich studentów, którzy nie mają oceny niższej niż 9. W tym celu możemy użyć NOT EXISTS, który neguje logikę operatora EXISTS.
Operator NOT EXISTS zwraca true jeśli zapytanie nie zwróci żadnego rekordu. Jednakże, jeśli pojedynczy rekord jest dopasowany przez wewnętrzne podzapytanie, operator NOT EXISTS zwróci false, a wykonywanie podzapytania może zostać zatrzymane.
Aby dopasować wszystkie student rekordy, które nie mają powiązanych student_grade z wartością mniejszą niż 9, możemy wykonać następujące zapytanie 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
Uruchamiając powyższe zapytanie widzimy, że dopasowany jest tylko rekord Alice:
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
Cool, prawda?
Warsztaty online
Jeśli podobał Ci się ten artykuł, założę się, że spodobają Ci się moje nadchodzące 4-dniowe warsztaty online!
- High-Performance Java Persistence Online Workshop (4 godziny x 4 dni) między 19 a 22 kwietnia
Podsumowanie
Zaletą używania operatorów SQL EXISTS i NOT EXISTS jest to, że wykonywanie wewnętrznego podzapytania może być zatrzymane tak długo, jak długo znajduje się pasujący rekord.
Jeśli podzapytanie wymaga przeskanowania dużej ilości rekordów, zatrzymanie wykonania podzapytania w momencie znalezienia pasującego rekordu może znacznie przyspieszyć całkowity czas odpowiedzi zapytania.
