Использование EXISTS с соотнесенными подзапросами
В соотнесенном подзапросе предложение EXISTS оценивается отдельно для каждой строки таблицы, имя которой указано во внешнем запросе, точно также как и другие операторы предиката, когда вы используете соотнесенный подзапрос. Это дает возможность использовать EXISTS как верный предикат, который генерирует различные ответы для каждой строки таблицы, указанной в основном запросе. Следовательно, информация из внутреннего запроса будет сохранена, если выведена непосредственно, когда вы используете EXISTS таким способом. Например, мы можем вывести продавцов, которые имеют многочисленных заказчиков (вывод для этого запроса показывается в Рисунке 12.2):
SELECT DISTINCT snum
FROM Customers outer
WHERE EXISTS (SELECT *
FROM Customers inner
WHERE inner.snum = outer.snum AND
inner.cnum <> outer.cnum);
============= SQL Execution Log ==============
| SELECT DISTINCT cnum |
| FROM Customers outer |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE inner.snum = outer.snum |
| AND inner.cnum <> outer.cnum); |
|===============================================|
| cnum |
| ----- |
| 1001 |
| 1002 |
===============================================
Рисунок 12.2. Использование EXISTS с соотнесенным подзапросом.
Для каждой строки-кандидата внешнего запроса (представляющей заказчика, проверяемого в настоящее время), внутренний запрос находит строки, которые совпадают со значением поля snum (которое имел продавец), но не со значением поля cnum (соответствующего другим заказчикам). Если любые такие строки найдены внутренним запросом, это означает, что имеются два разных заказчика, обслуживаемых текущим продавцом (т.е. продавцом заказчика в текущей строке-кандидате из внешнего запроса). Поэтому предикат EXISTS верен для текущей строки, и номер продавца — поле (snum) таблицы, указанной во внешнем запросе, будет выведен. Если был DISTINCT не указан, каждый из этих продавцов будет выбран один раз для каждого заказчика, к которому он назначен.
Комбинация из EXISTS и объединения
Однако для нас может быть полезнее вывести больше информации об этих продавцах, а не только их номера. Мы можем сделать это, объединив таблицу Заказчиков с таблицей Продавцов (вывод для запроса показывается в Рисунке 12.3):
SELECT DISTINCT first.snum, sname, first.city
FROM Salespeople first, Customers second
WHERE EXISTS (SELECT *
FROM Customers third
WHERE second.snum = third.snum AND
second.cnum <> third.cnum) AND
first.snum = second.snum;
============= SQL Execution Log ==============
| SELECT DISTINCT first.snum, sname, first.city |
| FROM Salespeople first, Customers second |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers third |
| WHERE second.snum = third.snum |
| AND second.cnum <> third.cnum) |
| AND first.snum = second.snum; |
|===============================================|
| cnum cname city |
| ----- -------- ---- |
| 1001 Peel London |
| 1002 Serres San Jose |
===============================================
Рисунок 12.3. Комбинация EXISTS с объединением.
Внутренний запрос здесь, как и в предыдущем варианте, фактически сообщает, что псевдоним был изменен. Внешний запрос — это объединение таблицы Продавцов с таблицей Заказчиков, наподобие того, что мы видели прежде. Новое предложение основного предиката (AND first.snum = second.snum) естественно оценивается на том же самом уровне, что и предложение EXISTS. Это — функциональный предикат самого объединения, сравнивающий две таблицы из внешнего запроса в терминах поля snum, которое являются для них общим. Из-за Булева оператора AND, оба условия основного предиката должны быть верны в Заказе для верного предиката. Следовательно, результаты подзапроса имеют смысл только в тех случаях, когда вторая часть запроса верна, а объединение выполнимо. Таким образом, комбинация объединения и подзапроса может стать очень мощным способом обработки данных.
Использование NOT EXISTS
Предыдущий пример дал понять, что EXISTS может работать в комбинации с операторами Буля. Конечно, то, что является самым простым способом для использования и вероятно наиболее часто используется с EXISTS — это оператор NOT. Один из способов, которым мы могли бы найти всех продавцов только с одним заказчиком, будет состоять в том, чтобы инвертировать наш предыдущий пример. (Вывод для этого запроса показывается в Рисунке 12.4.)
SELECT DISTINCT snum
FROM Customers outer
WHERE NOT EXISTS (SELECT *
FROM Customers inner
WHERE inner.snum = outer.snum AND
inner.cnum <> outer.cnum);
============== SQL Execution Log =============
| SELECT DISTINCT snum |
| FROM Salespeople outer |
| WHERE NOT EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE inner.snum = outer.snum |
| AND inner.cnum <> outer.cnum); |
|===============================================|
| cnum |
| ----- |
| 1003 |
| 1004 |
| 1007 |
===============================================
Рисунок 12.4. Использование NOT EXISTS.
EXISTS и агрегаты
Одна вещь, которую EXISTS не может сделать — взять функцию агрегата в подзапросе. Это имеет значение. Если функция агрегата находит любые строки для операций с ними, EXISTS верен, не взирая на то, что это — значение функции; если же агрегатная функция не находит никаких строк, EXISTS неправилен.
Попытка использовать агрегаты с EXISTS таким способом, вероятно, покажет, что проблема неверно решалась от начала до конца.
Конечно, подзапрос в предикате EXISTS может также использовать один или более из его собственных подзапросов. Они могут иметь любой из различных типов которые мы видели (или который мы будем видеть). Такие подзапросы, и любые другие в них, позволяют использовать агрегаты, если нет другой причины по которой они не могут быть использованы. Следующий раздел приводит этому пример.
В любом случае, вы можете получить тот же самый результат более легко, выбрав поле которое вы использовали в агрегатной функции, вместо использования самой этой функции. Другими словами, предикат — EXISTS (SELECT COUNT (DISTINCT sname) FROM Salespeople) — будет эквивалентен EXISTS (SELECT sname FROM Salespeople) который был позволен выше.