Табличные подзапросы
Во многих случаях значение, подлежащее сравнению в предложениях WHERE и HAVING, представляет собой не одно, а несколько значений.
Подзапросы генерируют виртуальную таблицу (непоименованное промежуточное отношение), которая может использоваться только в том месте, где она появляется в подзапросе.
К такому отношению невозможно обратиться по имени из какого – либо другого места запроса.
Какие операции можно применять к такому промежуточному множеству:
{WHERE | HAVING } <выражение> [NOT] IN <подзапрос>;
{WHERE | HAVING } <выражение> оператор сравнения {ALL | SOME | ANY } <подзапрос>;
{WHERE | HAVING } [NOT] IN EXISTS <подзапрос>;
Примеры с использованием операций in и not in
Оператор IN используется для сравнения некоторого значения со списком значений. При этом проверяется, входит ли значение в предлагаемый список.
Пример 7. Выдать список товаров, не относящихся к категории «Молочные продукты».
SELECT Название_товара
FROM Товары
WHERE Название_товара NOT IN
(SELECT Название_товара
FROM Категории Join Товары
On Категории.ID_категории=Товары.Категории
WHERE Название_категории=’Молочные продукты’)
Пример 8. Выдать список товаров, которые были приобретены покупателями из Москвы.
В данном случае в запросе участвуют три таблицы – Товары, Продажи, Клиенты
SELECT DISTINCT
Товары.Название_товара, Клиенты. Фамилия
FROM
(Клиенты INNER JOIN Продажи
ON Клиенты.ID_Клиента = Продажи.Клиент )
INNER JOIN Товары
ON Товары. ID_Товара = Продажи.Товар
WHERE Клиенты.Город = ’Москва’
В результате будет выведен список товаров, которые приобретали клиенты из Москвы. Но не исключено, что эти же товары приобретали и клиенты из других городов.
Пример 9. Выдать список товаров, которые покупали только клиенты из Москвы, и никто другой.
SELECT DISTINCT
Товары.Название_товара, Клиенты. Фамилия
FROM
Товары INNER JOIN
(Клиенты INNER JOIN Продажи ON Клиенты.ID_Клиента = Продажи.Клиент ) ON Товары. ID_Товара = Продажи.Товар
WHERE Товары.Название NOT IN
(SELECT Товары.Название
FROM Товары INNER JOIN
(Клиенты INNER JOIN Продажи ON Клиенты.ID_клиента = Продажи.Клиент)
ON Товары.ID_товара = Продажи.Товар
WHERE Клиенты.Город < > ’Москва’)
Пример 10. Определить, какие товары ни разу не покупали клиенты из Воронежа.
SELECT DISTINCT Товары.Название_товара, Клиенты. Фамилия
FROM
Товары INNER JOIN
(Клиенты INNER JOIN Продажи ON Клиенты.ID_Клиента = Продажи.Клиент ) ON Товары. ID_Товара = Продажи.Товар
WHERE Товары.Название NOT IN
(SELECT Товары, название_товара FROM Товары INNER JOIN
(Клиенты INNER JOIN Продажи ON Клиенты.ID_клиента = Продажи.Клиент)
ON Товары.ID_товара = Продажи.Товар
WHERE Клиенты.Город = ‘Воронеж’)
Здесь во вложенном запросе определяется список товаров, приобретенных клиентами из Москвы. Во внешнем запросе выбираются только те товары, которые не входят в этот список.
Пример 11. Определить клиентов, покупавших товары российского производства.
SELECT DISTINCT Клиенты.Название_фирмы, Клиенты. Город, Товар.Страна_производитель
FROM
Товары INNER JOIN
(Клиенты INNER JOIN Продажи ON Клиенты.ID_Клиента = Продажи.Клиент ) ON Товары. ID_Товара = Продажи.Товар
WHERE Товары.Страна_прозводитель = ‘Россия’
Этот запрос не исключает наличие у клиента покупок из других стран-прозводителей.
Введем в запрос фразу «только» и сразу потребуется оператор NOT IN.
Пример 12. Определить клиентов, покупающих товары только российского производства.
SELECT DISTINCT Клиенты.Название_фирмы, Клиенты. Город, Товар.Страна_производитель
FROM
Товары INNER JOIN
(Клиенты INNER JOIN Продажи ON Клиенты.ID_Клиента = Продажи.Клиент ) ON Товары. ID_Товара = Продажи.Товар
WHERE Товары.Страна_прозводитель NOT IN
(SELECT DISTINCT Товары.Страна_производитель
FROM Товары INNER JOIN
(Клиенты INNER JOIN Продажи ON Клиенты.ID_Клиента = Продажи.Клиент ) ON Товары. ID_Товара = Продажи.Товар
WHERE Товары.Страна_производитель <> ‘Россия’
Во вложенном запросе определяется множество фирм, совершивших хотя бы одну покупку товара не российского производства. Затем определяются фирмы, не входящие в это множество.