Понимание SQL (775646), страница 14
Текст из файла (страница 14)
4. Напишите запрос который извлекал бы из таблицы Заказчиков каждого заказчика назначенного к продавцу который в данный момент имеет по крайней мере еще одного заказчика (кроме заказчика которого вы выберете)с порядками в таблице Порядков (подсказка: это может быть похоже на структуру в примере с нашим трехуровневым подзапросом).
(См. Приложение A для ответов. )
13. ИСПОЛЬЗОВАНИЕ ОПЕРАТОРОВ ANY, ALL и SOME
Теперь, когда вы овладели оператором exists, Вы узнаете три специальных оператора, ориентируемых на подзапросы. (Фактически, имеются только два, так как ANY и SOME - одно и то же.) Если вы поймете работу этих операторов, вы будете понимать все типы подзапросов предиката, используемых в SQL . Кроме того, вы будете представлены различным способам, где данный запрос может быть сформирован, используя различные типы подзапросов предиката, и вы поймете преимущества и недостатки каждого из этих подходов.
ANY, ALL, и SOME напоминают EXISTS, который воспринимает подзапросы как аргументы; однако они отличаются от EXISTS тем, что используются совместно с реляционными операторами. В этом отношении, они напоминают оператор IN когда тот используется с подзапросами; они берут все значения выведенные подзапросом и обрабатывают их как модуль. Однако, в отличие от IN, они могут использоваться только с подзапросами.
СПЕЦИАЛЬНЫЕ ОПЕРАТОРЫ ANY или SOME
Операторы SOME и ANY - взаимозаменяемы везде и там, где мы используем ANY, SOME будет работать точно так же. Различие в терминологии состоит в том, чтобы позволить людям использовать тот термин, который наиболее однозначен. Это может создать проблему; потому что, как мы это увидим, наша интуиция может иногда вводить в заблуждение.
Имеется новый способ нахождения продавца с заказчиками размещенными в их городах (вывод для этого запроса показывается в Рисунке 13.1):
SELECT *
FROM Salespeople
WHERE city = ANY
(SELECT city
FROM Customers );
Оператор ANY берет все значения выведенные подзапросом, (для этого случая - это все значения city в таблице Заказчиков), и оценивает их как верные если любой(ANY) из их равняется значению города текущей строки внешнего запроса.
=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE city = ANY |
| (SELECT city |
| FROM Customers); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.12 |
| 1002 Serres San Jose 0.13 |
| 1004 Motika London 0.11 |
=============================================
Рисунок 13. 1: Использование оператора ANY
Это означает, что подзапрос должен выбирать значения такого же типа как и те, которые сравниваются в основном предикате. В этом его отличие от EXISTS, который просто определяет, производит ли подзапрос результаты или нет, и фактически не использует эти результаты.
ИСПОЛЬЗОВАНИЕ ОПЕРАТОРОВ IN ИЛИ EXISTS ВМЕСТО ОПЕРАТОРА ANY
Мы можем также использовать оператор IN чтобы создать запрос аналогичный предыдущему :
SELECT *
FROM Salespeople
WHERE city IN
(SELECT city
FROM Customers );
Этот запрос будет производить вывод показанный в Рисунке 13.2.
Однако, оператор ANY может использовать другие реляционные операторы кроме равняется (=), и таким образом делать сравнения которые являются выше возможностей IN. Например, мы могли бы найти всех продавцов с их заказчиками, которые следуют им в алфавитном порядке (вывод показан на Рисунке 13.3)
SELECT *
FROM Salespeople
WHERE sname < ANY
(SELECT cname
FROM Customers);
=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE city IN |
| (SELECT city |
| FROM Customers); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.12 |
| 1002 Serres San Jose 0.13 |
| 1004 Motika London 0.11 |
=============================================
Рисунок 13. 2: Использование IN в качестве альтернативы к ANY
=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE sname < ANY |
| (SELECT cname |
| FROM Customers); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.12 |
| 1004 Motika London 0.11 |
| 1003 Axelrod New York 0.10 |
=============================================
Рисунок 13. 3: Использование оператора ANY с оператором "неравно" (<) продавцов для их заказчиков которые упорядоченны в алфавитном порядке (вывод показан на Рисунке 13.3)
SELECT *
FROM Salespeople
WHERE sname < ANY
(SELECT cname
FROM Customers);
Все строки были выбраны для Serres и Rifkin, потому что нет других заказчиков чьи имена следовали бы за ими в алфавитном порядке. Обратите внимание что это является d основным эквивалентом следующему запросу с EXISTS, чей вывод показывается в Рисунке 13.4:
SELECT *
FROM Salespeople outer
WHERE EXISTS
(SELECT *
FROM Customers inner
WHERE outer.sname < inner.cname);
=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople outer |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE outer.sname < inner.cname); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.12 |
| 1004 Motika London 0.11 |
| 1003 Axelrod New York 0.10 |
=============================================
Рисунок 13.4 Использование EXISTS как альтернатива оператору ANY
Любой запрос который может быть сформулирован с ANY (или, как мы увидим, с ALL), мог быть также сформулирован с EXISTS, хотя наоборот будет неверно. Строго говоря, вариант с EXISTS не абсолютно идентичен вариантам с ANY или с ALL из-за различия в том как обрабатываются пустые(NULL)значения (что будет обсуждаться позже в этой главе). Тем ни менее, с технической точки зрения, вы могли бы делать это без ANY и ALL если бы вы стали очень находчивы в использовании EXISTS (и IS NULL).
Большинство пользователей, однако, находят ANY и ALL более удобными в использовании чем EXISTS, который требует соотнесенных подзапросов. Кроме того, в зависимости от реализации, ANY и ALL могут, по крайней мере в теории, быть более эффективными чем EXISTS.
Подзапросы ANY или ALL могут выполняться один раз и иметь вывод используемый чтобы определять предикат для каждой строки основного запроса. EXISTS, с другой стороны, берет соотнесенный подзапрос, который требует чтобы весь подзапрос повторно выполнялся для каждой строки основного запроса. SQL пытается найти наиболее эффективный способ выполнения любой команды, и может попробовать преобразовать менее эффективную формулу запроса в более эффективную (но вы не можете всегда рассчитывать на получение самой эффективной формулировки).
Основная причина для формулировки EXISTS как альтернативы ANY и ALL в том что ANY и ALL могут быть несколько неоднозначен, из-за способа использования этого термина в Английском языке, как вы это скоро увидите. С приходом понимания различия способов формулирования данного запроса, вы сможете поработать над процедурами которые сейчас кажутся Вам трудными или неудобными.
КАК ANY МОЖЕТ СТАТЬ НЕОДНОЗНАЧНЫМ
Как подразумевалось выше, ANY не полностью однозначен. Если мы создаем запрос чтобы выбрать заказчиков которые имеют больший рейтинг чем любой заказчик в Риме, мы можем получить вывод который несколько отличался бы от того что мы ожидали (как показано в Рисунке 13.5):
SELECT *
FROM Customers
WHERE rating > ANY
(SELECT rating
FROM Customers
WHERE city = Rome);
В Английском языке, способ которым мы обычно склонны интерпретировать оценку "больше чем любой (где city = Rome)" , должен вам сообщить что это значение оценки должно быть выше чем значение оценки в каждом случае где значение city = Rome. Однако это не так, в случае ANY - используемом в SQL, ANY оценивает как верно, если подзапрос находит любое значение которое делает условие верным.
=============== SQL Execution Log ============
| |
| SELECT * |
| FROM Customers |
| WHERE rating > ANY |
| (SELECT rating |
| FROM Customers |
| WHERE city = 'Rome'); |
| ============================================= |
| cnum cname city rating snum |
| ----- -------- ---- ------ ------ |
| 2002 Giovanni Rome 200 1003 |
| 2003 Liu San Jose 200 1002 |
| 2004 Grass Berlin 300 1002 |
| 2008 Cisneros San Jose 300 1007 |
=============================================
Рисунок 13.5 Как оператор "больше чем"(>) интерпретируется ANY
Если мы оценим ANY способом использующим грамматику Английского Языка, то только заказчики с оценкой 300 будут превышать Giovanni, который находится в Риме и имеет оценку 200. Однако, подзапрос ANY также находит Periera в Риме с оценкой 100. Так как все заказчики с оценкой 200 были выше этой, они будут выбраны, даже если имелся другой заказчик из Рима(Giovanni) чья оценка не была выше (фактически, то что один из выбранных заказчиков также находится в Риме несущественно). Так как подзапрос произвел по крайней мере одно значение которое сделает предикат верным в отношении этих строк, строки были выбраны.
Чтобы дать другой пример, предположим что мы должны были выбирать все порядки сумм приобретений которые были больше чем по крайней мере один из порядков на 6-е Октября:
SELECT *
FROM Orders
WHERE amt > ANY
(SELECT amt
FROM Orders
WHERE odate = 10/06/1990 );
Вывод для этого запроса показывается в Рисунке 13.6.
Даже если самая высокая сумма приобретений в таблице (9891.88) - имелась на 6-е Октября, предыдущая строка имеет более высокое значение суммы чем другая строка на 6-е Октября, которая имела значение суммы = 1309.95. Имея реляционный оператор ">=" вместо просто " > ", эта строка будет также выбрана, потому что она равна самой себе.
Конечно, вы можете использовать ANY с другой SQL техникой, например с техникой объединения. Этот запрос будет находить все порядки со значением суммы меньшей чем значение любой суммы для заказчика в San Jose.
(вывод показывается в Рисунке 13.7 ):
SELECT *
FROM Orders
WHERE amt < ANY
(SELECT amt
FROM Orders A, Customers b
WHERE a.cnum = b.cnum
AND b.city = " San Jose');
Даже если наименьший порядок в таблице был для заказчика из San Jose, то был второй наибольший; следовательно почти все строки будут выбраны. Простой способ запомнить, что < ANY значение меньшее чем наибольшее выбранное значение, а > ANY значение большее чем наименьшее выбранное значение.
=============== SQL Execution Log ==============
| |
| SELECT * |
| FROM Orders |
| WHERE amt > ANY |
| (SELECT amt |
| FROM Orders |
| WHERE odate = 10/06/1990); |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3005 5160.45 10/03/1990 2003 1002 |
| 3009 1713.23 10/04/1990 2002 1003 |
| 3008 4723.00 10/05/1990 2006 1001 |
| 3011 9891.88 10/06/1990 2006 1001 |
================================================
Рисунок 13. 6: Выбранное значение больше чем любое(ANY) на 6-е Октября
=============== SQL Execution Log ==============
| |
| WHERE amt > ANY |
| (SELECT amt |
| FROM Orders a, Customers b |
| WHERE a.cnum = b.cnum |
| AND b.city = 'San Jose'); |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 3001 18.69 10/03/1990 2008 1007 |
| 3003 767.10 10/03/1990 2001 1001 |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3006 1098.10 10/03/1990 2008 1007 |
| 3009 1713.23 10/04/1990 2002 1003 |
| 3007 75.10 10/04/1990 2004 1002 |
| 3008 4723.00 10/05/1990 2006 1001 |
| 3010 1309.88 10/06/1990 2004 1002 |
================================================
Рисунок 13. 7: Использование ANY с объединением
Фактически, вышеуказанные команды весьма похожи на следующее - (вывод показан на Рисунке 13.8) :
SELECT *
FROM Orders
WHERE amt <
(SELECT MAX amt
FROM Orders A, Customers b
WHERE a.cnum = b.cnum
AND b.city = " San Jose');
=============== SQL Execution Log ==============
| |
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
