Оператор EXISTS отличается от IN : EXISTS проверяет только сам факт
существования строк, в то время как IN проверяет реальные значения.
Подзапросы 185
Подсказка Как правило, запросы с EXISTS обеспечивают более высокую производительность,
чем запросы с IN. Поэтому следует там, где возможно, использовать EXISTS, а не IN.
При написании запросов, в которых используются NOT EXISTS или NOT
IN, следует быть очень внимательными. Когда в списке значений имеется
пустое значение, NOT EXISTS возвратит true, a NOT IN - false. В следующем
примере оператор NOT EXISTS используется для выборки тех типов товаров,
для которых товары отсутствуют в таблице products:
□ SELECT product_type_id, name
FROM product_types outer
WHERE NOT EXISTS
(SELECT 1
FROM products inner
WHERE inner.product_type_id = outer.product_type_id);
PRODUCT_TYPE_ID NAME
5 Magazine
Обратите внимание: э'гот запрос возвращает одну строку. В сл ед ую ще м
п р им ер е этот запрос переписан таким образом, что в н е м используется
оператор N O T IN; этот запрос не возвращает н и одной строки:
□ SELECT produ ct_type_id, name
FROM product_types
WHERE product_type_id NOT IN
(SELECT product_type_id
FROM products);
no rows se le c te d
Строки не возвращаются, потому что подзапрос возвращает список
значений produ ct_id, одно из которых является пустым - это значение
p rodu ct_type_id для товара № 12. Вследствие этого оператор NOT IN во
внешнем запросе возвращает значение false, поэтому не возвращается ни
одной строки. Это можно обойти, используя функцию NVL() для конвертирования
пустого (NULL) значения во что-то другое. В следующем примере
функция NVL() используется для конвертирования пустых значений
p rodu ct_type_id в 0:
□ SELECT product_type_id, name
FROM product_types
WHERE product_type_id NOT IN
(SELECT NVL(product_type_id, 0)
FROM products);
PRODUCT_TYPE_ID NAME
5 Magazine
Н а этот раз строка появилась.
Эти примеры демонстрируют ещё одно отличие между коррелированными
и некоррелированными подзапросами: коррелированный запрос
может разрешать пустые (NULL) значения.
186 Гпава 6
Написание вложенных подзапросов
Можно вкладывать подзапросы друг в друга вплоть до уровня вложенности
255. Но эту методику следует использовать расчетливо; может оказаться,
что запросы лучше работают, если использовать соединение таблиц.
Приведенный ниже пример использует вложенные подзапросы. Вложенный
подзапрос содержится в подзапросе, который, в свою очередь, размещен
во внешнем запросе:
П SELECT product_type_id, AVG(price)
FROM products
GROUP BY product_type_id
HAVING AVG(price) <
(SELECT MAX(AVG(price))
FROM products
WHERE product_type_id IN
(SELECT product_id
FROM purchases
WHERE quantity > 1)
GROUP BY product_type_id)
ORDER BY product_type_id;
PRODUCT_TYPE_ID AVG(PRICE)
1 24.975
3 13.24
4 13.99
13.49
Этот пример достаточно сложен и содержит три запроса: вложенный
подзапрос, подзапрос и внешний запрос. Эти части запроса работают
именно в таком порядке. Разберем пример на составные части и исследуем
возвращаемые результаты. Вот так выглядит вложенный подзапрос:
□ SELECT produ ct_id
FROM purchases
WHERE q u a n tity > 1
Этот подзапрос возвращает значения product_id для тех товаров, которые
были куплены более одного раза. Вот возвращенные подзапросом
строки:
П PRODUCT_ID
2
1
Подзапрос, получающий на свой вход эти выходные данные, имеет вид:
П SELECT MAX(AVG(price))
FROM products
WHERE product_type_id IN
(. . . выходные данные из предыдущего вложенного п одзапро са...)
GROUP BY product_type_id
Подзапросы 187
Этот подзапрос возвращает максимальное значение средней цены товаров,
возвращенных предыдущим вложенным подзапросом. Возвращаемая
строка имеет следующий вид:
□ MAX(AVG(PRICE))
26.22
Эта строка возвращается в следующий внешний запрос:
□ SELECT product_type_id, AVG(price)
FROM products
GROUP BY product_type_id
HAVING AVG(price) <
(. . . выходные данные предыдущего подзапроса.. . )
ORDER BY product_type_id;
Этот запрос возвращает produ ct_type_id и средние цены товаров, которые
меньше среднего значения, возвращенного предыдущим подзапросом.
Вот эти возвращенные строки:
□ PRODUCT_TYPE_ID AVGjf PRICE)
Это те самые строки, которые были возвращены показанным ранее
полным запросом.
Написание операторов UPDATE и DELETE, содержащих
подзапросы
До сих пор вы видели подзапросы, содержащиеся в выражениях SELECT.
Подзапросы можно использовать не только в этом операторе, но и в операторах
UPDATE и DELETE.
Написание операторов UPDATE, содержащих подзапросы
В операторе UPDATE можно установить новое значение столбца равным результату,