Мартин Грубер - Понимание SQL
(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. Он выводит набор из двух значений: 200 (для Liu ) и 300 (для Cisneros).
атем, основной запрос, выбирает все строки, с оценкой не совпадающей ни с одной из них - другими словами все строки с оценкой 100. Вы можете сформулировать тот же самый запрос используя оператор NOT IN:
SELECT*
FROM Customers
WHERE rating NOT IN
( SELECT rating
FROM Customers
WHERE city=" San Jose' );
Вы могли бы также использовать оператор ANY:
SELECT *
FROM Customers
WHERE NOT rating=ANY
( SELECT rating
FROM Customers
WHERE city=" San Jose' );
Вывод будет одинаков для всех трех условий.
ПРАВИЛЬНОЕ ПОНИМАНИЕ ANY И ALL
В SQL, сказать что - значение больше( или меньше ) чем любое(ANY) из набора значений - тоже самое что сказать, что оно больше( или меньше ) чем любое одно отдельное из этих значений. И наоборот, сказать что значение не равно всему(ALL) набору значений, тоже что сказать, что нет такого значения в наборе которому оно равно.
КАК ANY, ALL, И EXIST ПОСТУПАЮТM С ОТСУТСТВУЮЩИМИ И НЕИЗВЕСТНЫМИ ДАННЫМИ
Как было сказано, имеются некоторые различия между EXISTS и операторами представленными в этой главе относительно того как они обрабатывают оператор NULL. ANY и ALL также отличаются друг от друга тем как они реагируют если подзапрос не произвел никаких значений чтобы использовать их в сравнении. Эти различия могут привести к непредвиденным результатам на Ваши запросы если вы не будете их учитывать.
КОГДА ПОДЗАПРОС ВОЗВРАЩАЕТСЯ ПУСТЫМОдно значительное различие между ALL и ANY - способ действия в cитуации когда подзапрос не возвращает никаких значений. В принципе, всякий раз, когда допустимый подзапрос не в состоянии сделать вывод, ALL - автоматически верен, а ANY автоматически неправилен. Это означает, что следующий запрос
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' );
выведет всю таблицу Заказчиков. Когда нет никаких заказчиков в Boston,
естественно, ни одно из этих сравнений не имеет значення.
ANY И ALL ВМЕСТО EXISTS С ПУСТЫМ УКАЗАТЕЛЕМ( NULL )
Значения NULL также имеют некоторые проблемы с операторами наподобие этих. Когда SQL сравнивает два значения в предикате, одно из которых пустое (NULL), то результат неизвестен (смотрите Главу 5). Неизвестный предикат, подобен неверному и является причиной того что строка не выбирается, но работать он будет иначе в некоторых похожих запросах, в зависимости от того, используют они ALL или ANY вместо EXISTS. Рассмотрим наш предыдущий пример:
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' );
В общем, эти два запроса будут вести себя одинаково. Но предположим, что появилось пустое(NULL) значение в столбце rating таблицы Заказчиков:
CNUM CNAME CITY RATING SNUM 2003 Liu SanJose NULL 1002
В варианте с ANY, где оценка Liu выбрана основным запросом, значение NULL делает предикат неизвестным а строка Liu не выбирается для вывода. Однако, в варианте с NOT EXISTS когда эта строка выбрана основным запросом, значение NULL используется в предикате подзапроса, делая его неизвестным в каждом случае. Это означает что подзапрос не будет производить никаких значений, и EXISTS будет неправилен. Это, естественно, делает оператор NOT EXISTS верным. Следовательно, строка Liu будет выбрана для вывода. Это основное расхождение, в отличие от других типов предикатов, где значение EXISTS независимо от того верно оно или нет - всегда неизвестно. Все это является аргументом в пользу использования варианта формулировки с ANY. Мы не считаем что значение NULL является выше чем допустимое значение. Более того, результат будет тот же, если мы будем проверять для более низкого значения.
ИСПОЛЬЗОВАНИЕ COUNT ВМЕСТО EXISTS
Подчеркнем, что все формулировки с ANY и ALL могут быть в точности выполнены с EXISTS, в то время как наоборот будет неверно. Хотя в этом случае, также верно и то что EXISTS и NOT EXISTS подзапросы могут обманывать при выполнении тех же самых подзапросов с COUNT(*) в предложения SELECT подзапроса. Если больше чем ноль строк выводе будет подсчитано, это эквивалентно EXISTS; в противном случае это работает также как NOT EXISTS. Следующее является этому примером (вывод показывается в Таблице 13.12 ):
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
=РЕЗЮМЕИтак, вы прошли много чего в этой главе. Подзапросы не простая тема, и мы потратили много время чтобы показать их разновидности и неоднозначности. То чему Вы теперь научились, вещи достаточно глубокие. Вы знаете несколько технических решений одной проблемы, и поэтому вы можете выбрать то которое более подходит вашим целям. Кроме того, вы поняли, как различные формулировки будет обрабатывать пустые значения (NULL) и ошибки.
Теперь, когда вы полностью изучили запросы, наиболее важный, и вероятно наиболее сложный, аспект SQL, объем другого материала будет относительно прост для понимания.
Мы имеем еще одну главу о запросах, которая покажет вам как объединить выводы любого числа запросов в единое тело, с помощью формирования объединения многочисленых запросов используя оператор UNION.
РАБОТА С SQL* Напишите запрос который бы выбирал всех заказчиков чьи оценки равны или больше чем любая( ANY ) оценка заказчика Serres.
* Что будет выведено вышеупомянутой командой?
* Напишите запрос использующий ANY или ALL, который бы находил всех продавцов которые не имеют никаких заказчиков размещенных в их городе.
* Напишите запрос который бы выбирал все порядки с суммой больше чем любая (в обычном смысле ) для заказчиков в Лондоне.
* Напишите предыдущий запрос с использованием - MAX.
Глава 14. ИСПОЛЬЗОВАНИЕ ПРЕДЛОЖЕНИЯ UNION
В ПРЕДШЕСТВУЮЩИХ ГЛАВАХ, МЫ ОБСУЖДАЛИ различные способы которыми запросы могут помещаться один внутрь другого. Имеется другой способ объединения многочисленых запросов - то-есть формирование их в объединение. В этой главе, вы научитесь использованию предложения UNION в SQL. UNION отличается от подзапросов тем что в нем ни один из двух (или больше ) запросов не управляются другим запросом. Все запросы выполняются независимо друг от друга, а уже вывод их - обьединяется.
ОБЪЕДИНЕНИЕ МНОГОЧИСЛЕННЫХ ЗАПРОСОВ В ОДИНВы можете поместить многочисленые запросы вместе и объединить их вывод используя предложение UNION. Предложение UNION обьединяет вывод двух или более SQL запросов в единый набор строк и столбцов. Например чтобы получить всех продавцов и заказчиков размещенных в Лондоне и вывести их как единое целое вы могли бы ввести:
