Многостолбцовые подзапросы

Квантифицированные подзапросы

Квантифицированными называют подзапросы, созданные с использованием кванторов: операторов ALL и EXISTS. Оператор ALL уже был описан ранее в этой лекции. Квантор EXISTS используется для определения того, вернул ли подзапрос хоть одну строку. Условие с EXISTS является истинным в случае, если подзапрос вернул непустое множество строк.

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

SELECT last_name

FROM s_emp one

WHERE EXISTS (SELECT *

FROM s_emp

WHERE salary=one.salary
AND last_name=’Ngao’);

Результат:

LAST_NAME

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

Ngao

Quick-To-See

Dumas

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

Пример: Получить названия регионов, в которых нет ни одного отдела:

SELECT name

FROM s_region

WHERE NOT EXISTS (SELECT id

FROM s_dept

WHERE region_id=s_region.id);

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

Результат:

no rows selected –Таких регионов нет

Многостолбцовые подзапросы используются, когда требуется провести сравнение двух или более столбцов. В этом случае перед оператором сравнения в предложении WHERE указывается список сравниваемых столбцов (или выражений) через запятую в скобках. Такое же количество столбцов (или выражений) должен возвращать подзапрос. При этом, чтобы строка внешнего запроса попала в выборку, необходимо, чтобы строка, возвращаемая подзапросом полностью совпадала со строкой перед оператором сравнения в предложении WHERE.

Пример: Получить номера служащих, у которых должность и год приема на работу совпадают как с должностью, так и с годом приема на работу какого-либо служащего отдела №34:

SELECT id

FROM s_emp

WHERE (title, TO_CHAR(start_date,'yyyy')) IN

(SELECT title, TO_CHAR(start_date,'yyyy')

FROM s_emp

WHERE dept_id=34)

AND dept_id<>34;

-- Сами служащие отдела №34 исключаются из запроса (dept_id<>34), так как -- очевидно, что они удовлетворяют условиям запроса.

Результат:

-----

Если требуется сравнение нескольких столбцов, но нет необходимости, чтобы равенство выполнялось для всей строки в целом, необходимо использовать предложение WHERE с несколькими условиями.

Пример: Получить номера служащих, у которых должность совпадает с должностью какого-либо служащего отдела №34 и год приема на работу совпадает с годом приема на работу какого-либо служащего отдела №34. В отличие от предыдущего примера это может быть не один и тот же служащий:

SELECT id

FROM s_emp

WHERE title IN (SELECT title

FROM s_emp

WHERE dept_id=34)

AND TO_CHAR(start_date,'yyyy') IN (SELECT TO_CHAR(start_date,'yyyy')

FROM s_emp

WHERE dept_id=34)

AND dept_id<>34;

Результат:

-----