Podzapytania do rozwiązywania zapytań
Podzapytanie najlepiej definiować jako zapytanie w zapytaniu. Podzapytania umożliwiają pisanie zapytań, które wybierają wiersze danych dla kryteriów, które są faktycznie opracowywane, gdy zapytanie jest wykonywane w czasie wykonywania. Bardziej formalnie jest to użycie instrukcji SELECT wewnątrz jednej z klauzul innej instrukcji SELECT. W rzeczywistości podzapytanie może być zawarte w innym podzapytaniu, które znajduje się w innym podzapytaniu i tak dalej. Podzapytanie można również zagnieżdżać w instrukcjach INSERT, UPDATE i DELETE. Podzapytania muszą być zawarte w nawiasach.
Podzapytanie może być użyte w dowolnym miejscu, w którym wyrażenie jest dozwolone, pod warunkiem że zwraca pojedynczą wartość. Oznacza to, że podzapytanie, które zwraca pojedynczą wartość, może być również wymienione jako obiekt w listingu klauzuli FROM. Nazywa się to widokiem wbudowanym, ponieważ gdy podzapytanie jest używane jako część klauzuli FROM, jest traktowane jak wirtualna tabela lub widok. Podzapytanie można umieścić w klauzuli FROM, WHERE lub w klauzuli HAVING głównego zapytania.
Oracle zezwala na maksymalne zagnieżdżenie 255 poziomów podzapytań w klauzuli WHERE. Nie ma limitu zagnieżdżania podzapytań wyrażonych w klauzuli FROM. W praktyce limit 255 poziomów nie jest w rzeczywistości limitem, ponieważ rzadko można napotkać podzapytania zagnieżdżone poza trzema lub czterema poziomami.
Instrukcja SELECT podzapytania jest bardzo podobna do instrukcji SELECT używanej do rozpoczęcia zwykłego lub zewnętrznego zapytania. Pełna składnia podzapytania jest następująca:
( SELECT [DISTINCT] subquery_select_parameter
FROM {table_name | view_name}
{table_name | view_name} ...
[WHERE search_conditions]
[GROUP BY column_name [,column_name ] ...]
[HAVING search_conditions] )Typy podzapytań
Single Row Sub Query: Zapytanie podrzędne, które zwraca dane wyjściowe w jednym wierszu. Oznaczają użycie jednorzędowych operatorów porównania, gdy są używane w warunkach WHERE.
Multiple row sub query: Zapytanie podrzędne zwracające dane wyjściowe z wieloma wierszami. Korzystają z operatorów porównania wielu wierszy, takich jak IN, ANY, ALL. Mogą również istnieć zapytania podrzędne zwracające wiele kolumn.
Correlated Sub Query: Skorelowane podzapytania zależą od danych dostarczonych przez zapytanie zewnętrzne. Ten typ podzapytania obejmuje również podzapytania, które używają operatora EXISTS do testowania istnienia wierszy danych spełniających określone kryteria.
Zapytanie podrzędne w jednym wierszu
Podzapytanie jednowierszowe jest używane, gdy wyniki zapytania zewnętrznego są oparte na pojedynczej, nieznanej wartości. Chociaż ten typ zapytania jest formalnie nazywany „pojedynczym wierszem”, nazwa sugeruje, że zapytanie zwraca wiele kolumn, ale tylko jeden wiersz wyników. Jednak podzapytanie jednowierszowe może zwrócić tylko jeden wiersz wyników zawierający tylko jedną kolumnę do zapytania zewnętrznego.
W poniższym zapytaniu SELECT wewnętrzny SQL zwraca tylko jeden wiersz, czyli minimalne wynagrodzenie dla firmy. Ta z kolei używa tej wartości do porównania wynagrodzeń wszystkich pracowników i wyświetla tylko tych, których pensja jest równa pensji minimalnej.
SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary)
FROM employees);Klauzula HAVING jest używana, gdy wyniki grupowe zapytania muszą być ograniczone na podstawie jakiegoś warunku. Jeśli wynik podzapytania trzeba porównać z funkcją grupy, należy zagnieździć zapytanie wewnętrzne w klauzuli HAVING zapytania zewnętrznego.
SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary) < (SELECT AVG (salary) FROM employees)Wielowierszowe zapytanie podrzędne
Podzapytania wielowierszowe to zapytania zagnieżdżone, które mogą zwracać więcej niż jeden wiersz wyników do zapytania nadrzędnego. Podzapytania wielowierszowe są najczęściej używane w klauzulach WHERE i HAVING. Ponieważ zwraca wiele wierszy, musi być obsługiwany przez ustawione operatory porównania (IN, ALL, ANY). Podczas gdy operator IN ma to samo znaczenie, co omówiono we wcześniejszym rozdziale, operator ANY porównuje określoną wartość z każdą wartością zwróconą przez zapytanie podrzędne, podczas gdy ALL porównuje wartość z każdą wartością zwróconą przez zapytanie podrzędne.
Poniższe zapytanie pokazuje błąd, gdy jednorzędowe zapytanie podrzędne zwraca wiele wierszy.
SELECT first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE LOCATION_ID = 100)
department_id = (select
*
ERROR at line 4:
ORA-01427: single-row subquery returns more than one rowWykorzystanie operatorów wielu wierszy
[> ALL] Więcej niż najwyższa wartość zwrócona przez podzapytanie
[<ALL] Mniejsza niż najniższa wartość zwrócona przez podzapytanie
[<ANY] Mniejsza niż najwyższa wartość zwrócona przez podzapytanie
[> ANY] Więcej niż najniższa wartość zwrócona przez podzapytanie
[= ANY] Równa się dowolnej wartości zwróconej przez podzapytanie (tak samo jak IN)
Powyższy SQL można przepisać za pomocą operatora IN, jak poniżej.
SELECT first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE LOCATION_ID = 100)Zauważ, że w powyższym zapytaniu IN dopasowuje identyfikatory działów zwrócone z zapytania podrzędnego, porównuje je z tymi w zapytaniu głównym i zwraca nazwisko pracownika, który spełnia warunek.
Łączenie byłoby lepszym rozwiązaniem dla powyższego zapytania, ale dla ilustracji zastosowano w nim zapytanie podrzędne.
Skorelowane zapytanie podrzędne
W przeciwieństwie do zwykłego podzapytania, w którym zapytanie zewnętrzne zależy od wartości dostarczonych przez zapytanie wewnętrzne, podzapytanie skorelowane to takie, w którym zapytanie wewnętrzne zależy od wartości dostarczonych przez zapytanie zewnętrzne. Oznacza to, że w skorelowanym podzapytaniu zapytanie wewnętrzne jest wykonywane wielokrotnie, raz dla każdego wiersza, który może zostać wybrany przez zapytanie zewnętrzne.
Skorelowane podzapytania mogą generować tabele wyników, które odpowiadają na złożone pytania dotyczące zarządzania.
Rozważ poniższe zapytanie SELECT. W przeciwieństwie do wcześniej rozważanych podzapytań podzapytanie w tej instrukcji SELECT nie może zostać rozwiązane niezależnie od zapytania głównego. Zwróć uwagę, że zapytanie zewnętrzne określa, że wiersze są wybierane z tabeli pracowników o aliasie e1. Zapytanie wewnętrzne porównuje kolumnę numeru działu pracownika (DepartmentNumber) tabeli pracowników z aliasem e2 z tą samą kolumną dla tabeli aliasów o nazwie e1.
SELECT EMPLOYEE_ID, salary, department_id
FROM employees E
WHERE salary > (SELECT AVG(salary)
FROM EMP T
WHERE E.department_id = T.department_id)Zapytanie podrzędne z wieloma kolumnami
Podzapytanie z wieloma kolumnami zwraca więcej niż jedną kolumnę do zapytania zewnętrznego i może być wymienione w klauzuli FROM, WHERE lub HAVING zapytania zewnętrznego. Na przykład poniższe zapytanie pokazuje historyczne dane pracownika dla tych, których obecne wynagrodzenie mieści się w przedziale 1000 i 2000 i pracują w dziale 10 lub 20.
SELECT first_name, job_id, salary
FROM emp_history
WHERE (salary, department_id) in (SELECT salary, department_id
FROM employees
WHERE salary BETWEEN 1000 and 2000
AND department_id BETWEEN 10 and 20)
ORDER BY first_name;Gdy w klauzuli FROM zapytania zewnętrznego używane jest podzapytanie z wieloma kolumnami, tworzy ono tymczasową tabelę, do której mogą się odwoływać inne klauzule zapytania zewnętrznego. Ta tymczasowa tabela jest bardziej formalnie nazywana widokiem wbudowanym. Wyniki podzapytania są traktowane jak każda inna tabela w klauzuli FROM. Jeśli tymczasowa tabela zawiera zgrupowane dane, zgrupowane podzbiory są traktowane jako oddzielne wiersze danych w tabeli. Rozważ klauzulę FROM w poniższym zapytaniu. Widok wbudowany utworzony przez podzapytanie jest źródłem danych dla zapytania głównego.
SELECT *
FROM (SELECT salary, department_id
FROM employees
WHERE salary BETWEEN 1000 and 2000);