Język SQL. Rozdział 6b.
Podzapytania – konstrukcje zaawansowane
Podzapytania skorelowane. Operator EXISTS.
Podzapytania w klauzulach SELECT, FROM i ORDER BY.
Czy podzapytania "zwykłe" są wystarczające?
• Przykład 1. Podaj dane najgorzej opłacanych pracowników w poszczególnych zespołach.
• Zbiory danych:
SELECT id_zesp, nazwisko, placa_pod FROM pracownicy WHERE (id_zesp, placa_pod) IN
(SELECT id_zesp, MIN(placa_pod)
FROM pracownicy GROUP BY id_zesp) ORDER BY id_zesp, nazwisko;
ID_ZESP MIN(PLACA_POD)
10 410.2
20 371
30 208
40 1350
ID_ZESP NAZWISKO PLACA_POD
10 MAREK 410.2
10 WEGLARZ 1730
20 BRZEZINSKI 960
20 JEZIERSKI 439.7
20 KONOPKA 480
20 KOSZLAJDA 590
… … …
podzapytanie
zapytanie główne
Czy podzapytania "zwykłe" są wystarczające?
• Zbiory danych:
• Wykonanie:
1. MAREK: (10,410.2) IN ((10,410.2),(20,371),(30,208),(40,1350)) 2. WEGLARZ: (10, 1730) IN ((10,410.2),(20,371),(30,208),(40,1350)) 3. …
• Ogólnie:
<pracownik>: (id_zesp, placa_pod) IN (id_zesp, MIN(placa_pod))
• Inaczej: zapytanie_główne.id_zesp = podzapytanie.id_zesp AND
zapytanie_główne.placa_pod = podzapytanie.MIN(placa_pod)
ID_ZESP MIN(PLACA_POD)
10 410.2
20 371
30 208
40 1350
ID_ZESP NAZWISKO PLACA_POD
10 MAREK 410.2
10 WEGLARZ 1730
20 BRZEZINSKI 960
20 JEZIERSKI 439.7
20 KONOPKA 480
20 KOSZLAJDA 590
… … …
podzapytanie
zapytanie główne
Czy podzapytania "zwykłe" są wystarczające?
• Przykład 2. Podaj dane pracowników zarabiających więcej od najgorzej opłacanych pracowników w poszczególnych zespołach.
• Warunki:
• Problem:
• zapytania składowe korzystają z różnych operatorów.
• Zapytanie:
zapytanie_główne.id_zesp = podzapytanie.id_zesp AND
zapytanie_główne.placa_pod > podzapytanie.MIN(placa_pod)
SELECT id_zesp, nazwisko, placa_pod FROM pracownicy WHERE (id_zesp, placa_pod) ???
(SELECT id_zesp, MIN(placa_pod)
FROM pracownicy GROUP BY id_zesp) ORDER BY id_zesp, nazwisko;
Podzapytanie skorelowane (1)
• Pozwala na wykonanie selekcji niedostępnych dla podzapytań zwykłych (bez korelacji).
• Wykonywane wielokrotnie – dla każdego rekordu przeglądanego przez zapytanie główne.
• Operuje na wartościach atrybutów przekazanych przez zapytanie główne.
• Zawsze odwołuje się do atrybutu zapytania głównego (w klauzuli WHERE lub HAVING).
SELECT atrybuta, atrybutb, ...
FROM relacja
WHERE atrybutn >=
(SELECT atrybutj FROM relacja
WHERE atrybutj = atrybutb);
zapytanie główne
podzapytanie skorelowane
Podzapytanie skorelowane (2)
• Wykonanie:
1. Pobranie rekordu Ri przez zapytanie główne.
2. Wykonanie podzapytania na podstawie wartości z rekordu Ri 3. Zaakceptowanie bądź odrzucenie rekordu Ri.
4. Pobranie kolejnego rekordu Ri+1 przez zapytanie główne i powtórzenie kroków od 2. do 4.
• Przykład:
• Wyświetl dane pracowników zarabiających więcej niż najgorzej opłacany pracownik zespołu, w którym pracują.
SELECT nazwisko, placa_pod, id_zesp FROM pracownicy p
WHERE placa_pod >
(SELECT MIN(placa_pod) FROM pracownicy
WHERE id_zesp = p.id_zesp);
Podzapytanie skorelowane (3)
NAZWISKO PLACA_POD
LECH 300
CZECH 400
RUS PIAST
400 500
ID_ZESP
1 1 2 2
Pracownicy
NAZWISKO PLACA_POD
LECH 300
CZECH 400
RUS PIAST
400 500
ID_ZESP
1 1 2 2
P
SELECT nazwisko, placa_pod, id_zesp FROM pracownicy p
WHERE placa_pod >
(SELECT MIN(placa_pod) FROM pracownicy
WHERE id_zesp = p.id_zesp);
• Zwraca wartość prawdy jeżeli podzapytanie zwraca jakikolwiek rekord. Podzapytanie nie musi zwracać wartości z bazy danych, równie dobrze może zwracać dowolny literał.
• Przykłady:
Operator EXISTS
SELECT id_prac, nazwisko, etat, id_zesp FROM pracownicy p
WHERE EXISTS (SELECT * FROM pracownicy WHERE id_szefa = p.id_prac);
SELECT nazwisko, etat, id_zesp FROM pracownicy p
WHERE NOT EXISTS (SELECT 'A' FROM zespoly WHERE id_zesp = p.id_zesp);
• Jest źródłem danych dla jednej kolumny zbioru wynikowego.
• Musi zwrócić co najwyżej jedną wartość dla każdego rekordu zapytania głównego (często osiągane przez korelację).
• Przykłady:
SELECT nazwa, (SELECT MAX(placa_pod) FROM pracownicy
WHERE id_zesp = z.id_zesp) AS maks_placa FROM zespoly z
ORDER BY nazwa;
SELECT nazwisko, (SELECT nazwisko FROM pracownicy
WHERE id_prac = p.id_szefa) AS szef FROM pracownicy p
ORDER BY nazwisko;
Podzapytanie w klauzuli SELECT (1)
• Przykłady:
• Uwaga! Często problem można rozwiązać bez ww. konstrukcji.
SELECT nazwa, (SELECT MAX(placa_pod) FROM pracownicy
WHERE etat = nazwa) AS maks_placa, (SELECT AVG(placa_pod)
FROM pracownicy
WHERE etat = nazwa) AS średnia_placa FROM etaty
ORDER BY nazwa;
Podzapytanie w klauzuli SELECT (2)
SELECT nazwa, MAX(placa_pod) AS maks_placa, AVG(placa_pod) AS średnia_placa
FROM etaty LEFT JOIN pracownicy ON etat = nazwa GROUP BY nazwa
ORDER BY nazwa;
Podzapytanie w klauzuli FROM (1)
• Tworzy wejściowy zbiór danych dla zapytania głównego – pozwala to na rozbicie rozwiązania problemu na etapy.
• Dopuszczalne użycie klauzuli ORDER BY.
• Podzapytanie może mieć zdefiniowany alias.
• Przykład:
• Znajdź pracowników zarabiających rocznie ponad 15 000 zł.
SELECT nazwisko, suma FROM (SELECT nazwisko,
12*placa_pod + COALESCE(placa_dod, 0) AS suma FROM pracownicy)
WHERE suma > 15000 ORDER BY suma DESC;
Podzapytanie w klauzuli FROM (2)
• Przykład:
• Wyświetl nazwiska pracowników, którzy zarabiają więcej niż średnia płaca w zespole, w którym pracują.
SELECT nazwisko, placa_pod, srednia_w_zespole
FROM (SELECT id_zesp, AVG(placa_pod) AS srednia_w_zespole FROM pracownicy
GROUP BY id_zesp) z
JOIN pracownicy p ON z.id_zesp = p.id_zesp WHERE placa_pod > srednia_w_zespole
ORDER BY srednia_w_zespole DESC;
Podzapytanie w klauzuli ORDER BY
• Wynik podzapytania jest wykorzystywany do uporządkowania zbioru wynikowego zapytania głównego.
• Podzapytanie dla każdego rekordu zapytania głównego musi zwrócić dokładnie jedną wartość.
• Przykład:
• Pokaż nazwiska wszystkich pracowników. Wynik uporządkuj w kolejności malejącej wg liczności zespołów, do których należą pracownicy, oraz rosnąco wg nazwisk pracowników.
SELECT nazwisko FROM pracownicy p
ORDER BY (SELECT COUNT(*) FROM pracownicy
WHERE p.id_zesp = id_zesp) DESC, nazwisko;
