Мартин Грубер - Понимание SQL (991940), страница 19
Текст из файла (страница 19)
Если любые такие строки найдены внутренним запросом, это означает, что имеются два разных заказчика, обслуживаемыхтекущим продавцом (т.е. продавцом заказчика в текущей строке-кандидата из внешнего запроса). Предикат EXISTS поэтому верен для текущей строки, и номер продавца— поле (snum) таблицы, указанной во внешнем запросе, будет выведен. Если былDISTINCT не указан, каждый из этих продавцов будет выбран один раз для каждогозаказчика, к которому он назначен.КОМБИНАЦИЯ ИЗ EXISTS И ОБЬЕДИНЕНИЯОднако для нас может быть полезнее вывести больше информации об этих продавцах, а не только их номера. Мы можем сделать это, объединив таблицу Заказчиковс таблицей Продавцов (вывод для запроса показывается в Рисунке 12.3):SELECT DISTINCT first.snum, sname, first.cityFROM Salespeople first, Customers secondWHERE EXISTS ( SELECT *FROM Customers thirdWHERE second.snum = third.snumAND 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;|| ============================================= ||cnumcnamecity|| --------------||1001PeelLondon||1002SerresSan Jose|===============================================Рисунок 12.3: Комбинация EXISTS с обьединениемВнутренний запрос здесь — как и в предыдущем варианте, фактически сообщает, что псевдоним был изменен.
Внешний запрос — это обьединение таблицы Продавцов с таблицей Заказчиков, наподобии того, что мы видели прежде. Новоепредложение основного предиката (AND first.snum = second.snum) естественно оценивается на том же самом уровне, что и предложение EXISTS. Это — функциональный предикат самого обьединения, сравнивающий две таблицы из внешнего запросав терминах поля snum, которое являются для них общим.
Из-за Булева оператораAND, оба условия основного предиката должны быть верны в порядке для верногопредиката. Следовательно, результаты подзапроса имеют смысл только в тех случаяхкогда вторая часть запроса верна, а обьединение — выполняемо.
Таким образомкомбинация объединения и подзапроса может стать очень мощным способом обработки данных.ИСПОЛЬЗОВАНИЕ NOT EXISTSПредыдущий пример дал понять что EXISTS может работать в комбинации соператорами Буля. Конечно, то что является самым простым способом для использования и вероятно наиболее часто используется с EXISTS — это оператор NOT. Одиниз способов которым мы могли бы найти всех продавцов только с одним заказчикомбудет состоять в том, чтобы инвертировать наш предыдущий пример. (Вывод для этого запроса показывается в Рисунке 12.4.)SELECT DISTINCT snumFROM Customers outerWHERE NOT EXISTS ( SELECT *FROM Customers innerWHERE inner.snum = outer.snumAND 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: Использование EXISTS с NOTEXISTS И АГРЕГАТЫОдна вещь, которую EXISTS не может сделать — взять функцию агрегата в подзапросе.
Это имеет значение. Если функция агрегата находит любые строки для операций с ними, EXISTS верен, не взирая на то, что это — значение функции; если жеагрегатная функция не находит никаких строк, EXISTS неправилен.Попытка использовать агрегаты с EXISTS таким способом, вероятно покажет чтопроблема неверно решалась от начала до конца.Конечно, подзапрос в предикате EXISTS может также использовать один илиболее из его собственных подзапросов. Они могут иметь любой из различных типовкоторые мы видели (или который мы будем видеть). Такие подзапросы, и любые другие в них, позволяют использовать агрегаты, если нет другой причины по которой онине могут быть использованы.
Следующий раздел приводит этому пример.В любом случае, вы можете получить тот же самый результат более легко, выбрав поле которое вы использовали в агрегатной функции, вместо использования самой этой функции. Другими словами, предикат — EXISTS (SELECT COUNT (DISTINCTsname) FROM Salespeople) — будет эквивалентен EXISTS (SELECT sname FROMSalespeople) который был позволен выше.БОЛЕЕ УДАЧНЫЙ ПРИМЕР ПОДЗАПРОСАВозможные прикладные программы подзапросов могут становиться многократновкладываемыми.Вы можете вкладывать их два или более в одиночный запрос, и даже одинвнутрь другого.
Так как можно рассмотреть небольшой кусок чтобы получить всю картину работаты этой команды, вы можете воспользоваться способом в SQL, которыйможет принимать различные команды из большинства других языков.Имеется запрос, который извлекает строки всех продавцов которые имеют заказчиков с больше чем одним текущим порядком. Это не обязательно самое простоерешение этой проблемы, но оно предназначено скорее показать улучшеную логикуSQL.
Вывод этой информации связывает все три наши типовых таблицы:SELECT *FROM Salespeople firstWHERE EXISTS (SELECT *FROM Customers secondWHERE first.snum = second.snumAND 1 < (SELECT COUNT (*)FROM OrdersWHERE 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));|| ============================================= ||cnumcnamecitycomm|| ---------------------||1001PeelLondon0.17||1002SerresSan Jose0.13||1007RifkinBarselona0.15|===============================================Рисунок 12.5: Использование EXISTS с комплексным подзапросомМы могли бы разобрать вышеупомянутый запрос примерно так:Берем каждую строку таблицы Продавцов как строку-кандидат (внешний запрос)и выполняем подзапросы.
Для каждой строки-кандидата из внешнего запроса, берем всоответствие каждую строку из таблицы Заказчиков (средний запрос). Если текущаястрока заказчиков не совпадает с текущей строкой продавца (т.е. если first.snum < >second.snum), предикат среднего запроса неправилен. Всякий раз, когда мы находимзаказчика в среднем запросе который совдает с продавцом во внешнем запросе, мыдолжны рассматривать сам внутренний запрос чтобы определить, будет ли наш средний предикат запроса верен.
Внутренний запрос считает число порядков текущего заказчика (из среднего запроса). Если это число больший чем 1, предикат среднегозапроса верен, и строки выбираются. Это делает EXISTS предикат внешнего запросаверным для текущей строки продавца, и означает, что по крайней мере один из текущих заказчиков продавца имеет более чем один порядок.Если это не кажется достаточно понятным для вас в этой точке разбора примера, не волнуйтесь. Сложность этого примера — хороша независимо от того, как частобудете Вы использовать ее в деловой ситуации. Основная цель примеров такого типасостоит в том, чтобы показать вам некоторые возможности которые могут оказаться вдальнейшем полезными. После работы со сложными ситуациями подобно этой, простые запросы которые являются наиболее часто используемыми в SQL, покажутсяВам элементарными.Кроме того, этот запрос, даже если он кажется удобным, довольно извилистыйспособ извлечения информации и делает много работы.
Он связывает три разныхтаблицы чтобы дать вам эту информацию, а если таблиц больше чем здесь указано,будет трудно получить ее напрямую (хотя это не единственный способ, и не обязательно лучший способ в SQL). Возможно вам нужно увидеть эту информацию относительно регулярной основы — если, например, вы имеете премию в конце недели дляпродавца который получил многочисленые порядки от одного заказчика. В этом случае, он должен был бы вывести команду, и сохранять ее чтобы использовать снова иснова по мере того как данные будут меняться (лучше всего сделать это с помощьюпредставления, которое мы будем проходить в Главе 20).РЕЗЮМЕEXISTS, хотя он и кажется простым, может быть одним из самых непонятныхоператоров SQL.
Однако, он облажает гибкостью и мощностью. В этой главе, вы видели и овладели большинством возможностей которые EXISTS дает вам. В дальнейшем, ваше понимание улучшеной логики подзапроса расширится значительно.Следующим шагом будет овладение тремя другими специальными операторамикоторые берут подзапросы как аргументы, это — ANY, ALL, и SOME. Как вы увидете вГлаве 13, это — альтернативные формулировки некоторых вещей которые вы уже использовали, но которые в некоторых случаях, могут оказаться более предпочтительными.РАБОТА С SQL1. Напишите запрос который бы использовал оператор EXISTS для извлечения всехпродавцов которые имеют заказчиков с оценкой 300.2. Как бы вы решили предыдущую проблему используя обьединение ?3.
Напишите запрос использующий оператор EXISTS который выберет всех продавцов с заказчиками размещенными в их городах которые ими не обслуживаются.4. Напишите запрос который извлекал бы из таблицы Заказчиков каждого заказчиканазначенного к продавцу который в данный момент имеет по крайней мере ещеодного заказчика (кроме заказчика которого вы выберете) с порядками в таблицеПорядков (подсказка: это может быть похоже на структуру в примере с нашим трехуровневым подзапросом).(См. Приложение A для ответов.)13ИСПОЛЬЗОВАНИЕОПЕРАТОРОВ ANY, ALLИ SOMEТЕПЕРЬ, КОГДА ВЫ ОВЛАДЕЛИ ОПЕРАТОРОМ EXISTS, Вы узнаете приблизительно три специальных оператора ориентируемых на подзапросы. (Фактически,имеются только два, так как ANY и SOME — одно и то же.) Если вы поймете работуэтих операторов, вы будете понимать все типы подзапросов предиката используемыхв SQL. Кроме того, вы будете представлены различным способам где данный запросможет быть сформирован используя различные типы подзапросов предиката, и выпоймете преимущества и недостатки каждого из этих подходов.ANY, ALL, и SOME напоминают EXISTS который воспринимает подзапросы какаргументы; однако они отличаются от EXISTS тем, что используются совместно с реляционными операторами.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
