Коррелированные и некоррелированные подзапросы

Оператор ALL

Оператор ALL используется для сравнения выражения с каждым значением из списка. Этот список может быть как фиксированным, так и возвращаться подзапросом. ALL используется в сочетании со знаками равенства < (или <=), > (или >=), =.

<ALL (<=ALL) означает, что выражение должно быть меньше (меньше или равно) каждого значения из списка. Это условие выполняется в случае, если выражение меньше (меньше или равно) минимального значения из списка.

>ALL (>=ALL) означает, что выражение должно быть больше (больше или равно) каждого значения из списка. Это условие выполняется в случае, если выражение больше (больше или равно) максимального значения из списка.

=ALL – означает, что выражение должно быть равно каждому значению из списка. (Конечно, это выполнится только в случае, если в списке одно значение или несколько одинаковых значений.)

Пример: Найти служащих, которые были приняты на работу раньше всех служащих в должности ‘Warehouse Manager’:

SELECT last_name

FROM s_emp

WHERE start_date <ALL(SELECT start_date

FROM s_emp

WHERE title=’Warehouse Manager’);

Результат:

LAST_NAME

----------

Velasquez

Ngao

Ropeburn

Smith

Как уже было сказано, условие с оператором <ALL выполняется в случае, если выражение меньше минимального значения из списка. Таким образом, приведенный выше запрос эквивалентен следующему:

SELECT last_name

FROM s_emp

WHERE start_date < (SELECT MIN(start_date)

FROM s_emp

WHERE title='Warehouse Manager');

Помимо подразделения подзапросов на однострочные и многострочные, существует разделение подзапросов на коррелированные и некоррелированные. Данные, выбираемые некоррелированным подзапросом, никак не зависят от внешнего запроса. Во всех приведенных ранее в лекции примерах использовались некоррелированные подзапросы. Коррелированный подзапрос содержит ссылку на данные внешнего запроса и выполняется не один раз, а для каждой строки внешнего запроса.

Пример: Найти всех служащих, у которых зарплата такая же, как у служащего по фамилии Ngao:

SELECT last_name

FROM s_emp one

WHERE 'Ngao' IN (SELECT last_name

FROM s_emp

WHERE salary=one.salary);

Здесь использован коррелированный многострочный подзапрос, рассмотрим, как он работает. Как уже говорилось, подзапрос выполняется для каждой строки внешнего запроса. То есть для каждой фамилии, которую выберет внешний запрос, подзапрос выбирает служащих с такой же зарплатой, что и у служащего, выбранного внешним запросом. Если среди выбранных подзапросом служащих есть служащий по фамилии Ngao, значит служащий, выбранный внешним запросом, удовлетворяет условиям задачи и должен попасть в результирующую выборку.

Результат:

LAST_NAME

--------------

Ngao

Quick-To-See

Dumas

Любой некоррелированный подзапрос может быть переписан как коррелированный, но есть задачи, которые можно решить только с помощью коррелированных подзапросов.

Пример: Найти всех служащих, у которых зарплата превышает среднюю зарплату в их отделе:

SELECT last_name, salary

FROM s_emp x

WHERE salary > (SELECT AVG(salary)

FROM s_emp

WHERE dept_id=x.dept_id);

Здесь использован коррелированный однострочный подзапрос. Для каждого служащего, выбираемого внешним запросом, подзапрос вычисляет среднюю зарплату в отделе, в котором работает этот служащий. Если зарплата служащего, выбранного внешним запросом, превышает среднюю зарплату, выбранную подзапросом, служащий попадает в выборку.

Результат:

LAST_NAME SALARY

------------- ---------

Velasquez 2500

Ngao 1450

Menchu 1250

Biri 1100

Catchpole 1300

Havel 1307

Nguyen 1525

Maduro 1400

Nozaki 1200