Podzapytania stanowią obok złączeń drugie potężne narzędzie służące do budowy zaawansowanych zapytań. W wielu sytuacjach złączenie może być zastąpione przez użycie odpowiedniego podzapytania i na odwrót. Zdarzają się jednak sytuacje, w których tylko jedna z metod może dać oczekiwany rezultat.
Dlatego też dopiero poznanie obu tych technik pozwoli na budowanie optymalnych i dobrze zaprojektowanych zapytań.
Poprzez podzapytanie będziemy rozumieć instrukcję SELECT zagnieżdżoną wewnątrz innej instrukcji SELECT (nazywanej w takim przypadku instrukcją otaczającą). Podzapytania stosuje się w celu dostarczenia dodatkowych informacji wejściowych dla zapytania otaczającego. Dzięki temu, że wynikiem działania instrukcji SELECT (a zatem i podzapytania) może być zarówno jedna liczba, jedna kolumna, wiersz, lub cała tabela danych, podzapytań można używać w różnych częściach zapytania które je otacza. W zależności od efektu, który chcemy uzyskać podzapytania możemy użyć:
1. wewnątrz klauzuli WHERE 2. wewnątrz klauzuli HAVING
3. na liście wyrażeń po instrukcji SELECT
4. wewnątrz klauzuli FROM (jako nowej tabeli źródłowej)
4.1. Podzapytania wewnątrz klauzuli WHERE
Jednym z najczęstszych przypadków użycia podzapytań jest wykorzystanie ich wewnątrz klauzuli WHERE do uzyskania dodatkowych danych potrzebnych przy filtrowaniu rekordów. Przypuśćmy, że chcielibyśmy wyświetlić auta, których pojemność jest większa od średniej pojemności wszystkich aut zgromadzonych w bazie. Bez użycia podzapytań wydobywanie tej informacji musielibyśmy rozbić na dwa etapy. W pierwszym z nich skonstruować zapytanie obliczające średnią pojemność aut w bazie (Listing 1), a następnie wybrać z bazy te auta, których pojemność jest większa od obliczonej w poprzednim kroku wartości (Listing 2).
Rozwiązanie to jest o tyle uciążliwe, że obliczoną średnią pojemność musimy przechować „w głowie” aby ręcznie wpisać ją drugim zapytaniu. Ponadto gdy zmieni się zawartość tabeli Auta, najprawdopodobniej zmieni się również wartość średniej pojemności dla wszystkich aut, a co za tym idzie zapytanie z wpisaną „na sztywno” poprzednią wartością utraci swój sens.
SELECT AVG(A.Poj_Silnika) AS [Średnia pojemność]
FROM Auta AS A;
Listing 1: Wyliczenie średniej pojemności aut w bazie
SELECT A.ID, A.Marka, A.Model, A.Wersja, A.Poj_Silnika FROM Auta AS A
WHERE A.Poj_Silnika > 1848;
Listing 2: Wybranie aut o pojemności większej niż średnia pojemność aut w bazie
Rozwiązaniem opisanych problemów jest możliwość konstrukcji zapytania z podzapytaniem. W tym przypadku zapytanie wyświetlające auta z bazy, których pojemność spełnia określone warunki pełni rolę zapytania otaczającego, a zapytanie obliczające średnią pojemność aut w bazie – zagnieżdżonego w nim podzapytania. W rezultacie (Listing 3) otrzymujemy tylko jedno zapytanie, które należy „ręcznie” uruchomić. Wartość średniej pojemności będzie obliczana na podstawie aktualnej zawartości tabeli Auta, poprzez automatyczne wykonanie zagnieżdżonego podzapytania za każdym razem kiedy wywoływane jest zapytanie otaczające. Żaden rodzaj pojedynczego zapytania (nawet ze złączeniami) nie jest w stanie zapewnić tego samego efektu, gdyż w obrębie tylko jednej instrukcji SELECT aparat bazy danych nie jest w stanie jednocześnie wyświetlać pojedynczych rekordów oraz danych zagregowanych.
SELECT A.ID, A.Marka, A.Model, A.Wersja, A.Poj_Silnika FROM Auta AS A
WHERE A.Poj_Silnika > (SELECT AVG(A1.Poj_Silnika) FROM Auta AS A1);
Listing 3: Użycie prostego podzapytania niezwiązanego w celu wybrania aut o pojemności większej niż średnia pojemność aut w bazie
W podzapytaniu nie musimy oczywiście używać dokładnie tej samej tabeli co w zapytaniu otaczającym. Co więcej prawdziwa siła podzapytań tkwi właśnie w tym, że dodatkowe dane możemy pobierać z innych tabel niż te wymienione po klauzuli FROM otaczającej instrukcji SELECT. Jeżeli jednak, któraś z tabel powtarza się zarówno w zapytaniu otaczającym jak i w podzapytaniu, to bardzo istotne jest aby każda z kopii tej tabeli miała unikalny alias (tak jak w Listing 3 tabela Auta w zapytaniu otaczającym posiada alias A, a w podzapytaniu A1).
Pozwoli to na ich jednoznaczną identyfikację w poszczególnych częściach
wyrażeń (podobnie jak było to w przypadku samozłączeń gdzie jedna tabela powtarzała się w obrębie klauzuli FROM danego zapytania).
Z użyciem podzapytań jako części warunków logicznych wiąże się oprócz tego kilka ograniczeń. W zapytaniu z Listing 3 mieliśmy pewność, że wynikiem podzapytania będzie dokładnie jeden wiersz i jedna kolumna (podzapytanie z jedną funkcją agregującą i bez grupowania) czyli pojedyncza wartość, dlatego też mogliśmy go użyć zamiast wpisanej poprzednio liczby. W tego typu podzapytaniach musimy używać instrukcji SELECT z dokładnie jednym wyrażeniem na swojej liście kolumn. W przeciwnym wypadku aparat bazy danych nie będzie wiedział które wyrażenie ma uznać za wartość będącą wynikiem tego podzapytania. O ile ilość kolumn będących wynikiem podzapytania możemy zawsze jasno określić wypisując odpowiednią ilość wyrażeń (w szczególności jedno wyrażenie) na liście po instrukcji SELECT, o tyle przewidzenie dokładnej ilości wierszy jest raczej trudne. Jedynie w przypadku zapytań z agregacją bez grupowania mamy pewność, że zapytania zwróci dokładnie jeden wiersz. W pozostałych przypadkach ilość wierszy jest zależna od ilości rekordów (bądź grup w zapytaniach z agregacją i grupowaniem) spełniających określone warunki podane w klauzuli WHERE (lub HAVING). W takich sytuacjach może się zdarzyć, że zapytanie z podzapytaniem zakładające, że jego wynikiem podzapytania jest dokładnie jeden wiersz i przetestowane dla konkretnego zbioru danych, nagle po do bazy przestaje działać. Dzieje się tak bowiem w miejscu pojedynczej dotychczas wartości pojawia się cała kolumna.
Gdyby w podzapytaniu z Listing 3 umieścić dodatkowo grupowanie według typu silnika to aparat bazy danych wygenerowałby błąd (Listing 4). Stałoby się tak dlatego, że zapytanie z grupowaniem może zwrócić więcej niż jeden rekord. W tym przypadku podzapytanie zwracało dwie osobne średnie – dla aut z silnikiem benzynowym i aut z silnikiem Diesla (Listing 5).
SELECT A.ID, A.Marka, A.Model, A.Wersja, A.Poj_Silnika FROM Auta AS A
WHERE A.Poj_Silnika > (SELECT AVG(A1.Poj_Silnika) FROM Auta AS A1
GROUP BY A1.Typ_Silnika);
Listing 4: Błędne użycie operatora > z podzapytaniem zwracającym wiele wierszy
SELECT AVG(A.Poj_Silnika) AS [Średnia pojemność]
FROM Auta AS A
GROUP BY A.Typ_Silnika;
Listing 5: Podzapytanie z Listing 4
4.1.1. Operatory ALL i ANY
Gdy nie mamy pewności ile rekordów zwróci podzapytanie należy się zastanowić nad modyfikacją warunku logicznego. A dokładniej należy sprecyzować czy jak aparat bazy danych ma postąpić z wybraną relacją (mniejszości, większości, równości, itp.) w przypadku większej ilości rekordów zwróconych przed podzapytanie. W takiej sytuacji są dwie możliwości uznania warunku logicznego za prawdziwy. Możemy bowiem uznać, że aby cały warunek był prawdziwy (przyjął wartość TRUE) wystarczy by wyrażenie spełniało badaną relację z przynajmniej z jedną ze zwróconych przez podzapytanie wartości. Gdy nie spełni jej z żadną, wtedy cały warunek przyjmie wartość FALSE. Inną możliwością jest wymuszenie aby wyrażenie spełniało badaną relację ze wszystkimi wartościami zwróconymi w podzapytaniu. Do zapisania takich warunków w języku SQL służą odpowiednio operatory ANY (ang. jakikolwiek) i ALL (ang. wszystkie). Używa się ich w połączeniu z odpowiednim operatorem relacji (> < = <= >= <>).
Listing 4 można zatem zmodyfikować tak aby wyświetlał auta o pojemności większej od średniej pojemności przynajmniej jednej z grup (Listing 6), albo od średniej pojemności wszystkich grup (Listing 7). W pierwszym przypadku matematycznie sprowadzi się do wyświetlenia aut o pojemności większej od 1794.12, w drugim o pojemności większej od 1962.5.
Wewnątrz nawiasów po operatorach ANY lub ALL mogą się znajdować jedynie podzapytania. Nie możemy wewnątrz nich umieścić szeregu ręcznie wpisanych wartości oddzielonych przecinkami. W pamięci komputera coś podobnego robi jednak aparat bazy danych, wypełniając wnętrze tych operatorów wartościami pochodzącymi z rekordów zwróconych przez podzapytanie, a następnie używając ich do stworzenia ciągu warunków logicznych połączonych albo operatorami OR (w przypadku operatora ANY), albo operatorami AND (w przypadku operatora ALL). Podobna sytuacja miała miejsce w przypadku użycia operatora IN z paragrafu 2.
SELECT A.ID, A.Marka, A.Model, A.Wersja, A.Poj_Silnika FROM Auta AS A
WHERE A.Poj_Silnika > ANY(SELECT AVG(A1.Poj_Silnika) FROM Auta AS A1
GROUP BY A1.Typ_Silnika);
Listing 6: Użycie operatora ANY do budowy warunku logicznego z podzapytaniem
SELECT A.ID, A.Marka, A.Model, A.Wersja, A.Poj_Silnika FROM Auta AS A
WHERE A.Poj_Silnika > ALL(SELECT AVG(A1.Poj_Silnika) FROM Auta AS A1
GROUP BY A1.Typ_Silnika);
Listing 7: Użycie operatora ALL do budowy warunku logicznego z podzapytaniem
A zatem w przypadku Listing 6 i Listing 7 kombinacje: > ANY i > ALL zamieniane są odpowiednio na następujące warunki logiczne:
Poj_Silnika > ANY(...)
(Poj_Silnika > 1794.12) OR (Poj_Silnika > 1962.5) Poj_Silnika > ALL(...)
(Poj_Silnika > 1794.12) AND (Poj_Silnika > 1962.5)
4.1.2. Operator IN a podzapytania
Operatorów ANY i ALL możemy również używać wraz z operatorami relacji = i
<>. Jakkolwiek SQL rozszerza możliwości operatora IN do pracy z podzapytaniami. A zatem zamiast wpisanych ręcznie wartości, oddzielonych przecinkami wewnątrz nawiasów operatora możemy umieścić treść podzapytania. W takim przypadku działanie operatora IN sprowadza się do działania kombinacji operatorów: = ANY, a operatora NOT IN do kombinacji: <>
ALL. Operator IN jest jednym z podstawowych operatorów używanych w przypadku tworzenia zapytań z podzapytaniami. Użycie podzapytania pozwala bowiem na automatyczne wypełnienie listy jego wartości, wpisywanej dotychczas ręcznie. Przypuśćmy, że bez używania złączeń, chcemy wyświetlić listę ofert dotyczących aut marki Volkswagen. Podobnie jak było to zrobione w Listing 1 i Listing 2 należało by najpierw określić listę ID dla aut tej marki (Listing 8), a następnie z tabeli oferty wybrać tylko te rekordy, których klucz obcy ID_Auta znajduje się na otrzymanej wcześniej liście – najlepiej poprzez użycie operatora IN (Listing 9).
SELECT A.ID FROM Auta AS A
WHERE A.Marka = 'Volkswagen';
Listing 8: Wybranie ID aut marki Volkswagen
SELECT O.ID, O.ID_Auta, O.Cena, O.Data_Wstawienia FROM Oferty AS O
WHERE O.ID_Auta IN(7, 11, 24);
Listing 9: Użycie operatora IN do wybrania aut marki Volkswagen z tabeli Oferty
Niestety sposób ten niesie ze sobą dokładnie te same problemy jakie występowały w przypadku Listing 1 i Listing 2. Podstawowy z nich polega na tym, że w momencie dodania do bazy kolejnych aut marki Volkswagen, wynik zapytania z Listing 9 pozostanie bez zmian. Listing 10 usuwa tę niedogodność, używając operatora IN wraz z podzapytaniem.
SELECT O.ID, O.ID_Auta, O.Cena, O.Data_Wstawienia FROM Oferty AS O
WHERE O.ID_Auta IN(SELECT A.ID FROM Auta AS A
WHERE A.Marka = 'Volkswagen');
Listing 10: Użycie operatora IN wraz z podzapytaniem
4.1.3. Wielokrotne użycie podzapytań
W jednym zapytaniu otaczającym możemy użyć więcej niż jedno podzapytanie. Listing 11 używa dwóch podzapytań do wybrania bieżących ofert komisu, czyli aut będących na sprzedaż. W tym celu wykonuje dwa podzapytania wybierające z tabeli Transakcje auta sprzedane, a z tabeli Oferty_Wycofane auta wycofane. Wykluczając obie zwrócone przez podzapytania listy (zawierające id sprzedanych bądź wycofanych ofert) przy pomocy operatorów NOT IN.
SELECT O.ID AS [ID Oferty], O.Data_Wstawienia, O.ID_Auta, O.Przebieg, O.Cena, O.ID_Sprzedajacego
FROM Oferty AS O
WHERE (O.ID NOT IN(SELECT T.ID_Oferty FROM Transakcje AS T)) AND (O.ID NOT IN(SELECT OW.ID_Oferty
FROM Oferty_Wycofane AS OW));
Listing 11: Użycie dwóch podzapytań do wybrania bieżących ofert komisu
Zapytanie z Listing 11 odrzuca dokładnie te same rekordy, co zapytanie z poprzedniego rozdziału używające dwóch złączeń zewnętrznych i operatorów IS NULL.
Każde podzapytanie może zawierać własne, wewnętrzne podzapytania, które z kolei mogą otaczać dalsze podzapytania. O głębokości na jaką możemy zagnieżdżać podzapytania, a także o ilości podzapytań w ramach jednego zapytania otaczającego decyduje konkretny rodzaj i wersja aparatu bazy danych.
Listing 12 wyświetla wszystkie oferty, które zostały sprzedane przez sprzedawcę (ew. sprzedawców) o nazwisku 'Pliszka'. W tym celu wykonuje podzapytanie wybierające listę id ofert z tabeli Transakcje, ale tylko dla tych transakcji, dla których id sprzedawcy znajduje się na liście otrzymanej z kolejnego podzapytania, wybierającego z tabeli Sprzedawcy id sprzedawców o nazwisku 'Pliszka'.
SELECT O.ID AS [ID Oferty], O.Data_Wstawienia, O.ID_Auta, O.Przebieg, O.Cena, O.ID_Sprzedajacego
FROM Oferty AS O
WHERE O.ID IN(SELECT T.ID_Oferty FROM Transakcje AS T
WHERE T.ID_Sprzedawcy IN(SELECT S.ID
FROM Sprzedawcy AS S WHERE S.Nazwisko = 'Pliszka'));
Listing 12: Użycie podzapytania w podzapytaniu
4.1.4. Podzapytania związane i niezwiązane
Rozważane dotychczas typy podzapytań należały do grupy tzw. podzapytań niezwiązanych. Dla danego zapytania otaczającego wykonywane one były jednokrotnie na początku, a ich wynik nie zależał od aktualnie przetwarzanego rekordu. Jeżeli jednak wewnątrz klauzuli WHERE należącej do podzapytania użylibyśmy kolumny, bądź wyrażenia operującego na kolumnach tabeli (tabel) należących do zapytania otaczającego – wówczas mielibyśmy do czynienia z podzapytaniem związanym.
Przykład zapytania z podzapytaniem związanym został przedstawiony na Listing 13. Wybierani są w nim klienci, którzy dokonali przynajmniej jednego zakupu na kwotę większą bądź równą 20000 zł. W tym celu dla każdego z rekordów znajdujących się w tabeli Klienci wykonywane jest osobne podzapytanie pobierające listę kwot transakcji odpowiadających danemu klientowi kupującemu. Powiązanie zapytania otaczającego z podzapytaniem następuje poprzez warunek T.ID_Kupujacego = K.ID występujący wewnątrz klauzuli WHERE podzapytania. Otrzymana lista kwot przekazywana jest do operatora <= ANY , wewnątrz klauzuli WHERE zapytania otaczającego. Tam każda z wartości porównywana jest z kwotą 20000 zł. Jeżeli któraś z nich jest równa bądź większa od niej to cały warunek wewnątrz klauzuli WHERE zapytania otaczającego jest prawdziwy, a tym samym dany klient jest wyświetlany w końcowym zbiorze wyników.
SELECT K.Imie, K.Nazwisko, K.Miejscowosc FROM Klienci AS K
WHERE 20000 <= ANY (SELECT T.Kwota FROM Transakcje AS T
WHERE T.ID_Kupujacego = K.ID);
Listing 13: Użycie prostego zapytania związanego
Innym przykładem użycia podzapytań związanych jest wybór grup klientów pochodzących z tej samej miejscowości rozważany przy okazji omawiania samozłączeń w rozdziale 3. W przypadku użycia podzapytania związanego do wyboru właściwych grup klientów problem wydaje się być o wiele prostszy z teoretycznego punktu widzenia. Wystarczy bowiem z tabeli Klienci wybrać tylko tych klientów, których miejscowość zamieszkania znajdzie się na liście zwróconej z pewnego podzapytania. Podzapytanie to będzie sprawdzać czy w tabeli Klienci są jeszcze inni klienci pochodzący z tej samej miejscowości (warunek: K1.Miejscowosc=K.Miejscowosc). Jeżeli tak to w wynikach podzapytania owa miejscowość pojawi się tyle razy ile jest tych innych klientów.
Jeżeli klient „samotnie” pochodzi z danej miejscowości to dzięki warunkowi K1.ID <> K.ID (wykluczającym w podzapytaniu klientów przetwarzanych aktualnie w zapytaniu otaczającym) zostanie zwrócona lista pusta. Listing 14
przedstawia omówione rozwiązanie. Jego wyniki są identyczne z wynikami z poprzedniego rozdziału.
SELECT K.Imie, K.Nazwisko, K.Miejscowosc FROM Klienci AS K
WHERE K.Miejscowosc IN(SELECT K1.Miejscowosc FROM Klienci AS K1
WHERE (K1.Miejscowosc=K.Miejscowosc) AND(K1.ID <> K.ID));
Listing 14: Użycie podzapytania związanego do wyszukania grup klientów z tej samej miejs- cowości
4.1.5. Operatory EXIST i NOT EXITS
W zapytaniu z Listing 14 użycie operatora IN w celu sprawdzenia czy na liście zwróconej z podzapytania znajduje się dana miejscowość było rozwiązaniem nieco sztucznym. Lista ta mogła zawierać albo wielokrotnie powieloną tę właśnie miejscowość, albo być pusta. W takich sytuacjach gdy interesuje nas bardziej czy dane podzapytanie w ogóle coś zwraca, zamiast budować „dziwne” warunki logiczne z użyciem operatora IN, wygodniej jest się posłużyć operatorem EXIST (ang. istnieje). Operator ten zwraca prawdę gdy podzapytanie zwróciło jakiekolwiek rekordy. W przeciwnym razie operator EXIST zwróci fałsz.
Oczywiście w przypadku gdy chcielibyśmy otrzymać prawdę gdy podzapytanie nic nie zwraca możemy posłużyć się operatorem NOT EXIST.
Przykład użycia EXIST do modyfikacji Listing 14 pokazuje Listing 15.
SELECT K.Imie, K.Nazwisko, K.Miejscowosc FROM Klienci AS K
WHERE EXIST(SELECT DISTINCT K1.Miejscowosc FROM Klienci AS K1
WHERE (K1.Miejscowosc = K.Miejscowosc) AND(K1.ID <> K.ID));
Listing 15: Użycie podzapytania związanego i operatora EXIST do wyszukania grup klientów z tej samej miejscowości
W tym przypadku nie trzeba już wybierać określonej kolumny (takiej jak miejscowość) by porównywać ją z listą wartości zwróconą z podzapytania, a jedynie sprawdzić wartość operatora EXIST. Niestety operator EXIST w MS Access nie obsługuje podzapytań zwracających więcej niż jeden wiersz. Dlatego w podzapytaniu użyto dodatkowo operatora DISTINCT aby wyeliminować wielokrotne powtarzanie się nazwy tej samej miejscowości.
4.2. Podzapytania wewnątrz klauzuli HAVING
Podzapytania można stosować również wewnątrz klauzuli HAVING. Użycie ich w tym miejscu pozwala na tworzenie bardziej zaawansowanych warunków filtracji grup w jakich wykonywana jest agregacja. Listing 16 wyświetla średnią moc dla aut poszczególnych marek. Dzięki zastosowaniu podzapytania w klauzuli HAVING w końcowym raporcie wyświetlane są jedynie te grupy, których całkowita moc jest większa od jednej dziesiątej całkowitej mocy wszystkich aut w komisie.
SELECT A.Marka, AVG(A.Moc) AS [Moc Średnia]
FROM Auta AS A GROUP BY A.Marka
HAVING SUM(A.Moc) > (SELECT 0.1 * SUM(A2.Moc) FROM Auta AS A2);
Listing 16: Użycie podzapytania wewnątrz klauzuli HAVING
Użycie podzapytania wewnątrz HAVING nie wyklucza możliwości użycia go w innych częściach instrukcji SELECT. Listing 17 stosuje podzapytanie dodatkowo wewnątrz klauzuli WHERE, ograniczając ilość rekordów użytych potem w agregacji, do zbioru zwracanego poprzednio przez Listing 3.
SELECT A.Marka, AVG(A.Moc) AS [Moc Średnia]
FROM Auta AS A
WHERE A.Poj_Silnika > (SELECT AVG(A1.Poj_Silnika) FROM Auta AS A1)
GROUP BY A.Marka
HAVING SUM(A.Moc) > (SELECT 0.1 * SUM(A2.Moc) FROM Auta AS A2);
Listing 17: Jednoczesne użycie podzapytań wewnątrz klauzul WHERE i HAVING
4.3. Podzapytania na liście wyrażeń po instrukcji SELECT
W przypadku podzapytań zwracających pojedyncze wartości (np. podzapytań z agregacją, bez grupowania) możemy ich również używać do budowy wyrażeń na liście po instrukcji SELECT. Jest to na przykład bardzo pomocne w zapytaniach z agregacją i grupowaniem, gdzie chcemy mieć również dostęp do wartości zagregowanych bez grupowania (dla wszystkich rekordów). Sytuacja taka ma miejsce w Listing 18 gdzie obliczamy ilość aut w komisie dla danej marki. W wynikach zapytania chcemy również zamieścić informację o tym jaki procent wszystkich aut w komisie przypada na konkretną markę. Wykonujemy w tym celu podzapytanie niezwiązane, obliczające całkowitą ilość aut w komisie i przez otrzymaną wartość dzielimy ilość aut przypadających na daną markę, a następnie wynik mnożymy przez sto.
SELECT A.Marka, COUNT(A.Marka) AS [Ilość Aut], 100 * (COUNT(A.Marka)/(SELECT COUNT(*) FROM Auta AS A1)) AS [Procent wszystkich]
FROM Auta AS A GROUP BY A.Marka;
Listing 18: Użycie podzapytania niezwiązanego na liście wyrażeń po instrukcji SELECT
Podzapytania na liście po instrukcji SELECT mogą być również związane, w takich przypadkach przypominają coś w rodzaju złączeń, gdyż możemy za ich pomocą uzyskać dostęp do wartości z innych tabel odpowiadających danemu rekordowi. Listing 19 wyświetla informację na temat marki, modelu i wersji aut marki Opel znajdujących się w bazie. Za pomocą podzapytania związanego pobiera on również z tabeli Oferty cenę za jaką auto zostało wystawione na sprzedaż. Aby zapobiec zwróceniu wielu wierszy z podzapytania (w sytuacji, w której auto było wstawiane do komisu więcej niż jeden raz, a tym samym pojawiło się jako przedmiot więcej niż jednej oferty), używana jest funkcja AVG w celu uśrednienia cen auta z różnych ofert.
SELECT A.Marka, A.Model, A.Wersja, (SELECT AVG(O.Cena)
FROM Oferty AS O
WHERE A.ID = O.ID_Auta) AS [Cena Średnia]
FROM Auta AS A
WHERE A.Marka = 'Opel';
Listing 19: Użycie podzapytania związanego na liście wyrażeń po instrukcji SELECT
4.4. Podzapytania jako tabela źródłowa wewnątrz klauzuli FROM
Korzystając z faktu, iż podzapytanie może zwrócić swój wynik także w formie tabeli, aparaty niektórych baz danych umożliwiają wykorzystanie ich jako tabel źródłowych dla innych zapytań. Aliasy nadane kolumnom w takim podzapytaniu posłużą jako nazwy kolumn tabeli tymczasowej przekazywanej z podzapytania do zapytania otaczającego. W większości przypadków skorzystanie z podzapytania wewnątrz klauzuli FROM zapytania otaczającego może się wydawać nieco pozbawione sensu, bo po co pobierać wartości poprzez podzapytanie skoro można bezpośrednio użyć danych tabel w zapytaniu otaczającym. Istnieją jednak sytuacje w których takie rozwiązanie jest niezbędne. W rozdziale 2 wspomniano, że w systemie MS Access zliczenie ilości, bądź obliczenie np. sumy unikatowych elementów przy pomocy kombinacji odpowiedniej funkcji agregującej z operatorem DISTINCT jest niemożliwe. Używając jednak mechanizmu podzapytań można wpierw wykonać zapytanie wybierające przy pomocy operatora DISTINCT unikalne kombinacje wartości, a następnie wynik takiego podzapytania użyć jako tabelę źródłową w zapytaniu z odpowiednimi funkcjami agregującymi.
SELECT COUNT(AD.UnikatowaMarka) AS [Ilość Unikatowych Marek]
FROM (SELECT DISTINCT A.Marka AS UnikatowaMarka FROM Auta AS A) AS AD;
Listing 20: Użycie podzapytania wewnątrz klauzuli FROM do zliczenia elementów unikalnych
Podzapytania na liście FROM możemy również złączać z innymi podzapytaniami lub fizycznymi tabelami. Listing 21 prezentuje użycie złączenia wewnętrznego tabeli fizycznej Klienci z podzapytaniem obliczającym całkowitą kwotę zakupów w komisie wykonanych przez danego klienta. Z tabeli Klienci pobierane są w tym wypadku informacje na temat nazwiska, imienia i
miejscowości z której pochodzi dany klient, natomiast podzapytanie dostarcza informacji na temat wspomnianej kwoty całkowitej.
SELECT K.Nazwisko, K.Imie, K.Miejscowosc, TS.SK AS [Kwota]
FROM ((SELECT T.ID_Kupujacego AS IDK, SUM(T.Kwota) AS SK FROM Transakcje AS T
GROUP BY T.ID_Kupujacego) AS TS
INNER JOIN Klienci AS K ON K.ID = TS.IDK) ORDER BY K.Nazwisko, K.Imie, K.Miejscowosc;
Listing 21: Użycie złączenia tabeli fizycznej z podzapytaniem agregującym
Oczywiście w tym przypadku zapytanie dające dokładnie ten sam efekt można by było skonstruować posługując się złączeniem dwóch tabel fizycznych i agregacją, bez użycia podzapytania (Listing 22).
SELECT K.Nazwisko, K.Imie, K.Miejscowosc, SUM(T.Kwota) AS [Kwota]
FROM (Transakcje AS T
INNER JOIN Klienci AS K ON K.ID = T.ID_Kupujacego) GROUP BY K.Nazwisko, K.Imie, K.Miejscowosc
ORDER BY K.Nazwisko, K.Imie, K.Miejscowosc;
Listing 22: Zapytanie agregujące łączące dwie tabele fizyczne dające ten sam rezultat co List- ing 21
4.5. Podzapytania a złączenia
Jak wspomniano na początku tego rozdziału wiele problemów można rozwiązać zarówno poprzez użycie złączeń jak i poprzez użycie podzapytań.
Przykładem tego typu sytuacji było np. wyszukiwanie grup klientów z tej samej miejscowości lub wyszukanie ofert będących bieżącą ofertą komisu. Istniały również problemy rozwiązywalne jedynie przy użyciu podzapytań – jak np.
odfiltrowywanie rekordów (lub grup agregacji) przy użyciu warunków logicznych zawierających wartości zagregowane bądź to z innych tabel, bądź też z tych samych tabel ale na innym poziomie agregacji (np. zamiast dla grup to dla wszystkich rekordów z danej tabeli). Przykład takich zapytań został przedstawiony m.in. na Listing 3 oraz Listing 16. Czy oznacza to, że podzapytania są lepszą metodą od złączeń? Oczywiście, że nie. Należy bowiem pamiętać, że używając złączeń mamy jednoczesny dostęp do wszystkich kolumn z wszystkich
tabel użytych wewnątrz klauzuli FROM, a zatem możemy ich zawsze użyć na liście wyrażeń po instrukcji SELECT (np. w jednym wierszu wyświetlać informację na temat nazwisk klienta kupującego, sprzedającego oraz marki i modelu auta będącego przedmiotem transakcji). Używając podzapytań funkcjonalność tę możemy uzyskać jedynie w bardzo ograniczonym zakresie – stosując rozwiązanie opisane w podpunkcie 4.3.
A zatem nie ma ogólnej odpowiedzi na pytanie, która z metod – złączenia czy podzapytania jest lepsza i kiedy powinno się jaką stosować. O wyborze danej z nich należy decydować w zależności od tego, która jest lepsza do rozwiązania konkretnego zagadnienia. Decydując się na określoną metodę powinniśmy brać pod uwagę przede wszystkim przejrzystość rozwiązania, a także jego wydajność.
Najlepiej zawsze wtedy gdy potrzebujemy wyświetlić kolumny pochodzące z większej ilości tabel posłużyć się złączeniem, a w sytuacji kiedy filtracja rekordów zyskałaby na przejrzystości – podzapytaniem.
Należy również pamiętać że obie metody można dowolnie łączyć (i to nie tylko z podzapytaniami znajdującymi się wewnątrz klauzuli FROM – patrz podpunkt 4.4).
Listing 23 modyfikuje zapytanie z Listing 11 dołączając wewnętrznie tabele Klienci i Auta by w bieżącej ofercie komisu wyświetlić dodatkowo kolumny z informacją na temat auta i osoby chcącej auto sprzedać. W rezultacie otrzymujemy dokładnie ten sam wynik co w rozdziale 3, gdzie do odrzucenia ofert już sprzedanych bądź wycofanych, zamiast podzapytaniami posłużono się dwoma złączeniami zewnętrznymi i operatorami IS NULL.
SELECT O.ID AS [ID Oferty],
A.Marka&' '&A.Model&' '&A.Wersja AS [Auto], A.Rocznik,
O.Przebieg, O.Cena,
KS.Imie&' '&KS.Nazwisko AS [Sprzedający]
FROM ((Oferty AS O
INNER JOIN Klienci AS KS ON O.ID_Sprzedajacego = KS.ID) INNER JOIN Auta AS A ON O.ID_Auta = A.ID)
WHERE (O.ID NOT IN(SELECT T.ID_Oferty FROM Transakcje AS T)) AND (O.ID NOT IN(SELECT OW.ID_Oferty
FROM Oferty_Wycofane AS OW));
Listing 23: Jednoczesne użycie podzapytań i złączeń wewnętrznych
