Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Использование EXJSTS со связанными подза11росами

(SELECT *

FROM Customers

WHERE city = ·san Jose');

Внутренний запрос выбрал все данные обо всех покупателях из San Jose. Оператор EXISTS внешнего предиката отметил, что подзапрос генерирует выходные данные, и поскольку выражение EXISTS является единственным в этом предикате, он принима­ ет значение "истинно". Здесь подзапрос (не являющийся связанным) выполняется

только один раз для всего внешнего запроса и, следовательно, имеет единственное

значение для всех случаев. Поскольку EXISTS в данном примере делает предикат ис­

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

специфической информации не стоит.

Выбор столбцов с помощью EXISTS

В приведенном примере EXISTS выбирает один столбец, в отличие от с звездочки, выбирающей все столбцы, что отличается от рассмотренных ранее подзапросов, где извлекался единственный столбец. Однако несущественно, сколько столбцов извлека­ ет EXISTS, поскольку он вообще не применяет полученных значений, а лишь фикси­

рует наличие выходных данных подзапроса.

Использование EXISTS со связанными подзапросами

При применении связанных подзапросов предложение EXISTS, как и другие преди­ катные операторы, оценивается отдельно для каждой строки таблицы, на которую есть ссьmка во внешнем запросе. Это позволяет использовать EXISTS как правильный пре­ дикат, генерирующий различные ответы для каждой строки таблицы, на которую есть ссылка в основном запросе. Следовательно, при таком способе применения EXIST ин­ формация из внутреннего запроса сохраняется, если непосредственно не выводится. Например, можно сделать запрос на поиск тех продавцов, которые имеют нескольких покупателей (выходные данные такого запроса представлены на рис. 12.2):

SELECT DISTINCT snum FROM Customers outer WHERE EXISTS

(SELECT *

FROM Customers inner

WHERE inner.snum = outer.snum AND inner.cnum <> outer.cnum);

119

Глава 12. Использование оператора EXISTS

Рис. 11.2. Использование оператора EXISTS со связанными подзапросами

Для каждой строки-кандидата внешнего запроса (представляющей рассматривае­

мого в настоящий момент покупателя), внутренний запрос находит строки, имеющие

соответствующее значение snum (имеет того же продавца), но не значение cnum (соот­ ветствует другому покупателю). Если строка, удовлетворяющая подобному критерию, найдена во внутреннем запросе, это означает, что различные покупатели обслужива­ ются данным продавцом (т.е. продавцом, обслуживающим покупателя, указанного в строке-кандидате внешнего запроса). Следовательно, предикат EXISTS истинен для текущей строки, и номер поля продавца (snum) из таблицы внешнего запроса включа­

ется в состав выходных данных. Если бы не был задан DISТINCT, каждый из таких

продавцов выбирался бы один раз для каждого покупателя, которого он обслуживает.

Комбинирование EXISTS и соединений

Иногда кроме номера требуется получить о каждом продавце больше информации. Это можно сделать соединением таблиц Customers и Salespeople (выходные данные представлены на рис. 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

120

Использование EXJSTS со связанными подзапросами

AND second.cnum <> third.cnum)

AND first.snum = second.snum;

Внутренний запрос тот же, что и в предыдущем примере, изменены только алиасы.

Внешний запрос является соединением таблиц Salespeople и Customers. Новое предло­ жение основного предиката (AND first.snum = second.snum) оценивается на том же уровне, что и предложение EXISTS. Это функциональный предикат самого соедине­ ния, сравнивающий две таблицы из внешнего запроса в терминах общего для них поля snum. Поскольку используется булев оператор AND, оба условия, сформулированные в предикатах основного запроса, должны быть истинными для истинности этого пре­ диката. Следовательно, результаты подзапроса действуют в случаях, когда вторая часть запроса истинна и соединение выполняется. Комбинирование соединений и подзапросов указанным способом является весьма эффективным методом обработки

данных.

Рис. 12.3. Комбинирование EXISTS с JOIN (соединением)

Использование NOT EXISTS

Из предыдущего примера ясно, что EXISTS можно комбинировать с булевыми операторами. С EXISTS легче всего применять - и чаще всего применяется - опера­ тор NOT. Один из способов поиска всех продавцов, имеющих только одного покупате­ ля, - это поставить NOT перед EXISTS в предыдущем примере (см. рисунок 12.2) (выходные данные для запроса представлены на рис. 12.4):

SELECT DISTINCT snum

FROM Customers outer

121

Глава 12. Исllользование 01repamopa EXISTS

WHERE NOT EXISTS

(SELECT •

FROM Customers inner

WHERE inner.snum = outer.snum

AND inner.cnum <> outer.cnum);

Рис. 12.4. Использование EXISTS с NOT

EXISTS и агрегаты

EXISTS не может использовать агрегатные функции в своем подзапросе. Это есте­ ственно. Если функция агрегирования находит строки для работы с ними, то EXISTS принимает значение "истина", и ему безразлично реальное значение функции; если

функция не находит никаких строк, то значение EXISTS - "ложь". Попытка исполь­

зовать функции агрегирования с EXISTS подобным образом свидете.1ьствует о том, что проблема не была правильно понята.

Подзапрос для предиката EXISTS тоже может иметь в своем составе один или не­

сколько подзапросов любого типа. Эти подзапросы, как и любые другие, входящие в

их состав, могут использовать агрегатные функции, если только не существует каких­ либо иных причин, по которым зто невозможно. Пример, иллюстрирующий такую си­

туацию, приведен в следующем разделе.

В любом случае можно получить тот же результат проще - выбрать поле, которое используется в агрегатной функции, вместо применения самой функции. Другими

словами, предикат EXISTS (SELECT COUNT (DISТINCT sname) FROM sa\espeop\e) эквивалентен предикату EXISTS (SELECT sname FROM Sa\espeop\e), причем первый

из них тоже допустим.

122

Ис11олыование EXJSTS со связаиными подзапросами

Примеры более сложных подзапросов

Возможности применения запросов весьма разнообразны. В одном запросе можно разместить один или несколько запросов, и даже один внутри другого. Перед вами

пример, в котором извлекаются строки для всех продавцов, имеющих покупателей,

сделавших более одного заказа. Решение этой проблемы представляет интерес с точки зрения демонстрации преимуществ логики SQL. Нужную информацию можно полу­ чить путем связывания всех трех рассматриваемых нами в примерах таблиц:

SELECT •

FROM Salespeople first

WHERE EXISTS

(SELECT •

FROM Customers second

WHERE first.snum = second.snum

AND 1 <

(SELECT COUNT(•)

FROM Orders

WHERE Orders.cnum = second.cnum));

Выходные данные представлены на рис. 12.5.

Рис. 12.5. Использование EXISTS в сложном подзапросе

123