Мартин Грубер - Понимание SQL
(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 );
EXISTS И АГРЕГАТЫ
Одна вещь которую EXISTS не может сделать - взять функцию агрегата в подзапросе. Это имеет значение. Если функция агрегата находит любые строки для операций с ними, EXISTS верен, не взирая на то, что это - значение функции ; если же агрегатная функция не находит никаких строк, EXISTS неправилен.
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: Использование EXISTS с NOT
Попытка использовать агрегаты с EXISTS таким способом, вероятно покажет что проблема неверно решалась от начала до конца.
Конечно, подзапрос в предикате EXISTS может также использовать один или более из его собственных подзапросов. Они могут иметь любой из различных типов которые мы видели (или который мы будем видеть). Такие подзапросы, и любые другие в них, позволяют использовать агрегаты, если нет другой причины по которой они не могут быть использованы.
Следующий раздел приводит этому пример.
В любом случае, вы можете получить тот же самый результат более легко, выбрав поле которое вы использовали в агрегатной функции, вместо использования самой этой функции. Другими словами, предикат - EXISTS (SELECT COUNT (DISTINCT sname) FROM Salespeople) - будет эквивалентен - EXISTS (SELECT sname FROM Salespeople) который был позволен выше.
БОЛЕЕ УДАЧНЫЙ ПРИМЕР ПОДЗАПРОСАВозможные прикладные программы подзапросов могут становиться многократно вкладываемыми. Вы можете вкладывать их два или более в одиночный запрос, и даже один внутрь другого. Так как можно рассмотреть небольшой кусок чтобы получить всю картину работаты этой команды, вы можете воспользоваться способом в SQL, который может принимать различные команды из большинства других языков.
Имеется запрос который извлекает строки всех продавцов которые имеют заказчиков с больше чем одним текущим порядком. Это не обязательно самое простое решение этой проблемы, но оно предназначено скорее показать улучшеную логику 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.
SQL Execution Log
FROM Salespeople first WHERE EXISTS
(SELECT * FROM Customers second
WHERE first.snum=second.snum
AND 1 < (SELECT CONT (*) FROM Orders
WHERE Orders.cnum=second.cnum));
cnum
cname
city
comm
1001
Peel
London
0.17
1002
Serres
San Jose
0.13
1007
Rifkin
Barselona
0.15
Таблица 12.5: Использование EXISTS с комплексным подзапросом
Мы могли бы разобрать вышеупомянутый запрос примерно так:
Берем каждую строку таблицы Продавцов как строку-кандидат( внешний запрос ) и выполняем подзапросы. Для каждой строки-кандидата из внешнего запроса, берем в соответствие каждую строку из таблицы Заказчиков( средний запрос). Если текущая строка заказчиков не совпадает с текущей строкой продавца( т.е. если first.snum < > second.snum ), предикат среднего запроса неправилен. Всякий раз, когда мы находим заказчика в среднем запросе который совдает с продавцом во внешнем запросе, мы должны рассматривать сам внутренний запрос чтобы определить, будет ли наш средний предикат запроса верен. Внутренний запрос считает число порядков текущего заказчика (из среднего запроса). Если это число больший чем 1, предикат среднего запроса верен, и строки выбираются.
Это делает EXISTS предикат внешнего запроса верным для текущей строки продавца, и означает, что по крайней мере один из текущих заказчиков продавца имеет более чем один порядок.
Если это не кажется достаточно понятным для вас в этой точке разбора примера, не волнуйтесь. Сложность этого примера - хороша независимо от того, как часто будете Вы использовать ее в деловой ситуации. Основная цель примеров такого типа состоит в том, чтобы показать вам некоторые возможности которые могут оказаться в дальнейшем полезными. После работы со сложными ситуациями подобно этой, простые запросы которые являются наиболее часто используемыми в SQL, покажутся Вам элементарными.
Кроме того, этот запрос, даже если он кажется удобным, довольно из-вилистый способ извлечения информации и делает много работы. Он связывает три разных таблицы чтобы дать вам эту информацию, а если таблиц больше чем здесь указано, будет трудно получить ее напрямую (хотя это не единственный способ, и не обязательно лучший способ в SQL). Возможно вам нужно увидеть эту информацию относительно регулярной основы - если, например, вы имеете премию в конце недели для продавца который получил многочисленые порядки от одного заказчика. В этом случае, он должен был бы вывести команду, и сохранять ее чтобы использовать снова и снова по мере того как данные будут меняться (лучше всего сделать это с помощью представления, которое мы будем проходить в Главе 20 ).
РЕЗЮМЕEXISTS, хотя он и кажется простым, может быть одним из самых непонятных операторов SQL. Однако, он облажает гибкостью и мощностью. В этой главе, вы видели и овладели большинством возможностей которые EXISTS дает вам. В дальнейшем, ваше понимание улучшеной логики подзапроса расширится значительно.
Следующим шагом будет овладение тремя другими специальными операторами которые берут подзапросы как аргументы, это - ANY, ALL, и SOME. Как вы увидете в Главе 13, это - альтернативные формулировки некоторых вещей которые вы уже использовали, но которые в некоторых случаях, могут оказаться более предпочтительными.
