SQLГлава 13. Использование операторов ANY, ALL и SOME
Теперь, когда вы овладели оператором
Специальные операторы ANY или SOME
Операторы Вот новый способ нахождения продавцов с заказчиками, размещенными в их городах (вывод для этого запроса показан на Рисунке 13.1): SELECT * FROM Salespeople WHERE city = ANY (SELECT city FROM Customers);
Оператор =============== 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
Это означает, что подзапрос должен выбирать значения такого же типа, что и те, которые сравниваются в основном предикате. В этом его отличие от Использование IN или EXISTS вместо ANY
Мы можем также использовать оператор SELECT * FROM Salespeople WHERE city IN (SELECT city FROM Customers); Этот запрос даст вывод, показанный на Рисунке 13.2. =============== 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
Однако SELECT * FROM Salespeople WHERE sname < ANY (SELECT cname FROM Customers); =============== 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 с операцией "меньше" (<)
Здесь выбраны строки всех продавцов, за исключением 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 может стать неоднозначным?
Как говорилось выше, SELECT * FROM Customers WHERE rating > ANY (SELECT rating FROM Customers WHERE city = 'Rome');
В английском языке фраза "больше чем любой рейтинг в Риме" обычно означает, что нам нужен рейтинг, который больше каждого из рейтингов покупателей, живущих в этом городе. Однако в SQL слово =============== 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
Если бы Приведем еще один пример. Предположим, что требуется выбрать все заказы, которые стоят больше, чем хотя бы один из заказов от 6 октября: SELECT * FROM Orders WHERE amt > ANY (SELECT amt FROM Orders WHERE odate = '10/06/1990'); Вывод для этого запроса показан на Рисунке 13.6. =============== 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-е октября
Несмотря на то, что 6 октября был сделан самый крупный заказ во всей таблице (
Можно применять SELECT * FROM Orders WHERE amt < ANY (SELECT amt FROM Orders a, Customers b WHERE a.cnum = b.cnum AND b.city = 'San Jose'); =============== 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 с объединением
Самый маленький заказ в таблице был сделан покупателем из SELECT * FROM Orders WHERE amt < MAX(amt) (SELECT amt FROM Orders a, Customers b WHERE a.cnum = b.cnum AND b.city = 'San Jose'); =============== SQL Execution Log ============== | | | WHERE amt < | | (SELECT MAX (amt) | | FROM Orders a, Customers b | | WHERE a.cnum = b.cnum | | AND b.city = 'San Jose'); | | =============================================== | | 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.8 Использование агрегатной функции вместо ANY Специальный оператор ALL
С помощью SELECT * FROM Customers WHERE rating > ALL (SELECT rating FROM Customers WHERE city = 'Rome'): =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers | | WHERE rating > ALL | | (SELECT rating | | FROM Customers | | WHERE city = 'Rome'); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ------ | | 2004 Grass Berlin 300 1002 | | 2008 Cisneros San Jose 300 1007 | =============================================== Рисунок 13.9 Использование оператора ALL
Этот оператор проверяет значения рейтинга всех заказчиков в Риме. Затем он находит заказчиков с оценкой, большей, чем у любого из заказчиков в Риме. Самая высокая оценка в Риме — у
Как и в случае с SELECT * FROM Customers outer WHERE NOT EXISTS (SELECT * FROM Customers inner WHERE outer.rating <= inner.rating AND inner.city = 'Rome'); =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers outer | | WHERE NOT EXISTS | | (SELECT * | | FROM Customers inner | | WHERE outer rating <= inner.rating | | AND inner.city = 'Rome'); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ------ | | 2004 Grass Berlin 300 1002 | | 2008 Cisneros San Jose 300 1007 | =============================================== Рисунок 13.10 Использование EXISTS в качестве альтернативы ALL Равенства и неравенства
SELECT * FROM Customers WHERE rating = ALL (SELECT rating FROM Customers WHERE city = 'San Jose');
Эта команда допустима, но с этими данными мы не получим никакого вывода. Только в единственном случае вывод будет выдан этим запросом — если все значения оценки в SELECT * FROM Customers WHERE rating = (SELECT DISTINCT rating FROM Customers WHERE city = 'San Jose');
Основное различие в том, что эта последняя команда должна потерпеть неудачу, если подзапрос выведет много значений, в то время как вариант с SELECT * FROM Customers WHERE rating <> ALL (SELECT rating FROM Customers WHERE city = 'San Jose'); =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers | | WHERE rating <> ALL | | (SELECT rating | | FROM Customers | | WHERE city = 'San Jose'); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ------ | | 2001 Hoffman London 100 1001 | | 2006 Clemens London 100 1001 | | 2007 Pereira Rome 100 1004 | =============================================== Рисунок 13.11 Использование ALL с <>
Вышеупомянутый подзапрос выбирает все рейтинги для города San Jose. Он выводит набор из двух значений: SELECT * FROM Customers WHERE rating NOT IN (SELECT rating FROM Customers WHERE city = 'San Jose'); Вы могли бы также использовать оператор SELECT * FROM Customers WHERE NOT rating = ANY (SELECT rating FROM Customers WHERE city = 'San Jose'); Для всех трех операторов результат будет одинаковым. Правильное понимание ANY и ALL
В SQL, сказать, что значение больше (или меньше), чем любое ( Как ANY, ALL и EXIST поступают с отсутствующими и неизвестными данными?
Как было сказано, имеются некоторые различия между Когда подзапрос возвращается пустым?
Одно существенное различие между SELECT * FROM Customers WHERE rating > ANY (SELECT rating FROM Customers WHERE city = 'Boston'); не произведет никакого вывода, в то время как запрос SELECT * FROM Customers WHERE rating > ALL (SELECT rating FROM Customers WHERE city = 'Boston');
выведет всю таблицу Заказчиков. Когда нет никаких заказчиков в ANY И ALL вместо EXISTS с NULL
Значения Рассмотрим наш предыдущий пример: SELECT * FROM Customers WHERE rating > ANY (SELECT rating FROM Customers WHERE city = 'Rome'); И ещё один пример: SELECT * FROM Customers outer WHERE EXISTS (SELECT * FROM Customers inner WHERE outer.rating > inner.rating AND inner.city = 'Rome');
В общем, эти два запроса будут вести себя одинаково. Но предположим, что появилось пустое ( CNUM CNAME CITY RATING SNUM 2003 Liu SanJose NULL 1002
В варианте с Использование COUNT вместо EXISTS
Подчеркнём, что все формулировки с SELECT * FROM Customers outer WHERE NOT EXISTS (SELECT * FROM Customers inner WHERE outer.rating <= inner.rating AND inner.city = 'Rome'); =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers outer | | WHERE NOT EXISTS | | (SELECT * | | FROM Customers inner | | WHERE outer.rating <= inner.rating | | AND inner.city = 'Rome'); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ------ | | 2004 Grass Berlin 300 1002 | | 2008 Cisneros San Jose 300 1007 | =============================================== Рисунок 13.12 Использование EXISTS с соотнесённым подзапросом Это должно также быть выполнено как SELECT * FROM Customers outer WHERE 1 > (SELECT COUNT (*) FROM Customers inner WHERE outer.rating <= inner.rating AND inner.city = 'Rome'); Вывод к этому запросу показан на Рисунке 13.13. Теперь вы начинаете понимать, сколько способов имеется в SQL. Если это всё кажется несколько сложным на данном этапе, нет причин волноваться. Вы обучаетесь, чтобы использовать ту технику, которая больше отвечает вашим требованиям и наиболее понятна для вас. Начиная с этого места, мы хотим показать вам большое количество возможностей, чтобы вы могли найти ваш собственный стиль. =============== SQL Execution Log ============ | | | SELECT * | | FROM Customers outer | | WHERE 1 > | | (SELECT COUNT (*) | | FROM Customers inner | | WHERE outer.rating <= inner.rating | | AND inner.city = 'Rome'); | | ============================================= | | cnum cname city rating snum | | ----- -------- ---- ------ ------ | | 2004 Grass Berlin 300 1002 | | 2008 Cisneros San Jose 300 1007 | =============================================== Рисунок 13.13 Использование COUNT вместо EXISTS Резюме
Итак, вы узнали много нового в этой главе. Подзапросы — непростая тема, и мы потратили много времени, чтобы показать их варианты и неоднозначность. То, чему вы теперь научились, вещи достаточно глубокие. Вы знаете несколько технических решений одной проблемы, и поэтому можете выбрать то, которое больше подходит для ваших целей. Кроме того, вы поняли, как различные формулировки будут обрабатывать пустые значения ( Теперь, когда вы полностью изучили запросы — наиболее важный, и, вероятно, наиболее сложный аспект SQL — другой материал будет относительно прост для понимания.
У нас есть ещё одна глава о запросах, которая покажет вам, как объединить вывод любого количества запросов в единое тело с помощью формирования объединения нескольких запросов, используя оператор Работа с SQL
(См. ответы в Приложении A.) |
||