Wykład 7. Zapytania z podzapytaniem.

Wykład V omawia składnię zapytań SQL, w których w roli argumentu klauzul FROM, WHERE, HAVING występuje inne zapytanie, czyli składnię zapytań z podzapytaniem. Omówione zostaną zapytania z podzapytaniem„zwykłe”, jak też zapytania skorelowane z podzapytaniem.

Język SQL umożliwia użycie w roli argumentu porównań wyniku działania instrukcji SELECT. Zapytania o takiej konstrukcji zwykło się nazywać zapytaniem z podzapytaniem. Podzapytania mogą wystąpić wewnątrz klauzul FROM, WHERE i HAVING. Mają taką samą konstrukcję, jak inne instrukcje SELECT, natomiast podlegają pewnym ograniczeniom i muszą być ujęte w nawiasy.

Podzapytanie w klauzulach WHERE i HAVING może wystąpić, jako prawy argument operatorów =, <, <=, >, >=, <> oraz predykatów IN, NOT IN, a także, jako definicja wartości przedziału w operatorze zawierania BETWEEN … AND. W przypadku operatorów =, <, <=, >, >=, <> oraz BETWEEN … AND, podzapytanie powinno zwracać jedną wartość, a w przypadku predykatów IN oraz NOT IN może to być lista wartości.

Wewnątrz podzapytania nie może pojawić się klauzula ORDER BY. Może się ona pojawić w całej konstrukcji polecenia tylko raz, na samym jej końcu.
Podzapytanie nie może być kończone średnikiem. Średnik jest znakiem zakończenia całej konstrukcji polecenia.

Standard języka nie przewiduje możliwości użycia w konstrukcji podzapytania operatorów algebraicznych UNION, INTERSECT i EXCEPT (MINUS). Dialekty ORACLE oraz MS SQL Server rozszerzają w tym wypadku wymogi standardu, dopuszczając takie konstrukcje.

W podzapytaniu dostępne są nazwy kolumn (szerzej – nazwy wyrażeń) wprowadzone w głównym zapytaniu. Konstrukcję wykorzystującą tą właściwość nazywamy zapytaniem skorelowanym z podzapytaniem (w skrócie podzapytaniem skorelowanym).

Jeżeli zbiór wynikowych wierszy podzapytania nie zmienia się w zależności od wierszy w zapytaniu głównym (nie zależy od wartości wierszy w głównym zapytaniu) mówimy o podzapytaniu zwykłym. Jeżeli taka zależność istnieje, mamy do czynienia z podzapytaniem skorelowanym. W jednej instrukcji może zostać użytych kilka podzapytań.

Podzapytania zwykłe.

Najprostszym przykładem użycia podzapytania, jest wykorzystanie go do wyliczenia wartości, która będzie następnie użyta, jako argument porównania. Rozważmy następujący przykład:

Przykład 7.1.

Znajdź najlepiej zarabiającego pracownika.

Zadanie to może być rozwiązane dwuetapowo. W pierwszym etapie znajdziemy najwyższą płacę w całej firmie:

SELECT	Max(Sal)
FROM	EMP;

a następnie zwróconą przez to zapytanie wartość (wynosi ona dla naszej przykładowej bazy 5000) użyjemy, jako argument w kolejnym zapytaniu:

SELECT	Ename ”Pracownik z najlepszą płacą”, Sal
FROM	EMP
WHERE	Sal = 5000;

Ale mając do dyspozycji konstrukcję zapytania z podzapytaniem, możemy poszukiwany wynik otrzymać przy użyciu jednej instrukcji:

SELECT	Ename ”Pracownik z najlepszą płacą”, Sal
FROM	EMP
WHERE	Sal =	(SELECT Max(Sal)
		FROM	EMP);

W jednym zapytaniu może znaleźć się kilka podzapytań.

Przykład 7.2.

Znajdź nazwiska pracowników, którzy pracują na tym samym stanowisku, co pracownik o numerze 7369 i których zarobki są większe niż zarobki pracownika o numerze 7876.

Zadanie zawiera dwa warunki, które powinny zostać zrealizowane w klauzuli WHERE. Oba warunki powinny zostać zdefiniowane poprzez podzapytania.

SELECT	Ename, Job
FROM	EMP
WHERE	Job =	(SELECT Job
		FROM	EMP
		WHERE	Empno = 7369)
		AND Sal >	(SELECT Sal
				FROM	EMP
				WHERE	Empno = 7876);

Gdy podzapytanie zwraca wiele wierszy, należy użyć IN lub NOT IN. Operator IN zwraca w wyniku te rekordy, dla których wyrażenie zdefiniowane po WHERE ma wartość równą któremukolwiek elementowi listy.

Przykład 7.3.

Wypisz nazwy działów, w których pracują urzędnicy.

SELECT	Dname
FROM	DEPT
WHERE	Deptno IN SELECT Deptno
		  FROM	EMP
 		  WHERE	Job = 'CLERK');

Musi zostać tutaj użyty operator IN , gdyż podzapytanie zwraca więcej niż jedną wartość (listę wartości).

Operator NOT IN, a pseudowartość NULL

W przypadku zastosowania operatora NOT IN do podzapytanie zwracającego listę wartości, wśród których znajduje się pseudowartość NULL, pojawia się problem. O żadnej wartości, nie można powiedzieć, że nie jest NULL, zatem zapytanie nadrzędne zwróci zbiór pusty!

Przykład 7.4.

Znajdź pracowników, którzy nie są kierownikami (nie mają podwładnych).

Pozornie zadanie to daje się łatwo rozwiązać, stosując operator NOT IN do listy wartości Mgr zwróconej przez podzapytanie.

SELECT	Prac.Ename
FROM	EMP Prac
WHERE	Prac.Empno NOT IN SELECT Podwladny.Mgr
			  FROM   EMP Podwladny);

Powyższe rozwiązanie nie zwraca jednak żadnego rekordu, choć wiemy, że na liście wartości EMPNO znajdują się wartości, które nie występują na liście wartości Mgr. Ale na liście wartości Mgr, wskazujących na przełożonych pracowników, znajduje się jedna wartość NULL – pracownik King nie ma przełożonego. I to jest przyczyna!

Rozwiązaniem problemu jest użycie funkcji konwertującej pseudowartość Null na wartość znaczącą, czyli zamiast Podwladny.Mgr należy użyć:

w ORACLE NVL(Podw.Mgr,0)

w MS SQL Server 2005 Isnull(Podw.Mgr,0)

W dalszej części wykładów przedsatwimy inne rozwiązanie tego problemu, wykorzystujące korelację pomiędzy zapytaniem i podzapytaniem.

Kwantyfikatory ALL i SOME (ANY)

W odniesieniu do podzapytania zwracającego listę wartości, możemy użyć jednego z dwóch kwantyfikatorów ALL i ANY/SOME (SOME i ANY działają identycznie).

• Kwantyfikator ALL realizuje operację „od każdej wartości znajdującej się na liście”.
• Kwantyfikator SOME (ANY) realizuje operację „od jakiejkolwiek wartości znajdującej się na liście”.

Przykład 7.5.

Znajdź pracowników, którzy zarabiają więcej, niż najlepiej zarabiający pracownik działu 10.

SELECT	Ename, Sal
FROM	EMP
WHERE	Sal > ALL (SELECT Sal
		  FROM	EMP
		  WHERE	Deptno = 10);

Przykład 7.6.

Znajdź pracowników, którzy zarabiają więcej, niż najmniej zarabiający pracownik działu 10.

SELECT	Ename, Sal
FROM	EMP
WHERE	Sal > ANY (SELECT Sal
		  FROM EMP
		  WHERE Deptno = 10);

Użycie operatorów ALL i ANY pozwala rozwiązać w sposób elegancki pewien problem.

Przykład 7.7.

Znajdź dział, w którym są najwyższe średnie zarobki.

Zadanie to jest banalnie proste, pod warunkiem, że implementacja języka SQL pozwala na zagnieżdżanie funkcji agregujących tak, jak ma to miejsce w ORACLE.

SELECT		Dname, Loc, AVG(Sal)
FROM		DEPT
INNER JOIN	EMP
ON		EMP.Deptno = DEPT. Deptno
GROUP BY	Dname, Loc
HAVING		AVG(Sal) = 	(SELECT		MAX(AVG(Sal))
				FROM		EMP
				GROUP BY	Deptno);

Niestety, nawet wersja MS SQL Server 2008 R2 takich zagnieżdżeń, zgodnie z wymogami standardu, nie dopuszcza. Rozwiązaniem jest użycie kwantyfikatora ALL:

SELECT		Dname, Loc, AVG(Sal)
FROM		DEPT
INNER JOIN	EMP
ON		EMP.Deptno = DEPT. Deptno
GROUP BY	Dname, Loc
HAVING		AVG(Sal) >= ALL (SELECT	 AVG(Sal)
				FROM	 EMP
				GROUP BY Deptno);

Znalezienie rozwiązania tego zadania, bez użycia zagnieżdżania funkcji i bez użycia kwantyfikatora, pozostawiamy czytelnikowi.

Podzapytanie w klauzuli FROM

Jak już wcześniej wspomniano, podzapytania mogą pojawić się w klauzuli FROM. Pełnią wówczas rolę definiowanych ad hoc widoków. Aby w konstrukcji całego zapytania móc się do niego odwołać, musi zostać nazwany poprzez opatrzenie go aliasem. W ORACLE przed definicją aliasu nie może pojawić się słowo AS.

Przykład 7.8.

Wypisz nazwiska (Ename), płacę (Sal), numer departamentu (Deptno) i średnią płacę w departamencie, dla pracowników, których płace przekraczają średnią ich departamentów.

SELECT	Ename “Pracownicy zarabiający powyżej średniej”
	,Sal Płaca
	,EMP.Deptno “Numer departamentu”
	,Dept_avg “Średnia płaca w departamencie”
FROM	EMP,
	(SELECT  Deptno, AVG(Sal) Dept_avg
	FROM	 EMP
	GROUP BY Deptno) NOWA
WHERE	EMP.Deptno = NOWA.Deptno
AND	Sal > Dept_avg;

W powyższym rozwiązaniu NOWA jest aliasem wirtualnej tabeli (widoku), zdefiniowanej w celu wyliczenia średniej płacy w departamencie. Do tego aliasu odwołujemy się w warunkach powiązań i warunkach ograniczających klauzuli WHERE tak, jakby to była nazwa istniejącej tabeli.

Podzapytania skorelowane.

Jak już wspomniano na początku tego wykładu, w podzapytaniu dostępne są nazwy wyrażeń, wprowadzone w zapytaniu głównym. Korzystając z tej właściwości, konstrukcja zapytania z podzapytaniem może uzależniać zbiór wyników podzapytania od wartości występujących w wierszach głównego zapytania. Koncepcję konstruowania takiego rozwiązania pokażemy na przykładzie.

Przykład 7.9.

Dla każdego działu wypisz osobę, która zarabia najwięcej w tym dziale.

Rozwiązanie możemy realizować według koncepcji:

Maksymalne zarobki w danym dziale = 
	SELECT Max(Sal)
	FROM Emp
	WHERE Deptno =
			<Deptno określony w głównym zapytaniu>

Pełne rozwiązanie wygląda następująco:

SELECT	E.Deptno
	E.Ename
	E.Sal
FROM	Emp E
WHERE	Sal = 	(SELECT Max(EMP.Sal)
		FROM EMP
		WHERE E.Deptno = EMP.Deptno);

Konieczność zastosowania aliasu E tabeli EMP wynika z konieczności odwołania do tej samej tabeli po obu stronach porównania klauzuli WHERE. Zaznaczono to w treści rozwiązania przez podkreślenie.

Koncepcję realizacji tego rozwiązania można opisać następująco:
Tworzone jest zapytanie główne (nadrzędne), odczytujące wszystkie rekordy z tabeli EMP. Następnie, dla pierwszego odczytanego rekordu, wykonywane jest zapytanie podrzędne, znajdujące wartość płacy maksymalnej dla tego departamentu, którego numer odczytany został we właśnie rozpatrywanym rekordzie zapytania nadrzędnego, co jest możliwe dzięki wykonaniu porównania w klauzuli WHERE, łączącego wyniki zapytań nadrzędnego i podrzędnego. Jeżeli wartość płacy Sal, odczytanej w zapytaniu nadrzędnym, jest równa płacy maksymalnej obliczonej w zapytaniu podrzędnym, rozpatrywany rekord wchodzi do wyniku całego rozwiązania, a jeżeli nie, zostaje odrzucony. Ta operacja powtarzana jest dla wszystkich rekordów odczytanych w zapytaniu nadrzędnym.

Ta koncepcja może być porównywana z działaniem kursora – konstrukcji programistycznej, która zostanie omówiona w dalszej części wykładów z przedmiotu KBD.

Predykaty EXISTS i NOT EXISTS

W niektórych przypadkach, w rozwiązaniu zapytania skorelowanego z podzapytaniem, wystarczająca jest informacja, czy dla rozpatrywanego rekordu zapytania nadrzędnego zapytanie podrzędne zwraca pusty zbiór wyników, czy nie. W celu testowania używane są predykaty EXISTS i NOT EXISTS.

Przykładowa konstrukcja podzapytania wykorzystującego predykat EXISTS ma następującą postać:

[NOT] EXISTS (SELECT 'x' FROM EMP WHERE Deptno = 10)

Dla wyniku nie jest istotne, co napiszemy na liście SELECT w ramach predykatu EXISTS. Najprostsza obliczeniowo jest wartość stała taka jak 'x‘ lub 1, gdyż istotne jest tylko, czy podzapytanie zwraca „coś” czy też „nic”.

Przykład 7.10.

Wypisz działy, w których aktualnie nie ma zatrudnionych pracowników.

SELECT DISTINCT	 Dname
FROM		 DEPT
WHERE NOT EXISTS (SELECT 1
		 FROM	EMP
		 WHERE	Emp.Deptno = Dept.Deptno);

Za pomocą predykatu NOT EXISTS można rozwiązać problem znalezienia tych wszystkich pracowników, którzy nie są kierownikami. Problemu tego nie udało się poprzednio rozwiązać za pomocą predykatu NOT IN z powodu występowania pseudo-wartości NULL w wyniku podzapytania.

SELECT	Empno
	,Ename
	,Job
	,Deptno
FROM	EMP AA
WHERE NOT EXISTS (SELECT 1
		 FROM	EMP BB
		 WHERE	BB.Mgr = AA.Empno);

W tym rozwiązaniu również wystąpiła konieczność użycia aliasów AA i BB w odniesieniu do tabeli EMP.

Podsumowanie podzapytań

Podzapytania mogą występować w następujących postaciach:

• WHERE wartość = < > >= <= <> (zapytanie zwracające 1 wartość)
• WHERE wartość [NOT] IN (zapytanie zwracające wiele wartości)
• WHERE wartość < > <= >= SOME | ALL | ANY (zapytanie zwracające wiele wartości)
• WHERE [NOT] EXISTS (dowolne zapytanie)

Analogicznie do przedstawopnych powyżej, podzapytania mogą wystąpić w klauzuli HAVING.

• FROM (zapytanie) alias

Podsumowanie zapytań

Na tym kończymy prezentację grupy poleceń języka SQL, odpowiedzialnych za odczytywanie danych z bazy danych. Przedstawione w wykładach 4 – 7 instrukcje SELECT, czyli zapytania języka SQL, odczytujące dane z tabel bazy danych, w zasadzie wyczerpują konstrukcje przewidziane przez składnię języka. W omówieniu pominięto niektóre dopuszczalne konstrukcje, mające mniejsze znaczenie praktyczne, a także pewnych konstrukcji, które choć są realizowane przez serwery baz danych, wymuszają bardzo nieefektywne sposoby realizacji (plany zapytań).