Podzapytania SQL

Czym jest podzapytanie

Podzapytanie to zapytanie SQL, które umieszczone jest wewnątrz innego zapytania. Podzapytanie zawsze otoczone jest parą nawiasów (). Jak zwykle spróbuję pokazać to na przykładzie. Dla przypomnienia, najprostsze zapytanie SQL może wyglądać tak:

Po wykonaniu takiego zapytania otrzymasz pojedynczy wiersz zawierający jedną kolumnę z wartością 1. Teraz trochę skomplikuję to zapytanie:

Efekt działania obu przykładów jest dokładnie taki sam. Drugi przykład używa podzapytania. Główne zapytanie SELECT * FROM zwraca wszystkie wiersze zwrócone przez podzapytanie SELECT 1. Przykład, który tu pokazałem jest trochę naciągany, bardziej prawdopodobny przykład może wyglądać następująco:

Ponownie rozbiję to zapytanie na czynniki pierwsze. Proszę zwróć uwagę na podzapytanie:

To zapytanie zwraca listę identyfikatorów płodnych artystów ;). Zapytanie zwraca identyfikatory artystów z tabeli album, którzy opublikowali więcej niż dziesięć albumów.

W połączeniu z głównym zapytaniem otrzymuję nazwy artystów, którzy opublikowali więcej niż dziesięć albumów.

Podzapytania skorelowane

Poprzedni przykład pokazywał „zwykłe” podzapytania. Istnieją jeszcze tak zwane podzapytania skorelowane. Czasami nazywa się je także zapytaniami powiązanymi. Od zwykłych różnią się one tym, iż są powiązane z nadrzędnym zapytaniem. Spróbuję wyjaśnić to na przykładzie:

To zapytanie zwraca identyfikator utworu, identyfikator albumu i tytuł utworu z tabeli track. Zwraca wyłącznie takie utwory, które są dziesięć razy dłuższe niż najkrótszy utwór z tego samego albumu. W tym przypadku podzapytanie używa dokładnie tej samej tabeli. Żeby móc odróżnić tabelę track z zapytania wewnętrznego, od tej samej tabeli w zapytaniu zewnętrznym używam aliasów – słowa kluczowego AS.

Do tej pory w kursie posługiwałem się wyłącznie aliasami kolumn, jak widzisz istnieje także możliwość nadania aliasu tabelom.

Zapytania skorelowane nie są możliwe do wykonania bez dostępu do zapytania nadrzędnego. W tym przypadku zapytanie nie może być wykonane samodzielnie dlatego, iż nie wie czym jest tabela outer_track.

Po co stosuje się podzapytania

Powtórzę jeszcze raz przykład z poprzedniego punktu:

Czy można osiągnąć ten sam efekt bez podzapytania¹? Oczywiście, iż można. Jednym ze sposobów jest użycie stałej listy identyfikatorów artystów. Listę tych identyfikatorów zwróci zapytanie:

Następnie taką listę można użyć w kolejnym zapytaniu:

Takie podejście ma jednak swoje wady. Jedną z nich jest to, iż trzeba wykonać dwa zapytania. Kolejną jest potrzeba modyfikowania drugiego zapytania na podstawie wyników pierwszego. Co więcej taka modyfikacja nie zawsze jest możliwa – co jeżeli lista zwróconych identyfikatorów miałaby kilkadziesiąt tysięcy elementów?

Podzapytania mogą mieć wiele zastosowań. Czasami osiągnięcie oczekiwanego efektu nie jest możliwe bez użycia podzapytania. Stosowanie podzapytań czasami może także prowadzić do uproszczenia finalnego zapytania.

Podzapytania mogą mieć różny wpływ na wydajność zapytania. jeżeli wydajność zapytania jest kluczowa sprawdzaj plan zapytania upewniając się czy usunięcie podzapytań mogłoby przyspieszyć jego wykonanie².

Gdzie może występować podzapytanie

Podzapytanie może występować praktycznie wszędzie wewnątrz zapytania SQL. To gdzie podzapytanie może być użyte uzależnione jest od tego ile wartości zwraca. jeżeli podzapytanie zwraca pojedynczą wartość może być użyte jako część wyrażenia – na przykład w porównaniach, czy zwracanych kolumnach.

W przypadku gdy podzapytanie zwraca wiele wartości może być użyte na przykład w porównaniach czy jako tabela źródłowa. Poniższe przykłady powinny wyjaśnić poszczególne przypadki.

Podzapytanie wewnątrz listy pobieranych wartości

Wyobraź sobie raport, który musisz przygotować. Raport powinien zawierać wszystkie faktury klientów. Poszczególne kolumny powinny pokazywać identyfikator klienta, wartość faktury i globalną średnią wartość faktur. Tego typu problem możesz rozwiązać używając podzapytania:

W tym przypadku podzapytanie zwraca pojedynczą wartość – globalną średnią wartość wszystkich faktur:

W połączeniu z zapytaniem głównym zwróci następujące wyniki:

Okazuje się, iż raport nie jest idealny. Lepiej wyglądałoby zestawienie wartości poszczególnych faktur ze średnią faktur dla danego klienta. W tym przypadku podzapytanie musi bazować na kolumnie dostępnej w zapytaniu głównym. Aby móc tego dokonać niezbędne jest używanie aliasów (w tym przypadku aliasów dla tabel):

W tym przypadku podzapytanie przez cały czas zwraca pojedynczą wartość. Jednak tym razem wartość ta zależna jest od identyfikatora klienta znajdującego się w danym wierszu. Dla przykładu wybrałem jeden z identyfikatorów:

Zwróć uwagę, iż tym razem zapytanie główne zwraca średnią charakterystyczną dla wszystkich klienta (która jest rożna od średniej dla wszystkich klientów):

Drugi przypadek pokazuje podzapytanie skorelowane. To podzapytanie powiązane jest z zapytaniem głównym. W odróżnieniu od pierwszego przypadku musi zostać wykonane wiele razy. Średnia użyta w pierwszym przypadku może być obliczona dokładnie raz dla uzyskania poprawnego wyniku.

Podzapytanie wewnątrz klauzuli FROM

Wyniki podzapytania użytego wewnątrz klauzuli FROM traktowane są jakby były tabelą. Dlatego w tym przypadku podzapytanie może zwrócić wiele wartości. Kolumny użyte w podzapytaniu stają się kolumnami „tabeli” i mogą być użyte w zapytaniu głównym.

Proszę spójrz na przykład:

Ponownie zacznę od analizy podzapytania:

Podzapytanie sumuje wszystkie poszczególnych klientów. Zwraca dokładnie tyle wierszy ile jest wartości kolumny customerid:

Następnie taki wynik użyty jest do policzenia średniej z wszystkich sum. Ostatecznym wynikiem zapytania jest liczba pokazująca średnią sumę zamówień wszystkich klientów:

Podzapytania tego typu mogą być użyte w bardziej skomplikowanych zapytaniach. Proszę spójrz na przykład poniżej:

Analizę ponownie zacznę od podzapytania:

Podzapytanie używa klauzuli GROUP BY żeby zwrócić średnią wartość zamówienia dla wszystkich stanu:

Następnie takie wyniki, używając klauzuli JOIN, złączone są z tabelą invoice. Kolumną używaną do złączenia jest billingstate. Wynikiem jest zbiór wierszy zawierający faktury, które mają uzupełnioną kolumnę billingstate (efekt złączenia). Każda taka faktura zestawiona jest później ze średnią obowiązującą w danym stanie:

Podzapytania wewnątrz klauzuli WHERE

Podzapytanie może być także użyte do filtrowania wyników głównego zapytania. Przykład poniżej pokazuje takie zapytanie:

W tym przypadku podzapytanie zwraca dziesięciokrotność długości najkrótszej ścieżki:

Następnie ten wynik użyty jest do zwrócenia ścieżek, które są krótsze od tej wartości:

Możliwe jest także używanie podzapytań zwracających wiele wartości. Proszę spójrz na przykład poniżej:

W tym przypadku podzapytanie zwraca listę identyfikatorów typów których nazwa pasuje do wyrażenia '%AAC%'. Następnie te identyfikatory użyte są do odfiltrowania ścieżek, które mają odpowiednią wartość kolumny mediatypeid. Innymi słowy zapytanie zwraca ścieżki, które są w formacie pasującym do '%AAC%'.

Wyżej wspomniałem już o zapytaniach powiązanych. Musisz wiedzieć, iż podzapytania powiązane mogą wystąpić także w innych miejscach. Poniżej pokazuję Ci przykład takiego podzapytania występującego w klauzuli WHERE:

W tym przypadku podzapytanie zwraca średnią długość ścieżki dla wszystkich albumu. Następnie wartość ta użyta jest w głównym zapytaniu. Pozwala ona zwrócić wyłącznie te wiersze, które dotyczą ścieżek o długości krótszej niż średnia z ich albumu.

Operator EXISTS

W artykule dotyczącym klauzuli WHERE pominąłem między innymi możliwość użycia operatora EXISTS. Operator EXISTS powoduje, iż zwrócone są wyłącznie te wiersze, dla których podzapytanie zwróci co najmniej jeden wiersz. Proszę spójrz na przykład:

W tym przypadku skorelowane podzapytanie zwraca wiersze, które połączone są relacją szef-podwładny. Wiersze, które zawierają pracowników nie posiadających podwładnych są pominięte. Dzieje się tak dlatego, iż podzapytanie w ich przypadku nie zwróci ani jednego wiersza.

Operatory ALL i ANY

Operatory ALL i ANY nie są obsługiwane przez bazę SQLite.

Operatory ALL i ANY używa się w połączeniu z operatorami porównania z klauzuli WHERE.

Na przykład wyrażenie kolumna > ALL (podzapytanie) oznacza, iż kolumna musi mieć większą wartość niż wszystkie wartości zwrócone przez podzapytanie.

Analogicznie kolumna <= ANY (podzapytanie) oznacza, iż kolumna musi mieć wartość mniejszą bądź równą którejkolwiek z wartości zwróconych przez podzapytanie.

Chociaż SQLite nie wspiera tych operatorów identyczne zachowanie, w przypadku niektórych zapytań, można uzyskać stosując funkcje MIN albo MAX. Dla przykładu dwa poniższe zapytania dałyby te same wyniki:

Podzapytania jako wyrażenie

Podzapytania zwracające pojedynczą wartość mogą traktowane być jako wyrażenie. W związku z tym mogą wystąpić w innych miejscach zapytania SQL. Kilka zapytań tego typu omówiłem dokładnie w poprzednich podpunktach.

Poniżej pokazuję kilka przykładów obrazujących użycie podzapytań w innych miejscach zapytania SQL.

Podzapytania wewnątrz klauzuli ORDER BY

Dziwne, ale poprawne sortowanie:

Podzapytania wewnątrz klauzuli LIMIT

Ponownie dziwne, ale poprawne ograniczanie liczby wierszy:

Podzapytania wewnątrz klauzuli HAVING

Tym razem podzapytanie zostało użyte do zwrócenia wierszy, dla których suma jest większa niż suma w jednym ze stanów:

Podzapytania a klauzula JOIN

Często istnieje wiele sposobów na uzyskanie tych samych wyników. W przypadku niektórych podzapytań możliwe jest ich zastąpienie odpowiednimi złączeniami. Poprawne użycie klauzuli JOIN może pomóc w usunięciu niechcianego podzapytania.

Podzapytania w innych rodzajach zapytań

Do tej pory w ramach kursu SQL omawiałem wyłącznie zapytania typu SELECT. W języku SQL istnieją także inne rodzaje zapytań. Musisz wiedzieć, iż także w zapytaniach typu UPDATE czy DELETE możesz spodziewać się użycia podzapytań.

Dobre praktyki przy używaniu podzapytań

To, iż coś jest możliwe, wcale nie znaczy, iż powinno być używane. Zapytania SQL gwałtownie mogą stać się mało czytelne. Przez co będą trudne w zrozumieniu i późniejszym utrzymaniu. jeżeli podzapytanie wprowadza niepotrzebne zamieszanie postaraj się rozwiązać problem inaczej – czasami jest to możliwe na przykład przy użyciu klauzuli JOIN.

Ta sama klauzula może także pomóc w optymalizowaniu zapytania zawierającego podzapytania. Dobrą praktyką jest porównanie planu wykonania obu wersji zapytania. Plan zapytania możesz sprawdzić używając EXPLAIN <zapytanie sql>.

Podzapytanie w podzapytaniu podzapytania

Podzapytania to twory, które mogą być zagnieżdżone. W zależności od silnika bazy danych limit zagnieżdżonych podzapytań może być różny. Mimo tego, iż takie konstrukcje są możliwe, w codziennej pracy nie spotkałem się za podzapytaniami zagnieżdżonymi więcej niż dwa poziomy.

Nadmierne zagnieżdżanie podzapytań nie jest dobrą praktyką. Takie łańcuszki nie poprawiają czytelności zapytania. Dodatkowo powoduje problemy z jego utrzymaniem. jeżeli musisz stosować więcej niż jeden, dwa poziomy zagnieżdżenia zastanów się czy nie można rozwiązać tego problemu inaczej.

Zadania do wykonania

Poniżej przygotowałem dla Ciebie zestaw kilku zadań, które pozwolą Ci sprawdzić wiedzę dotyczącą podzapytań w praktyce. Zanim zerkniesz do przykładowego rozwiązania zachęcam się do samodzielnej próby rozwiązania zadań – w ten sposób nauczysz się najwięcej.

Napisz zapytanie używając podzapytań, które zwróci:

1. sumaryczną wartość (kolumna total) faktur (tabela invoice), których kwota jest powyżej średniej wartości wszystkich faktur,
2. średnią liczbę albumów (tabela album) dla artystów, którzy opublikowali więcej niż dwa albumy,
3. wiersze zawierające identyfikator klienta (kolumna customerid) i wartość faktur ponad średnią wartość faktur danego klienta (wartość - średnia). Zapytanie powinno zwrócić wyłącznie wiersze gdzie ta różnica jest większa od 0,
4. te same wyniki, które zwraca zapytanie poniżej bez użycia klauzuli JOIN:
   SELECT name FROM artist JOIN album ON artist.artistid = album.artistid GROUP BY name HAVING COUNT(*) > 10;
5. te same wyniki, które zwraca zapytanie poniżej bez użycia klauzuli JOIN:
   SELECT invoiceid ,total ,invoice.billingstate ,billingstate_avg.state_avg FROM (SELECT billingstate ,AVG(total) AS state_avg FROM invoice GROUP BY billingstate) AS billingstate_avg JOIN invoice ON billingstate_avg.billingstate = invoice.billingstate;

Przykładowe rozwiązania zadań

1.

2.

3.

4.

5.

Podsumowanie

Po lekturze artykułu wiesz już czym są podzapytania. Wiesz doskonale gdzie można ich używać. Udało Ci się także poznać kilka dobrych praktyk dotyczących używania podzapytań. Po samodzielnym rozwiązaniu zadań możesz śmiało powiedzieć, iż potrafisz posługiwać się podzapytaniami.

Artykuł ten zamyka część kursu poświęconą zapytaniom typu SELECT. W kolejnych częściach kursu poznasz pozostałe elementy języka SQL niezbędne do codziennej pracy.

Mam nadzieję, iż artykuł przypadł Ci do gustu. Udało Ci się rozwiązać zadania? Podziel się swoimi rozwiązaniami! Spojrzenie na ten sam problem z innego punktu widzenia pozwoli wszystkim na nauczenie się jeszcze więcej.

Zależy mi na dotarciu do nowych Czytelników, jeżeli uważasz, iż ten artykuł byłby wartościowy dla kogoś z Twoich znajomych bardzo proszę podziel się z nim odnośnikiem do tego artykułu. Z góry dziękuję!

Jeśli nie chcesz ominąć kolejnych artykułów w przyszłości proszę dopisz się do samouczkowego newslettera i polub Samouczka na Facebook’u. Trzymaj się i do następnego razu!