Теперь, когда вы уже познакомились с подзапросами, мы можем поговорить о некоторых специальных операторах, которые всегда принимают подзапросы как аргументы. Вы узнаете о первом из их в этой главе. Остальные будут описаны в следующей главе.
Оператор EXISTS используется, чтобы указать предикату, производить ли подзапросу вывод или нет. В этой главе вы узнаете, как использовать этот оператор со стандартными и (обычно) соотнесёнными подзапросами. Мы будем также обсуждать специальные вопросы, которые перейдут в игру, когда вы будете использовать этот оператор как относительный агрегат, как пустой указатель NULL и как булев оператор. Кроме того, вы сможете повысить ваш профессиональный уровень относительно подзапросов, исследуя их в более сложных прикладных программах, чем те, которые мы видели до сих пор.
EXISTS это оператор, который производит верное или неверное значение, другими словами, булево выражение (см. в Главе 4 обзор этого термина).
Это означает, что он может работать автономно в предикате или в комбинации с другими выражениями, использующими булевы операторы AND, OR и NOT. Он берет подзапрос как аргумент и оценивает его как верный, если тот производит любой вывод, или как неверный, если тот не делает этого. Этим он отличается от других операторов предиката, в которых он не может быть неизвестным.
Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков, если, и только если, один или более заказчиков в этой таблице находятся в San Jose (вывод для этого запроса показан на Рисунке 12.1):
SELECT cnum, cname, city
FROM Customers
WHERE EXISTS
(SELECT *
FROM Customers
WHERE city = " San Jose');
Внутренний запрос выбирает все данные для всех заказчиков в San Jose. Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод был произведён подзапросом, и, поскольку выражение EXISTS было полным предикатом, делает предикат верным. Подзапрос (не соотнесённый) был выполнен только один раз для всего внешнего запроса, и, следовательно, имеет одно значение во всех случаях. Поэтому EXISTS, когда используется этим способом, делает предикат верным или неверным для всех строк сразу, что не так уж полезно для извлечения определенной информации.
=============== SQL Execution Log ============
| |
| SELECT snum, sname, city |
| FROM Customers |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers |
| WHERE city = 'San Jose'); |
| ============================================= |
| cnum cname city |
| ----- -------- ---- |
| 2001 Hoffman London |
| 2002 Giovanni Rome |
| 2003 Liu San Jose |
| 2004 Grass Berlin |
| 2006 Clemens London |
| 2008 Cisneros San Jose |
| 2007 Pereira Rome |
=============================================
Рисунок 12.1 Использование оператора EXISTS
В вышеупомянутом примере, EXISTS должен быть установлен так, чтобы легко выбрать один столбец, вместо того чтобы выбирать все столбцы, используя в выборе звёздочку (SELECT *). В этом состоит его отличие от подзапроса, который (как вы видели ранее в Главе 10, мог выбрать только один столбец). Однако в принципе он мало отличается при выборе 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 не был указан, каждый из этих продавцов будет выбран один раз для каждого заказчика, которому он назначен.
Однако для нас может быть полезнее вывести больше информации об этих продавцах, а не только их номера. Мы можем сделать это, объединив таблицу Заказчиков с таблицей Продавцов (вывод для запроса показан на Рисунке 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, оба условия основного предиката должны быть верны в заказе для верного предиката. Следовательно, результаты подзапроса имеют смысл только в тех случаях, когда вторая часть запроса верна, а объединение - выполнимо. Таким образом, комбинация объединения и подзапроса может стать очень мощным способом обработки данных.
Предыдущий пример показал, что 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 неправилен.
=============== 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, это альтернативные формулировки некоторых возможностей, которые вы уже использовали, но которые иногда могут оказаться предпочтительными.
1. Напишите запрос, который использовал бы оператор EXISTS для извлечения всех продавцов, имеющих заказчиков с оценкой 300. 2. Как бы вы решили предыдущую проблему, используя объединение? 3. Напишите запрос, использующий оператор EXISTS, который выберет всех продавцов с заказчиками, размещёнными в их городах, которые ими не обслуживаются. 4. Напишите запрос, который извлекал бы из таблицы Заказчиков каждого заказчика, назначенного продавцу, который в данный момент имеет по крайней мере ещё одного заказчика (кроме заказчика, которого вы выберете) с заказами в таблице Заказов (подсказка: это может быть похоже на структуру в примере с нашим трехуровневым подзапросом). (См. ответы в Приложении A.)