Вложенные запросы, возвращающие одно значение

Часто перед подзапросом полезно ставить один из шести операторов сравнения (=, <>, <, <=, >, >=). Это можно делать тогда, когда у выражения, стоящего перед оператором, вычисляется единственное значение, а подзапрос, стоящий после оператора, также выдает одно значение. Исключением является оператор сравнения, сразу после которого находится квантор (ANY, SOME или ALL).

Чтобы проиллюстрировать случай, когда вложенный подзапрос возвращает единственное значение, вернемся к базе данных корпорации Zetec. В ней имеется таблица CUSTOMER (покупатель), содержащая информацию о компаниях, которые покупают товары Zetec. Кроме того, в ней имеется еще другая таблица, CONTACT (представитель для контакта), с личными данными о контактных представителях каждой компании-клиента. Структура этих таблиц приведена в табл. 11.4 и 11.5.

Таблица 11.4. Таблица CUSTOMER.

Столбец	Тип	Ограничения
CustiD (идентификатор покупателя)	INTEGER	PRIMARY KEY
Company (компания)	CHAR (40)
СustAddress (адрес покупателя)	CHAR (30)
Custcity (из какого города покупатель)	CHAR (20)
Custstate (из какого штата)	CHAR (2)
Сustzip (почтовый код покупателя)	CHAR (10)
CustPhone (телефон покупателя)	CHAR (12)
ModLevel	INTEGER

Таблица 11.5. Таблица CONTACT.

Столбец	Тип	Ограничения
CustID	INTEGER	FOREIGN KEY
ContFName (имя представителя)	CHAR (10)
СontLName (фамилия представителя)	CHAR (16)
ContPhone (телефон представителя)	CHAR (12)
Continfo (информация о представителе)	CHAR (50)

Скажем, вам надо посмотреть контактную информацию о компании Olympic Sales, но вы не помните, какой у этой компании идентификатор в столбце CustiD. Используйте такой вложенный запрос:

SELECT *

FROM CONTACT

WHERE CustiD =

(SELECT CustiD

FROM CUSTOMER

WHERE Company = 'Olympic Sales');

Результат его выполнения примерно следующий:

CustiD	ContFName	ContLName	ContPhone	Contlnfo
-------	-------------	--------------	------------	----------
118	Jerry	Attwater	505-876-3456	Will play major role in coordinating the wireless Web.

В последнем столбце говорится, что этот представитель занимается вопросами, как-то связанными с беспроводным доступом в Internet. Так что можете теперь позвонить Джерри Эттуотеру в Olympic и рассказать ему о специальной продаже сотовых телефонов, подключаемых к Internet.

Если в сравнении '=' используется подзапрос, то в списке SELECT этого подзапроса должен находиться один столбец (CustiD в этом примере). Подзапрос должен возвратить только одну строку. Это необходимо для того, чтобы в сравнении было одно значение.

В этом примере я предполагаю, что в таблице CUSTOMER находится только одна строка, в которой столбец Company содержит значение 'Olympic Sales'. Если в операторе CREATE TABLE, с помощью которого была создана таблица CUSTOMER, для столбца Company было установлено ограничение UNIQUE (уникальный), то это дает гарантию, что подзапрос в предыдущем примере возвратит только одно значение (или вообще ни одного). Однако подзапросы, похожие на тот, что используется в примере, обычно используются со столбцами, для которых это ограничение не установлено. В этих случаях, чтобы значения в столбце не повторялись, приходится полагаться на другие средства.

А если окажется, что в столбце Company таблицы CUSTOMER находится больше одного значения 'Olympic Sales' (филиалы в разных штатах), то выполнение подзапроса вызовет ошибку.

С другой стороны, если ни один покупатель из CUSTOMER не работает в Olympic Sales, то подзапрос возвратит значение NULL и результатом сравнения будет значение "unknown" (неизвестно). В этом случае итоговая виртуальная таблица будет пустой. Дело в том, что предложение WHERE возвращает только строки, для которых было получено значение True, а строки со значениями False и "unknown" будут отфильтрованы. Такое может, скорее всего, произойти, если по чьей-то ошибке в столбце Company окажется неправильное название, например 'Olumpic Sales'.

Хотя в таких структурах оператор равенства (=) и является самым распространенным, но в них можно использовать и пять остальных операторов сравнения. Для каждой строки из таблицы, которая указана в предложении замыкающего оператора, единственное значение, возвращаемое подзапросом, сравнивается с выражением из предложения WHERE того же оператора. Если результатом сравнения является значение True, то строка добавляется в выводимую виртуальную таблицу.

Если в состав подзапроса будет включена итоговая функция, то он гарантированно возвратит единственное значение. Эти функции всегда возвращают единственное значение. (Об итоговых функциях см. в главе 3.) Естественно, такой подзапрос будет полезен только тогда, когда требуется получить значение именно итоговой функции.

Скажем, вы торговый представитель компании Zetec и, чтобы оплатить неожиданно свалившиеся на вас счета, должны заработать довольно большие комиссионные. У вас не остается другого выхода, кроме как перестать тратить время на мелочевку и сосредоточиться на продаже только самых дорогих товаров. Самый дорогой товар вы определяете с помощью вложенного запроса:

SELECT Model, ProdName, ListPrice

FROM PRODUCT

WHERE ListPrice =

(SELECT MAX(ListPrice)

FROM PRODUCT);

Это пример вложенного запроса, в котором подзапрос и замыкающий оператор работают с одной и той же таблицей. Подзапрос возвращает единственное значение – максимальную цену из столбца ListPrice таблицы PRODUCT. А внешний запрос возвращает все строки из той же таблицы, имеющие максимальное значение в столбце ListPrice.

В следующем примере показан подзапрос сравнения, в котором используется оператор сравнения, отличный от '=':

SELECT Model, ProdName, ListPrice

FROM PRODUCT

WHERE ListPrice <

(SELECT AVG(ListPrice)

FROM PRODUCT);

Подзапрос возвращает единственное значение – среднее значение цен, находящихся в столбце ListPrice таблицы PRODUCT. А внешний запрос возвращает все строки из той же таблицы, в которых значение столбца ListPrice меньше этого среднего значения.

Первоначально стандарт языка SQL разрешал иметь в сравнении только один подзапрос, который должен был находиться в правой части запроса. Согласно стандарту SQL:1999 подзапросом может быть любой из двух операндов сравнения и даже оба сразу. А стандарт SQL:2003 поддерживает эту возможность.

Кванторы ALL, SOME и ANY

Другой способ сделать так, чтобы подзапрос возвращал единственное значение, – поставить перед этим подзапросом оператор сравнения с квантором. В сочетании с оператором сравнения квантор общности ALL (все) и кванторы существования SOME (некоторый) и ANY (какой-либо) обрабатывают список, возвращенный подзапросом, и в результате этот список сводится к единственному значению.

Воздействие этих кванторов на сравнение я проиллюстрирую примером, использующим базу данных из главы 10. В этой базе хранятся данные об играх, во время которых бейсбольные питчеры не менялись на подаче.

Содержимое двух таблиц получено с помощью следующих двух запросов:

SELECT * FROM NATIONAL;
FirstName	LastName	СompleteGames
-----------	-----------	------------------
Sal	Maglie	11
Don	Newcombe	9
Sandy	Koufax	13
Don	Drysdale	12
SELECT * FROM AMERICAN;
FirstName	LastName	СompleteGames
-----------	-----------	------------------
Whitey	Ford	12
Don	Larson	10
Bob	Turley	8
Allie	Reynolds	14

Теория состоит в том, что питчеры с самым большим количеством игр, бессменно сыгранных на подаче, должны находиться в Американской лиге потому, что в этой лиге разрешены назначенные хиттеры. Один из способов проверить эту теорию– создать запрос, который возвращает всех питчеров Американской лиги, бессменно сыгравших на подаче больше игр, чем все питчеры Национальной лиги. Для этого может быть сформулирован следующий запрос:

SELECT *

FROM AMERICAN

WHERE CompleteGames > ALL

(SELECT CompleteGames FROM NATIONAL);

Вот его результат:

FirstName	LastName	СompleteGames
-----------	-----------	------------------
Allie	Reynolds	14

Подзапрос (SELECT CompleteGames FROM NATIONAL) возвращает значения из столбца CompleteGames (количество бессменных игр) для всех питчеров Национальной лиги. Выражение > ALL означает, что надо возвращать только те значения CompleteGames из таблицы AMERICAN, которые больше любого значения, возвращаемого подзапросом. Иными словами, "больше наивысшего значения, возвращаемого подзапросом". В этом случае таким наивысшим значением является 13. В таблице AMERICAN единственной строкой, где находится большее значение, является запись Элли Рейнолдса (АШе Reynolds) с его 14 играми, бессменно сыгранными на подаче.

А что если ваше первоначальное допущение ошибочно? Что если лидером высшей лиги по количеству бессменных игр был все-таки питчер Национальной лиги, несмотря на то, что в Национальной лиге нет назначенного хиттера? Если это так, то запрос:

SELECT *

FROM AMERICAN

WHERE CompleteGames > ALL

(SELECT CompleteGames FROM NATIONAL);

…возвращает предупреждение о том, что нет строк, удовлетворяющих условиям запроса. А это означает, что в Американской лиге нет такого питчера, которых бессменно пробыл бы на подаче в течение большего количества игр, чем питчер-рекордсмен Национальной лиги.