Język SQL. Rozdział 6a. Podzapytania – podstawy

Język SQL. Rozdział 6a.

Podzapytania – podstawy

Podzapytania zwykłe.

Operatory ANY/SOME i ALL.

Podzapytanie

• Podzapytanie jest poleceniem SELECT zagnieżdżonym w innym poleceniu SELECT (tzw. zapytaniu głównym). Podzapytanie może wystąpić wszędzie tam, gdzie system spodziewa się zbioru

wartości, czyli w klauzulach SELECT, FROM, WHERE, HAVING i ORDER BY.

• Funkcje podzapytań:

• jako część warunku zapytania głównego – podzapytanie w klauzulach WHERE i HAVING,

• jako źródło danych dla kolumny zbioru wynikowego zapytania głównego – podzapytanie w klauzuli SELECT,

• jako zbiór danych dla zapytania głównego – podzapytanie w klauzuli FROM,

• jako zbiór wartości dla kryterium sortowania zbioru wynikowego zapytania głównego – podzapytanie w klauzuli ORDER BY.

Podzapytania zwykłe

• Podzapytanie zagnieżdżone w klauzulach WHERE lub HAVING.

• Schemat zagnieżdżenia:

• brak kl. ORDER BY w podzapytaniu.

• Dostępne operatory:

• =, <>, <, >, <=, >=, IN, NOT IN, ANY, ALL.

• Wybór operatora determinuje liczba rekordów, uzyskanych z podzapytania.

• Wykonanie:

1. jednokrotne wykonanie podzapytania, 2. wykonanie zapytania głównego.

SELECT atrybut₁, atrybut₂, ...

FROM relacja

WHERE atrybut | wyrażenie operator (SELECT atrybut | wyrażenie FROM relacja …);

SELECT atrybut₁, atrybut₂, ...

FROM relacja GROUP BY …

HAVING atrybut | wyrażenie operator (SELECT atrybut | wyrażenie FROM relacja …);

Podzapytania wyznaczające jedną krotkę (1)

• Dopuszczają użycie operatorów logicznych.

• Przykłady:

• Wyznacz pracownika zarabiającego najmniej.

• Wyznacz najgorzej zarabiającego asystenta.

SELECT nazwisko, etat, placa_pod FROM pracownicy

WHERE placa_pod =

(SELECT MIN(placa_pod)

FROM pracownicy);

208

SELECT nazwisko, etat, placa_pod FROM pracownicy

WHERE etat = 'ASYSTENT' AND placa_pod =

(SELECT MIN(placa_pod) FROM pracownicy

WHERE etat='ASYSTENT');

371

• Przykłady:

• Wyznacz najgorzej zarabiającego asystenta.

Podzapytania wyznaczające jedną krotkę (2)

SELECT nazwisko, etat, placa_pod FROM pracownicy

WHERE (placa_pod, etat) =

(SELECT MIN(placa_pod), 'ASYSTENT' FROM pracownicy

WHERE etat='ASYSTENT');

371, 'ASYSTENT'

Podzapytania wyznaczające wiele krotek (1)

SELECT nazwisko, placa_pod, placa_dod FROM pracownicy

WHERE id_zesp IN

(SELECT id_zesp FROM zespoly

WHERE nazwa in ('ALGORYTMY','ADMINISTRACJA'));

10 40

• Dopuszczają użycie operatora IN i NOT IN.

• użycie operatorów logicznych możliwe jedynie w powiązaniu z operatorami ALL i ANY/SOME.

• Przykłady:

• Wyświetl nazwiska i płace pracowników, zatrudnionych w zespołach o nazwach "ADMINISTRACJA" lub "ALGORYTMY".

Podzapytania wyznaczające wiele krotek (2)

SELECT nazwisko, placa_pod, id_zesp FROM pracownicy

WHERE (placa_pod, id_zesp) IN

(SELECT MIN(placa_pod), id_zesp FROM pracownicy

GROUP BY id_zesp);

410 10 371 20 208 30 1350 40

• Przykłady:

• Wyświetl nazwiska najgorzej zarabiających pracowników w każdym zespole.

Operatory ANY/SOME i ALL

• Stosowane w połączeniu z operatorami logicznymi.

• ANY/SOME – warunek jest prawdziwy jeśli jest spełniony dla jakiejkolwiek wartości zwróconej przez podzapytanie.

• ALL – warunek jest prawdziwy jeśli jest spełniony dla wszystkich wartości zwróconych przez podzapytanie.

SELECT nazwisko, placa_pod, etat, id_zesp FROM pracownicy WHERE placa_pod > ANY (SELECT DISTINCT placa_pod

FROM pracownicy WHERE id_zesp = 30);

SELECT nazwisko, placa_pod, etat, id_zesp FROM pracownicy WHERE placa_pod > ALL (SELECT DISTINCT placa_pod

FROM pracownicy WHERE id_zesp = 30);

Podzapytania w klauzuli HAVING

• Zasady stosowania identyczne z zasadami dla podzapytań w klauzuli WHERE.

• Przykład:

• Wyświetl te zespoły, w których średnia płaca podstawowa jest większa niż średnia płaca w całej organizacji.

SELECT nazwa, AVG(placa_pod) AS srednia

FROM pracownicy JOIN zespoly USING(id_zesp) GROUP BY nazwa

HAVING AVG(placa_pod) >

(SELECT AVG(placa_pod) FROM pracownicy);

Najczęściej popełniane błędy

• Liczba wyrażeń w klauzuli SELECT podzapytania jest niezgodna z liczbą wyrażeń w warunku:

• Podzapytanie wielorekordowe w warunku z operatorem logicznym:

SELECT nazwisko, etat, placa_pod FROM pracownicy WHERE id_zesp =

(SELECT id_zesp, nazwisko FROM pracownicy WHERE nazwisko = 'SLOWINSKI');

SELECT nazwisko, etat, placa_pod FROM pracownicy WHERE placa_pod =

(SELECT MAX(placa_pod) FROM pracownicy GROUP BY id_zesp);

ORA-01427: jednowierszowe podzapytanie zwraca więcej niż jeden wiersz ORA-00913: za duża liczba wartości

Wielopoziomowe zagnieżdżanie zapytań

• Kolejność wykonania: od najgłębiej zagnieżdżonego ku górze.

• Przykład:

• Wyświetl 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'));

40

1350

Reguły zagnieżdżania podzapytań

• W podzapytaniu nie używamy klauzuli ORDER BY, klauzula ORDER BY może wystąpić wyłącznie jako ostatnia klauzula najbardziej zewnętrznego zapytania (zapytania głównego).

• Liczba oraz typy wyrażeń w klauzuli SELECT podzapytania musi być zgodna z liczbą i typem wyrażeń użytych w warunku zapytania głównego.

• Podzapytania mogą się znaleźć w dowolnym miejscu w klauzuli WHERE/HAVING.

• Podzapytanie może być elementem wyrażenia.

SELECT * FROM pracownicy

WHERE (SELECT MIN(placa_pod) FROM pracownicy) = placa_pod;

SELECT * FROM pracownicy WHERE placa_pod >=

0.5 * (SELECT MAX(placa_pod) FROM pracownicy WHERE etat = 'PROFESOR');