SELECT *
FROM Соcтав
WHERE Блюдо IN ( SELECT ID_блюда
FROM Блюда
WHERE Основа = 'Молоко');
При обработке полного запроса система выполняет, прежде всего, вложенный подзапрос. Этот подзапрос выдает множество номеров блюд, основой которых является молоко, а именно множество (7, 8, 12, 18, 22, 24, 28, 31). Поэтому первоначальный запрос эквивалентен такому простому запросу:
SELECT *
FROM Соcтав
WHERE Блюдо IN (7, 8, 12, 18, 22, 24, 28, 31);
Приведем пример запроса с несколькими уровнями вложенности:
Пример 27.
SELECT p.*, Количество, Цена
FROM Продукты p LEFT JOIN Наличие n
ON p. ID_Продукта = n.Продукт
WHERE p.ID_Продукта IN (SELECT Продукт
FROM Соcтав
WHERE Блюдо IN ( SELECT ID_блюда
FROM Блюда
WHERE Основа = ’Молоко’) );
Результат
|
|
ID_Продукта
|
Про
дукт
|
Белки
|
Жиры
|
Угле
воды
|
К
|
Са
|
Na
|
B2
|
PP
|
C
|
Коли
чество
|
Цена
|
3
|
Масло
|
60
|
825
|
90
|
230
|
220
|
740
|
0,1
|
1
|
NULL
|
73
|
274,61
|
5
|
Яйца
|
127
|
115
|
7
|
1530
|
550
|
710
|
4,4
|
1,9
|
NULL
|
61
|
111,83
|
6
|
Сметана
|
26
|
300
|
28
|
950
|
850
|
320
|
1
|
1
|
2
|
88
|
206,60
|
7
|
Молоко
|
28
|
32
|
47
|
1460
|
1210
|
1500
|
1,3
|
1
|
10
|
214
|
83,80
|
8
|
Творог
|
167
|
90
|
13
|
1120
|
1640
|
1410
|
2,7
|
4
|
5
|
92
|
82,80
|
13
|
Рис
|
70
|
6
|
773
|
540
|
240
|
260
|
0,4
|
16
|
NULL
|
54
|
51,17
|
14
|
Мука
|
106
|
13
|
732
|
1760
|
240
|
120
|
1,2
|
22
|
NULL
|
91
|
43,77
|
15
|
Яблоки
|
4
|
NULL
|
113
|
2480
|
160
|
260
|
0,3
|
3
|
NULL
|
117
|
189,92
|
16
|
Сахар
|
NULL
|
NULL
|
998
|
30
|
20
|
10
|
NULL
|
NULL
|
130
|
98
|
96,14
|
Этот запрос выдает информацию о продуктах, их цене и наличии на складе, необходимых для приготовления блюд на основе молока.
В любой ситуации, где вы можете использовать реляционный оператор сравнения (=), вы можете использовать IN. В отличие от реляционных операторов, IN не может заставить команду потерпеть неудачу, если больше чем одно значение выбрано подзапросом. Заметим, что использование IN соответствует операции пересечения реляционной алгебры, а NOT IN разности.
4.2.2. Использование одной и той же таблицы во внешнем и вложенном подзапросе
Пример 28.
Выдать номера Блюд, в состав которых входят продукты, необходимые для приготовления сырников.
SELECT DISTINCT Блюдо
FROM Соcтав
WHERE Продукт IN ( SELECT Продукт
FROM Состав
WHERE Блюдо = 18);
Отметим, что ссылка на Состав во вложенном подзапросе означает не то же самое, что ссылка на Состав во внешнем запросе. В действительности, два имени Состав обозначают различные значения. Чтобы этот факт стал явным, полезно использовать псевдонимы, например, С1 и С2:
SELECT DISTINCT Блюдо
FROM Соcтав С1
WHERE С1. Продукт IN ( SELECT Продукт
FROM Состав С2
WHERE С2.Блюдо = 18);
Здесь С1 и С2 – произвольные псевдонимы таблицы Состав, определяемые во фразе FROM и используемые как явные уточнители во фразах SELECT и WHERE. Напомним, что псевдонимы определены лишь в пределах одного запроса.
4.2.3. Использование агрегатных функций в подзапросах.
Любой запрос, использующий одиночную функцию агрегата без предложения GROUP BY, будет выбирать одиночное значение, а значит, может быть использован для сравнения в основном предикате.
Пример 29.
Необходимо найти самые дешевые продукты:
SELECT p.Продукт, Цена
FROM Продукты p JOIN Наличие n ON p.ID_продукта = n.Продукт
WHERE Цена = ( SELECT MIN (Цена) FROM Наличие);
Результат
|
|
Продукт
|
Цена
|
Зелень
|
34,96
|
Подзапрос ищет минимальную цену в таблице Наличие, а основной запрос выбирает продукты с найденной ценой.
Следует заметить, что если агрегатные функции используются с предложением GROUP BY, то подзапрос может вернуть многочисленные значения. Они, следовательно, не допустимы в подзапросах такого характера. Даже если GROUP BY и HAVING используются таким способом, что только одна группа выводится с помощью подзапроса, команда будет отклонена в принципе.
Если не удается построить подзапрос, возвращающий единственное значение вы должны использовать предикаты IN, ANY или ALL. Но следует заметить, что предикаты BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами
Следует отметить все подзапросы в качестве результата должны возвращать одиночный столбец или одиночное выражение (может быть основано на нескольких столбцах). Это обязательное требование, поскольку результат запроса сравнивается с одиночным значением. Исключением из этого правила является ситуация, когда подзапросы используются с оператором EXISTS, который мы будем рассматривать ниже.
4.2.4. Подзапросы в предложении HAVING.
Подзапросы могут использоваться внутри предложения HAVING.
Пример 30.
Найти самые дешевые блюда.
SELECT b.Блюдо,
cast(SUM (s.Вес*n.Цена/1000)+b.Труд as numeric(6,2)) as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда =s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд
HAVING (SUM (s.Вес*n.Цена/1000)+b.Труд) = ( SELECT MIN(стоимость)
FROM (SELECT b.Блюдо, SUM (s.Вес*n.Цена/1000)+b.Труд as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд ) a
);
Результат
|
|
Блюдо
|
Цена
|
Кофе черный
|
2,33
|
Этот запрос ищет для каждого блюда стоимость (псевдоним a), затем из этих данных выбирает минимум (подается на вход HAVING). Этот минимум используется для отбора блюд для вывода результата основного запроса.
4.3. Соотнесенные подзапросы.
Соотнесенным называется подзапрос, который ссылается на данные внешнего запроса. Соотнесенный подзапрос - один из большого количества тонких понятий в SQL из-за сложности в его оценке. Если вы сумеете овладеть им, вы найдете что он очень мощный, потому что может выполнять сложные функции с помощью очень лаконичных указаний. Соотнесенный подзапрос выполняется неоднократно, по одному разу для каждой строки таблицы основного запроса.
Пример 31.
Рассчитать стоимость блюд на основе молока.
SELECT b.Блюдо, ( SELECT SUM (s.Вес*n.Цена/1000)
FROM Состав s JOIN Наличие n ON s.Продукт = n.Продукт
WHERE s.Блюдо = b.ID_Блюда) + b.Труд as Стоимость
FROM Блюда b
WHERE Основа = 'Молоко';
Результат
|
|
Блюдо
|
Стоимость
|
Сметана
|
28,2671
|
Творог
|
19,9821
|
Суп молочный
|
35,9746
|
Сырники
|
25,0069
|
Вареники ленивые
|
24,1267
|
Суфле из творога
|
36,7818
|
Крем творожный
|
22,8368
|
Молочный напиток
|
45,4615
|
Такой подзапрос отличается от рассмотренного выше, тем, что вложенный подзапрос не может быть обработан прежде, чем будет обрабатываться внешний подзапрос. Это связано с тем, что вложенный подзапрос зависит от значения b.ID_Блюда, а оно изменяется по мере того, как система проверяет различные строки таблицы Блюда. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом:
Система проверяет первую строку таблицы Блюда. Предположим, что это строка удовлетворяет условию Основа = 'Молоко'
Теперь система может передать b.ID_Блюда в подзапрос и вычислить стоимость блюда.
Далее система будет повторять обработку такого рода для следующего блюда и т.д. до тех пор, пока не будут рассмотрены все строки таблицы Блюда.
Рассмотрим пример использования одной и той же таблицы во внешнем подзапросе и соотнесенном вложенном подзапросе.
Пример 32.
Выдать номера всех продуктов, которые используются только в одном блюде.
Do'stlaringiz bilan baham: |