podzapytanie skorelowane *

Obrzydliwa teoria, którą starałem się przełożyć na język coś bardziej ludzki:

W podzapytaniu nieskorelowanym najpierw wykonuje się podzapytanie, a potem jego wynik jest używany jako warunek wyboru wierszy przez zapytanie nadrzędne.

Natomiast podzapytanie skorelowane, a właściwie jego zapytanie nadrzędne bierze wiersz po wierszu i porównuje jego zawartość (przez warunek porównania określony w klauzuli WHERE) z wynikiem podzapytania, które jest wyliczane> od nowa dla każdego kolejnego wiersza, >korzystając z wybranej wartości występującej w tym właśnie wierszu.

 

Zbyt pokręcone? – no to krok po kroku na przykładzie:

Treść zadania: Wyświetl pracowników, którzy zarabiają więcej, niż wynosi średnia płaca w ich departamencie.

 

SELECT EMPLOYEE_ID, LAST_NAME, SALARY, DEPARTMENT_ID

FROM EMPLOYEES NAD

WHERE SALARY>

(SELECT AVG(SALARY)

FROM EMPLOYEES POD

WHERE POD.DEPARTMENT_ID=NAD.DEPARTMENT_ID);

 

Wynik zwróci 38 wierszy, oto pierwsze z nich:

 

Zacznijmy sekcję zwłok SQL-owej komendy:

 

Najpierw przeprowadzamy selekcję, które dane z wiersza nas będą interesować:

SELECT EMPLOYEE_ID, LAST_NAME, SALARY, DEPARTMENT_ID

                                    (Nr_Pracownika, Nazwisko, Płaca, Nr_Departamentu)

 

We „FROM EMPLOYEES NAD” wskazujemy, że te atrybuty weźmiemy z tabeli Pracownicy - EMPLOYEES. W podzapytaniu skorelowanym musimy skorzystać z aliasu NAD, czyli formy wskazania, że chodzi nam dokładnie o tabelę z pytania NADrzędnego. Alias to bynajmniej nie as z rękawa Ali, tylko inna nazwa tego samego, takie pseudo, o!

Alias POD dla tabeli z podzapytania jest w sumie zbędny, ale dałem go dla zwiększenia przejrzystości.

 

Ten alias NAD w PODzapytaniu w klauzuli WHERE wskaże, ze wartość POD.DEPARTMENT_ID PODzapytanie ma sobie pobrać z wiersza, który akurat wzięło pod lupę zapytanie NADrzędne.

Czyli i my bierzemy pod lupę pierwszy dostępny wiersz (krotkę):

(100, ‘King ‘,24000,90) w kolejności  (EMPLOYEE_ID, LAST_NAME, SALARY, DEPARTMENT_ID)

i pytamy, czy SALARY (tu 24000) jest większe od? - PODzapytanie ma wyliczyć średnią płace dla tego samego departamentu , co w wierszu, który akurat jest pod lupą pytania NADrzędnego czyli: 90.  PODzapytanie da więc dla tego wiersza taki sam wynik, jak byśmy wpisali następujące zapytanie do Oracle’a:

 

SELECT AVG(SALARY)

FROM EMPLOYEES

WHERE DEPARTMENT_ID=90;

 

Wynik ten 19333,33  zwróci  do warunku porównania: WHERE SALARY>(podzapytanie) czyli 24000>19333,33,  co jest oczywiście prawdą, więc ten wiersz (krotka) zostanie wyświetlony przez zapytanie nadrzędne.

 

Potem zapytanie NADrzędne weźmie kolejny wiersz (101, ‘Kochhar’,17000,90) i znów przejdzie do PODzapytania, gdzie od nowa wyliczy średnią płacę dla zespołu znów 90, bo akurat występuje w tej krotce, porówna 17000,90>19333,33, co jest fałszem, więc ten wiersz (krotka) zostanie odrzucony.

 

I tak wiersz po wierszu:

1. Zapytanie Nadrzędne weźmie pierwszy / kolejny wiersz;

2. wyliczy wartość PODzapytania korzystając z danych z wiersza z punktu 1 (a właściwie z tej danej, która pojawi się w  warunku WHERE PODzapytania);

3. porówna z lewą stroną warunku w klauzuli  WHERE zapytania NADrzędnego z wynikiem uzyskanym w punkcie 2;

4. jeśli wynik będzie prawdziwy, to wiersz ten zostanie wyświetlony,  jeśli nie,  to nie;

5. wróci do punktu 1. 

 

Teraz trochę trudniejsza bajka :-)

Treść zadanka: Wyświetl informacje o 5 najlepiej zarabiających pracownikach.

SELECT EMPLOYEE_ID, LAST_NAME, SALARY

FROM EMPLOYEES NAD

WHERE 5>

(SELECT COUNT(*) FROM EMPLOYEES POD

WHERE POD.SALARY>NAD.SALARY);

 

Jak to działa: COUNT(*) zlicza, ile razy pensja w PODzapytaniu jest >od tej w danym wierszu pobranym z NAD, jeżeli zliczeń jest mniej niż 5, wyśwetla ten wiersz .

 

Dla 1-wszego wiersza - (100, ‘King ‘,24000,90):

SELECT COUNT(*)

FROM EMPLOYEES POD

WHERE POD.SALARY>24000,

no to podzapytanie bierze teraz wszystkie wiersze z POD i porównuje wartość płacy w podzapytaniu z 24000, jeżeli jest większa podbija licznik o 1.

Dla 24000 mamy 0 zliczeń, więc warunek porównania 5>0 da wartość TRUE i wiersz zostanie wyświetlony.

 

Teraz NADzapytanie weźmie 2-gi wiersz. Pan Kochhar zarabia 17000, więc PODzapytanie wstawia 17000 za NAD.SALARY i zaczyna zliczanie od nowa :

SELECT COUNT(*)

FROM EMPLOYEES POD

WHERE POD.SALARY>17000,

co da wynik 1, ale w dalszym ciągu jest to wynik mniejszy od 5, więc wyświetli nam ten wiersz, itd.

*tekst autorski - publikowanie w części lub w całości dozwolone pod rygorem podania źródła oraz imienia i nazwiska autora.