Построение нетривиальных запросов

Использование подзапросов, возвращающих множество значений

Во многих случаях значение, подлежащее сравнению в предложениях WHERE или HAVING, представляет собой не одно, а несколько значений. Вложенные подзапросы генерируют непоименованное промежуточное отношение, временную таблицу. Оно может использоваться только в том месте, где появляется в подзапросе. К такому отношению невозможно обратиться по имени из какого-либо другого места запроса. Применяемые к подзапросу операции основаны на тех операциях, которые, в свою очередь, применяются к множеству, а именно:

Использование операций IN и NOT IN

Оператор IN используется для сравнения некоторого значения со списком значений, при этом проверяется, входит ли значение в предоставленный список или сравниваемое значение не является элементом представленного списка.

Пример 7.7. Определить список товаров, которые имеются на складе.

SELECT  Название
FROM  Товар
WHERE КодТовара In 
    (SELECT КодТовара FROM Склад)

Пример 7.8. Определить список отсутствующих на складе товаров.

SELECT  Название
FROM  Товар
WHERE КодТовара Not In (SELECT КодТовара 
    FROM Склад)

Пример 7.9. Определить товары, которые покупают клиенты из Москвы.

SELECT DISTINCT Товар.Название, 
    Клиент.ГородКлиента
FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка 
ON Клиент.КодКлиента=Сделка.КодКлиента) 
ON Товар.КодТовара=Сделка.КодТовара
WHERE Клиент.ГородКлиента='Москва'

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

Введение в запрос фразы "только" требует использования операции NOT IN.

Пример 7.10. Определить товары, покупку которых осуществляют только клиенты из Москвы, и никто другой.

SELECT DISTINCT Товар.Название, 
    Клиент.ГородКлиента
FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка 
ON Клиент.КодКлиента=Сделка.КодКлиента)
ON Товар.КодТовара=Сделка.КодТовара
WHERE Товар.Название NOT IN 
    (SELECT Товар.Название
    FROM Товар INNER JOIN 
       (Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента) 
    ON Товар.КодТовара=Сделка.КодТовара
    WHERE Клиент.ГородКлиента<>'Москва')

Пример 7.11. Какие товары ни разу не купили московские клиенты?

SELECT DISTINCT Товар.Название, 
    Клиент.ГородКлиента
FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка
    ON Клиент.КодКлиента=Сделка.КодКлиента)
    ON Товар.КодТовара=Сделка.КодТовара
WHERE Товар.Название NOT IN 
    (SELECT Товар.Название
FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка
    ON Клиент.КодКлиента=Сделка.КодКлиента)
    ON Товар.КодТовара=Сделка.КодТовара
WHERE Клиент.ГородКлиента='Москва')

Во вложенном запросе определяется список товаров, приобретаемых клиентами из Москвы. Во внешнем запросе выбираются только те товары, которые не входят в этот список.

Пример 7.12. Определить фирмы, покупающие товары местного производства.

SELECT DISTINCT Клиент.Фирма, Клиент.ГородКлиента,
    Товар.ГородТовара
FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента)
    ON Товар.КодТовара=Сделка.КодТовара
WHERE Клиент.ГородКлиента=Товар.ГородТовара

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

Введем в запрос фразу "только" – сразу потребуется привлечение операции NOT IN.

Пример 7.13. Определить фирмы, которые покупают только товары, произведенные в своем городе, и никакие другие.

SELECT DISTINCT Клиент.Фирма, 
    Клиент.ГородКлиента,
    Товар.ГородТовара
FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка
    ON Клиент.КодКлиента=Сделка.КодКлиента)
    ON Товар.КодТовара=Сделка.КодТовара
WHERE Клиент.ГородКлиента NOT IN 
    (SELECT DISTINCT  Клиент.ГородКлиента 
    FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента) 
    ON Товар.КодТовара=Сделка.КодТовара 
    WHERE Клиент.ГородКлиента<>
       Товар.ГородТовара)

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

Использование ключевых слов ANY и ALL

Ключевые слова ANY и ALL могут использоваться с подзапросами, возвращающими один столбец чисел.

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

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

Если в результате выполнения подзапроса получено пустое значение, то для ключевого слова ALL условие сравнения будет считаться выполненным, а для ключевого слова ANY – невыполненным. Ключевое слово SOME является синонимом слова ANY.

Пример 7.14. Определить клиентов, совершивших сделки с максимальным количеством товара (эквивалентно запросу 7.3.)

SELECT Клиент.Фамилия, Сделка.Количество
FROM Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента
WHERE Сделка.Количество>=ALL(SELECT Количество
    FROM Сделка)

В примере определены клиенты, в сделках которых количество товара больше или равно количеству товара в каждой из всех сделок.

Пример 7.15. Найти фирму, купившую товаров на сумму, превышающую 10000 руб.

SELECT Клиент.Фирма, 
    Sum(Товар.Цена*Сделка.Количество) 
    AS Общ_стоимость
FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента) 
    ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Клиент.Фирма
HAVING Sum(Товар.Цена*Сделка.Количество)>10000

Добавим в запрос подзапрос.

Пример 7.16. Найти фирму, которая приобрела товаров на самую большую сумму.

SELECT Клиент.Фирма, 
    Sum(Товар.Цена*Сделка.Количество) 
    AS Общ_стоимость
FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка
    ON Клиент.КодКлиента=Сделка.КодКлиента) 
    ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Клиент.Фирма
HAVING Sum(Товар.Цена*Сделка.Количество)>=
ALL(SELECT Sum(Товар.Цена*Сделка.Количество)
    FROM Товар INNER JOIN Сделка 
    ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Сделка.КодКлиента)

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

Пример 7.17. Найти фирмы, в сделках которых количество товара превышает такой же показатель хотя бы в одной сделке клиентов из Самары.

SELECT Клиент.Фирма, Сделка.Количество
FROM Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента
WHERE Сделка.Количество>
ANY(SELECT Сделка.Количество
    FROM Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента
WHERE Клиент.ГородКлиента='Самара')

Использование операций EXISTS и NOT EXISTS

Ключевые слова EXISTS и NOT EXISTS предназначены для использования только совместно с подзапросами. Результат их обработки представляет собой логическое значение TRUE или FALSE. Для ключевого слова EXISTS результат равен TRUE в том и только в том случае, если в возвращаемой подзапросом результирующей таблице присутствует хотя бы одна строка. Если результирующая таблица подзапроса пуста, результатом обработки операции EXISTS будет значение FALSE. Для ключевого слова NOT EXISTS используются правила обработки, обратные по отношению к ключевому слову EXISTS . Поскольку по ключевым словам EXISTS и NOT EXISTS проверяется лишь наличие строк в результирующей таблице подзапроса, то эта таблица может содержать произвольное количество столбцов.

Пример 7.18. Определить список имеющихся на складе товаров (запрос эквивалентен примеру 7.7).

SELECT Название
FROM Товар
WHERE EXISTS (SELECT КодТовара
    FROM Склад
WHERE Товар.КодТовара=Склад.КодТовара)

Пример 7.19. Определить список отсутствующих на складе товаров (запрос эквивалентен примеру 7.8).

SELECT Название
FROM Товар
WHERE NOT EXISTS (SELECT КодТовара
    FROM Склад
WHERE Товар.КодТовара=Склад.КодТовара)