Podzapytania - Instytut Informatyki

Rozdział 6: Podzapytania
(c) 1999, Instytut Informatyki Politechniki Poznańskiej
Podzapytania
• podzapytanie jest poleceniem SELECT zagnieżdżonym w
innym poleceniu SELECT
• ogólny format zagnieżdżania zapytań:
SELECT atrybut1, atrybut2, ...
FROM relacja
WHERE atrybut operator (SELECT atrybut
FROM relacja
WHERE warunek);
• operator
 =, !=, <>, >, >=, <, <=
 IN
 ANY, ALL
(c) 1999, Instytut Informatyki Politechniki Poznańskiej
Reguły zagnieżdżania podzapytań
• podzapytanie ograniczamy nawiasami i zagnieżdżamy po
prawej stronie warunku zapytania zewnętrznego;
• w podzapytaniu nie używamy klauzuli ORDER BY;
• klauzula ORDER BY może wystąpić wyłącznie jako ostatnia
klauzula najbardziej zewnętrznego zapytania;
• liczba oraz typy atrybutów występujących w klauzuli
SELECT podzapytania musi być zgodna z liczbą i typem
atrybutów użytych w warunku zapytania zewnętrznego, tj.
zapytania wyższego poziomu zagnieżdżenia;
• w podzapytaniu można używać operatorów zbiorowych;
• podzapytania są zawsze wykonywane w kolejności od
najgłębiej zagnieżdżonego do najbardziej zewnętrznego.
(c) 1999, Instytut Informatyki Politechniki Poznańskiej
Podzapytania wyznaczające jedną krotkę
• wyznacz pracownika zarabiającego najmniej
SELECT nazwisko, etat, placa_pod
FROM pracownicy
WHERE placa_pod = (SELECT MIN(placa_pod)
FROM pracownicy);
• wyznacz pracownika zarabiającego najmniej w swoim
zespole
SELECT nazwisko, etat, placa_pod
FROM pracownicy
WHERE (placa_pod, id_zesp) IN
(SELECT MIN(placa_pod), id_zesp
FROM pracownicy
GROUP BY id_zesp);
(c) 1999, Instytut Informatyki Politechniki Poznańskiej
Podzapytania wyznaczające wiele krotek cd.
• operator ANY
SELECT nazwisko, placa_pod, etat, id_zesp FROM pracownicy
WHERE placa_pod > ANY (SELECT DISTINCT placa_pod
FROM pracownicy
WHERE id_zesp = 30);
• operator ALL
SELECT nazwisko, placa_pod, etat, id_zesp FROM pracownicy
WHERE placa_pod > ALL (SELECT DISTINCT placa_pod
FROM pracownicy
WHERE id_zesp = 30);
(c) 1999, Instytut Informatyki Politechniki Poznańskiej
Podzapytania w klauzuli HAVING
• wyświetl te zespoły, w których średnia płaca podstawowa
jest większa niż średnia płaca w zespole trzydziestym
SELECT id_zesp, AVG (placa_pod) FROM pracownicy
HAVING AVG (placa_pod) > (SELECT AVG (placa_pod)
FROM pracownicy
WHERE id_zesp = 30)
GROUP BY id_zesp;
(c) 1999, Instytut Informatyki Politechniki Poznańskiej
Wielopoziomowe zagnieżdżanie zapytań
• wyświetlić nazwiska i płace pracowników, zarabiających
więcej niż wynosi maksymalna płaca w zespole o nazwie
ALGORYTMY
SELECT nazwisko, placa_pod
FROM pracownicy
WHERE placa_pod >
(SELECT MAX (placa_pod)
FROM pracownicy
WHERE id_zesp =
(SELECT id_zesp
FROM zespoly
WHERE nazwa ='ALGORYTMY'));
(c) 1999, Instytut Informatyki Politechniki Poznańskiej
Podzapytania skorelowane
• cechy
– wykonywane dla każdej krotki przeglądanej przez
zapytanie zewnętrzne
– operują na wartościach atrybutów przekazanych przez
zapytanie zewnętrzne
• przykład:
– wyszukaj pracowników zarabiających więcej niż wynosi
średnia pensja w ich zespole
(c) 1999, Instytut Informatyki Politechniki Poznańskiej
Podzapytania skorelowane cd.
P
Pracownicy
nazwisko placa_pod id_zesp
nazwisko placa_pod id_zesp
LECH
CZECH
RUS
PIAST
300
400
400
500
1
1
2
2
LECH
CZECH
RUS
PIAST
300
400
400
500
SELECT nazwisko, placa_pod, id_zesp
FROM pracownicy p
WHERE placa_pod >
(SELECT AVG(placa_pod) FROM pracownicy
WHERE id_zesp = p.id_zesp);
(c) 1999, Instytut Informatyki Politechniki Poznańskiej
1
1
2
2
Podzapytania skorelowane cd.
• operator EXISTS
– umożliwia sprawdzenie czy podzapytanie wyznacza
jakąkolwiek wartość
• operator NOT EXISTS
• przykład: wyszukaj pracowników, którym służbowo
podlegają inni pracownicy
select id_prac, nazwisko, etat, id_zesp
from pracownicy p
where exists (select id_prac
from pracownicy
where id_szefa = p.id_prac);
(c) 1999, Instytut Informatyki Politechniki Poznańskiej