Strona o szybkim czytaniu.
Podzapytania
Podzapytania proste
Podzapytania są bardzo wygodną metodą osiągania pożądanego wyniku w języku SQL. Stosujemy je wtedy, gdy w zapytaniu chcemy się odwołać nie do jakiejś statycznej wartości ale do wyniku zwróconego z innego zapytania.
Podzapytania proste wykonują się w ten sposób, że najpierw jeden raz wykonuje się podzapytanie, a następnie zwrócone przez nie wartości są podstawiane do zapytania zewnętrznego i wykonywane jest zapytanie zewnętrzne.
Przykład
Wypisać nazwę, cenę i podatek najdroższego towaru.
SELECT tow_nazwa,
tow_cena,
tow_podatek
FROM towary
WHERE tow_cena = (SELECT max(tow_cena)
FROM towary);
Przykład
Wypisać nazwę, cenę i podatek towarów, które mają tę samą stawkę opodatkowania jak lampka.
SELECT tow_nazwa,
tow_cena,
tow_podatek
FROM towary
WHERE tow_podatek = (SELECT tow_podatek
FROM towary
WHERE tow_nazwa = 'Lampka');
Podczas sprawdzania warunków w klauzuli WHERE można stosować nie tylko operator równości.
Przykład
Wypisać opis i datę złożenie zamówień, które zostały złożone wcześniej niż zamówienie nr 490.
SELECT zam_opis,
zam_data_zamowienia
FROM zamowienia
WHERE zam_data_zamowienia < (SELECT zam_data_zamowienia
FROM zamowienia
WHERE zam_numer = 490);
W jednym zapytaniu może występować kilka podzapytań.
Przykład
Wypisać numer klienta, który złożył pierwsze i ostatnie zamówienie.
SELECT zam_klt_id
FROM zamowienia
WHERE zam_data_zamowienia = (SELECT max(zam_data_zamowienia)
FROM zamowienia)
OR zam_data_zamowienia = (SELECT min(zam_data_zamowienia)
FROM zamowienia);
Powyższe zapytania działają tylko w przypadku, gdy podzapytanie zwraca nie więcej niż jeden wiersz. Jeżeli podzapytanie zwraca więcej wierszy, to zamiast operatora równości musimy użyć operatora IN.
Przykład
Wypisać wszystkie informacje o zamówieniach, które zostały złożone przez klientów indywidualnych.
SELECT *
FROM zamowienia
WHERE zam_klt_id IN (SELECT klt_id
FROM klienci
WHERE klt_typ = 'IND');
W przypadku podzapytań wielowierszowych zamiast = stosujemy IN, natomiast zamiast operatorów większości i mniejszości musimy stosować operatory ANY lub ALL. Określają one, czy sprawdzana wartość musi być większa (mniejsza) od wszystkich wartości zwróconych przez podzapytanie czy tylko większa (mniejsza) od którejkolwiek z wartości zwróconych.
Przykład
Wypisać nazwę, cenę towarów, które są droższe od któregokolwiek towaru z zamówienia o numerze 899.
SELECT tow_nazwa,
tow_cena
FROM towary
WHERE tow_cena
> ANY (SELECT tow_cena
FROM towary T, zamowienia Z, pozycje_zamowien P
WHERE T.tow_id = P.pzm_tow_id
AND P.pzm_zam_id = Z.zam_id
AND Z.zam_numer = 899);
Podzapytanie może zwracać więcej niż jedną kolumnę. Możemy wtedy porównywać całe zbiory wartości, ale jesteśmy ograniczeni tylko do operatora równości w przypadku podzapytań jednowierszowych i do operatora IN w przypadku podzapytań zwracających wiele wierszy.
Przykład
Wypisać pełną informację o tych towarach, które są najtańsze w grupie towarów objętych tą samą stawką podatku.
SELECT *
FROM towary
WHERE (nvl(tow_podatek, 0), tow_cena)
IN (SELECT nvl(tow_podatek, 0), min(tow_cena)
FROM towary
GROUP BY nvl(tow_podatek, 0));
Podzapytania mogą być wielokrotnie zagnieżdżane.
Przykład
Wypisać informację o kliencie, który złożył ostatnie zamówienie.
SELECT *
FROM klienci
WHERE klt_id
IN (SELECT zam_klt_id
FROM zamowienia
WHERE zam_data_zamowienia
= (SELECT max(zam_data_zamowienia)
FROM zamowienia));
Podzapytanie może być także użyte w klauzuli HAVING.
Przykład
Wypisać stawkę podatku oraz średnią cenę towarów w grupach objętych tą stawką, ale tylko dla grup, dla których minimalna cena w grupie jest większa od ceny mikrofonu.
SELECT nvl(tow_podatek,0), avg(tow_cena)
FROM towary
GROUP BY nvl(tow_podatek,0)
HAVING min(tow_cena) > (SELECT tow_cena
FROM towary
WHERE tow_nazwa = 'Mikrofon');
Podzapytania skorelowane
W zapytaniach zawierających podzapytania skorelowane w definicji podzapytania występuje odwołanie do wartości z zapytania zewnętrznego, tzw. korelacja.
Przykład
Wypisać nazwy, ceny i podatki towarów, dla których cena jest wyższa lub równa średniej cenie w grupie podatkowej, w której się znajdują.
SELECT tow_nazwa, tow_cena, nvl(tow_podatek, 0)
FROM towary T
WHERE tow_cena
>= (SELECT avg(tow_cena)
FROM towary
WHERE nvl(tow_podatek, 0) = nvl(T.tow_podatek, 0));
Zapytania z podzapytaniami skorelowanymi wykonują się zupełnie inaczej niż z podzapytaniami prostymi. Najpierw jest wykonywane zapytanie zewnętrzne, a następnie dla każdego wiersza zwróconego z zapytania zewnętrznego budowane jest podzapytanie i dla każdego wiersza z zapytania zewnętrznego wykonywane jest to podzapytanie.
Bardzo wiele zadań można zrealizować zarówno przy pomocy podzapytań prostych jak i przy pomocy podzapytań skorelowanych.
Operatory EXISTS i NOT EXISTS
Podzapytania z operatorami EXISTS i NOT EXISTS są szczególnym przypadkiem podzapytań skorelowanych.
Operatorów tych używamy w przypadku, gdy nie jest dla nas ważne, jakie wartości zwraca podzapytanie, ale tylko interesuje nas, czy podzapytanie zwraca jakikolwiek wiersze.
Przykład
Znaleźć te towary, które są złożone z innych towarów.
SELECT *
FROM towary T
WHERE EXISTS (SELECT *
FROM towary
WHERE tow_tow_id = T.tow_id);
Ponieważ wartości zwracane przez podzapytanie nie mają żadnego znaczenia, to na liście SELECT w podzapytaniu może znaleźć się dowolna wartość.
Przykład
Znaleźć te towary, które nie były zamawiane w 1999 roku.
SELECT *
FROM towary T
WHERE NOT EXISTS
(SELECT 1
FROM pozycje_zamowien P,
zamowienia Z
WHERE P.pzm_zam_id = Z.zam_id
AND P.pzm_tow_id = T.tow_id
AND to_char(Z.zam_data_zamowienia, 'yyyy') = '1999');
Podzapytania w klauzuli FROM
Każde zapytanie dostarcza zbioru danych uszeregowanych w postaci wierszy i kolumn. a więc w takiej postaci, w jakiej dane są przechowywane w tabelach. Można to wykorzystać wpisując podzapytanie w klauzuli FROM zapytania zewnętrznego.
W takim wypadku wynik zwrócony przez podzapytanie jest traktowany jak dynamicznie utworzona tabela, która posiada pewien określony zbiór kolumn i wierszy, do których można odwoływać się w zapytaniu zewnętrznym jak do kolumn i wierszy zwykłej, statycznej tabeli.
Przykład
Wypisać w jednym wierszu raport zawierający ilość towarów objętych podatkiem 22%, 7% i pozostałymi stawkami. Raport powinien wyglądać następująco:
22% 7% pozostałe
-----.---- -----------
16 5 1
SELECT sum(T.t22) "22%", sum(T.t7) "7%", sum(T.tx) "pozostałe"
FROM (SELECT count(*) t22, 0 t7, 0 tx
FROM towary
WHERE tow_podatek = 22
UNION ALL
SELECT 0 t22, count(*) t7, 0 tx
FROM towary
WHERE tow_podatek = 7
UNION ALL
SELECT 0 t22, 0 t7, count(*) tx
FROM towary
WHERE tow_podatek NOT IN (7, 22)) T;
Przykład
Wypisać opisy wszystkich zamówień zrealizowanych, ilość pozycji w każdym z zamówień, łączną kwotę do zapłaty za każde zamówienie bez uwzględnienia rabatu i rabat przysługujący dla każdego zamówienia (w postaci kwoty).
SELECT Z1.zam_opis "Zamowienie",
Z2.ile_pozycji "Ilość pozycji",
Z2.kwota "Kwota",
Z2.kwota*G.grc_rabat/100 "Rabat"
FROM zamowienia Z1,
(SELECT Z.zam_id,
count (*) ile_pozycji,
sum (T.tow_cena * P.pzm_ilosc) kwota
FROM zamowienia Z,
pozycje_zamowien P,
towary T
WHERE Z.zam_id = P.pzm_zam_id
AND T.tow_id = P.pzm_tow_id
AND Z.zam_data_realizacji IS NOT null
GROUP BY Z.zam_id) Z2,
grupy_cen G
WHERE Z1.zam_id = Z2.zam_id
AND Z2.kwota BETWEEN G.grc_cena_od AND G.grc_cena_do;
Aby móc odwoływać się w zapytaniu do kolumn tabeli powstałej poprzez wykonanie podzapytania należy zdefiniować alias do podzapytania oraz aliasy do kolumn podzapytania, które powstały na skutek obliczenia wyrażenia i przez to nie posiadają swojej nazwy.