Оператор ANY
Оператор IN
Однострочные и многострочные подзапросы
Все приведенные выше примеры подзапросов являлись однострочными подзапросами. Такие подзапросы возвращают в качестве результата только одну строку. В однострочных подзапросах обычно используют однострочные операторы сравнения (=, >, < и т.п.), сравнивающие выражение с одиночным значением, или логические операторы.
Может возникнуть ситуация, когда подзапрос возвращает более одной строки. Такой подзапрос называется многострочным и не может быть обработан с помощью однострочного оператора сравнения, необходимо использовать многострочный оператор сравнения, такой как IN, ANY или ALL.
Пример: Найти всех служащих, нанятых на работу в тот же квартал года, что и служащий по фамилии Patel:
SELECT last_name, start_date
FROM s_emp
WHERE TO_CHAR(start_date, 'qyyyy') = (SELECT TO_CHAR(start_date,'qyyyy')
FROM s_emp
WHERE last_name='Patel');
Результат:
WHERE TO_CHAR(start_date, 'qyyyy') = (SELECT TO_CHAR(start_date,'qyyyy')
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
Ошибка произошла из-за того, что был использован однострочный оператор сравнения (=) в многострочном подзапросе. Дело в том, что среди служащих несколько человек носят фамилию Patel, соответственно подзапрос вернул несколько значений start_date. С помощью однострочного оператора сравнения нельзя сравнить одно значение из внешнего запроса с несколькими значениями, возвращаемыми подзапросом. Такого рода ошибка – наиболее часто встречающаяся ошибка в подзапросах. Следует заменить однострочный оператор сравнения (=) на многострочный (IN), а также подкорректировать формулировку задачи:
Найти всех служащих, нанятых на работу в тот же квартал года, что и какой-либо из служащих по фамилии Patel:
SELECT last_name, start_date
FROM s_emp
WHERE TO_CHAR(start_date, 'qyyyy') IN (SELECT TO_CHAR(start_date,'qyyyy')
FROM s_emp
WHERE last_name='Patel');
Результат:
LAST_NAME START_DA
------------- --------
Patel 06.08.91
Newman 21.07.91
Chang 30.11.90
Patel 17.10.90
Оператор IN используется для проверки, входит ли значение выражения в некоторый список. Этот список может быть как фиксированным, так и возвращаться подзапросом.
Пример: Найти служащих, у которых зарплата равна минимальной зарплате в каком-либо из отделов:
SELECT last_name
FROM s_emp
WHERE salary IN (SELECT MIN(salary)
FROM s_emp
GROUP BY dept_id);
Результат:
LAST_NAME
------------------
Newman
Patel
Patel
Chang
Dancs
Smith
Nagayama
Maduro
…
15 rows selected.
Оператор ANY используется для сравнения выражения с любым значением из списка. Этот список может быть как фиксированным, так и возвращаться подзапросом. ANY используется в сочетании со знаками равенства < (или <=), > (или >=), =.
<ANY (<=ANY) означает, что выражение должно быть меньше (меньше или равно) какого-либо значения из списка. Это условие выполняется в случае, если выражение меньше (меньше или равно) максимального значения из списка.
>ANY (>=ANY) означает, что выражение должно быть больше (больше или равно) какого-либо значения из списка. Это условие выполняется в случае, если выражение больше (больше или равно) минимального значения из списка.
=ANY – эквивалент IN.
Пример: Найти служащих, фамилии которых по алфавиту раньше, чем фамилия любого из служащих в должности ‘Warehouse Manager’:
SELECT last_name
FROM s_emp
WHERE last_name <ANY (SELECT last_name
FROM s_emp
WHERE title=’Warehouse Manager’);
Результат:
LAST_NAME
------------------
Ngao
Nagayama
Quick-To-See
Ropeburn
Menchu
Biri
Catchpole
Havel
…
23 rows selected.
Как уже было сказано, условие с оператором <ANY выполняется в случае, если выражение меньше максимального значения из списка. Таким образом, приведенный выше запрос эквивалентен следующему:
SELECT last_name
FROM s_emp
WHERE last_name < (SELECT MAX(last_name)
FROM s_emp
WHERE title='Warehouse Manager');