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

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

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

• { WHERE | HAVING } выражение [ NOT ] IN ( подзапрос );
• { WHERE | HAVING } выражение оператор_сравнения { ALL | SOME | ANY } ( подзапрос );
• {WHERE | HAVING } [ NOT ] EXISTS ( подзапрос );

Использование операций 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 Клиент.ГородКлиента<>
       Товар.ГородТовара)

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