|
Продукт
|
Количество
|
Цена
|
Говядина
|
108
|
429,84
|
Судак
|
0
|
0,00
|
Масло
|
73
|
274,61
|
Майонез
|
39
|
97,46
|
Яйца
|
61
|
111,83
|
Сметана
|
88
|
206,60
|
Молоко
|
214
|
83,80
|
Творог
|
92
|
82,80
|
Морковь
|
0
|
0,00
|
Лук
|
77
|
46,30
|
Помидоры
|
46
|
51,70
|
Зелень
|
13
|
34,96
|
Рис
|
54
|
51,17
|
Мука
|
91
|
43,77
|
Яблоки
|
117
|
189,92
|
Сахар
|
98
|
96,14
|
Кофе
|
37
|
166,50
|
Таблица Продукты соединяется с таблицей Наличие с помощью ключевого слова JOIN(INNER по умолчанию). После ON задаётся условие соединения, которое указывает, каким образом строки таблиц должны соответствовать друг другу, чтобы принять участие в соединении.
Между таблицами Продукты и Наличие существует связь «один к одному». Это означает, что для каждой строки из таблицы Продукты, в таблице Наличие будет найдено не более одной соответствующей строки (может не быть ни одной). В результат запроса попадут только те строки из Таблицы продукты, для которых соответствие найдено (продукт есть на складе).
Пример 22.
Более сложный запрос: Рассчитать стоимость блюда
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.Труд;
Результат______Блюдо'>Результат
|
|
Блюдо
|
Стоимость
|
Кофе черный
|
2,332
|
Сметана
|
28,2671
|
Компот
|
16,2558
|
Кофе на молоке
|
12,0205
|
Молочный напиток
|
45,4615
|
Творог
|
19,9821
|
Морковь с рисом
|
14,5298
|
…
|
|
Для того чтобы определить состав каждого блюда, таблица Блюда соединяется с таблицей Состав. Между таблицами Блюда и Состав существует связь «один ко многим». Что означает, что в состав каждого блюда может входить несколько продуктов. В результате внутреннего соединения получаем промежуточную таблицу. Для расчета стоимости полученную промежуточную таблицу соединяем с таблицей Наличие для определения цены продукта. Группировка по полю Блюдо позволяет каждое блюдо рассматривать как независимый набор строк, к которому применяется формула подсчета стоимости. Группировка по полю Труд позволяет учесть трудозатраты на изготовление одной порции блюда.
Левое внешнее соединение.
Запрос с левым внешним соединением идентичен уже рассмотренному запросу с внутренним соединением (Пример 21), за исключением того, что используются ключевые слова LEFT OUTER JOIN.
SELECT p.Продукт, n.Количество, n.Цена
FROM Продукты p LEFT JOIN Наличие n ON p.ID_Продукта = n.Продукт;
Результат
|
|
Продукт
|
Количество
|
Цена
|
Говядина
|
108
|
429,84
|
Судак
|
0
|
0,00
|
Масло
|
73
|
274,61
|
Майонез
|
39
|
97,46
|
Яйца
|
61
|
111,83
|
Сметана
|
88
|
206,60
|
Молоко
|
214
|
83,80
|
Творог
|
92
|
82,80
|
Морковь
|
0
|
0,00
|
Лук
|
77
|
46,30
|
Помидоры
|
46
|
51,70
|
Зелень
|
13
|
34,96
|
Рис
|
54
|
51,17
|
Мука
|
91
|
43,77
|
Яблоки
|
117
|
189,92
|
Сахар
|
98
|
96,14
|
Кофе
|
37
|
166,50
|
Масло растительное
|
NULL
|
NULL
|
Единственное различие между левым внешним объединением и внутренним в наличии лишней строки для продукта масло растительное результате. Этого продукта нет на складе (т.е. не выполняется условие внутреннего соединения). Строка включается потому, что использовано именно левое внешнее объединение, при котором все строки левой таблицы, Продукты, должны попасть в результат.
Если мы хотим узнать, какие продукты отсутствуют на складе, необходимо дополнить запрос условием:
SELECT p.Продукт,
ISNULL(CAST(n.Количество as VARCHAR), ’НЕТ’) as на_складе
FROM Продукты p LEFT JOIN Наличие n ON p.ID_Продукта = n.Продукт
WHERE n.Продукт IS NULL;
Результат
|
|
Продукт
|
на_складе
|
Масло растительное
|
НЕТ
|
Правое внешнее объединение.
Следующее правое внешнее соединение даёт в точности такие же результаты, как и левое:
SELECT p.Продукт,
ISNULL(CAST(n.Количество as VARCHAR),’НЕТ’) as на_складе
FROM Наличие n RIGHT JOIN Продукты p ON p.ID_Продукта = n.Продукт
Результат
|
|
Продукт
|
на_складе
|
Масло растительное
|
НЕТ
| WHERE n.Продукт IS NULL
Но как такое может быть?
В запросе поменяны местами таблицы! В запросе с правым соединением написано
FROM Наличие n RIGHT JOIN Продукты p
А в запросе с левым соединением было:
FROM Продукты p LEFT JOIN Наличие n
Из этого примера можно вынести важный урок: левое и правое внешние соединения абсолютно эквивалентны, смысл лишь в том, какая из таблиц является внешней — все строки этой таблицы будут включены в результат. Из-за этого многие программисты избегают правых внешних объединений и преобразуют их в левые, меняя таблицы местами; при этом таблица, из которой должны быть взяты все строки, всегда является левой. Левые внешние соединения для многих людей кажутся более понятными, чем правые.
Что если не менять таблицы местами в рассмотренном соединении? Предположим, запрос выглядит так:
SELECT p.Продукт, n.Количество, n.Стоимость
FROM Продукты p RIGHT JOIN Наличие n ON p.ID_Продукта = n.Продукт
На этот раз, как и в исходном левом объединении, таблица Продукты — левая, а таблица Наличие — правая.
Результат
|
|
Продукт
|
Количество
|
Цена
|
Говядина
|
108
|
429,84
|
Судак
|
0
|
0,00
|
Масло
|
73
|
274,61
|
Майонез
|
39
|
97,46
|
Яйца
|
61
|
111,83
|
Сметана
|
88
|
206,60
|
Молоко
|
214
|
83,80
|
Творог
|
92
|
82,80
|
Морковь
|
0
|
0,00
|
Лук
|
77
|
46,30
|
Помидоры
|
46
|
51,70
|
Зелень
|
13
|
34,96
|
Рис
|
54
|
51,17
|
Мука
|
91
|
43,77
|
Яблоки
|
117
|
189,92
|
Сахар
|
98
|
96,14
|
Кофе
|
37
|
166,50
|
Масло растительное
|
NULL
|
NULL
|
Результаты запроса, оказываются, идентичны результатам внутреннего соединения.
Как такое может быть? Нет ли ошибки? Нет, и причина лежит в содержимом таблиц. Вспомните: правое внешнее объединение возвращает все строки правой таблицы и соответствующие им строки левой таблицы, если такие найдутся. Таблица Наличие — правая, и в данном случае каждая запись имеет свой ID_Продукта в таблице Продукты (иначе бы при добавлении строк в таблицу Наличие мы бы имели ошибку, связанную с внешним ключом). Возвращаются все записи, и несоответствующих строк нет.
Поэтому в порядке вещей, что правое внешнее соединение в этом случае даст такие же результаты, как и внутреннее, ведь оно следовало правилу: вернуть все строки внешней таблицы и соответствия, если таковые найдутся. В данном случае, они не нашлись.
Чтобы на самом деле увидеть правые внешние соединения в действии, рассмотрим следующий запрос.
Пример 23.
SELECT b.Блюдо, m.Дата, SUM( z.Количество_порций)
FROM ( Заказы z RIGHT JOIN Меню m ON m.Блюдо = z. Блюдо
and m. Дата = z. Дата)
JOIN Блюда b ON m.Блюдо = b.ID_ Блюдо
WHERE m.Дата = '2011-01-02'
Результат
|
|
Блюдо
|
Дата
|
|
Бефстроганов
|
2011-01-02
|
4
|
Кофе на молоке
|
2011-01-02
|
2
|
Паштет из рыбы
|
2011-01-02
|
NULL
|
Салат летний
|
2011-01-02
|
10
|
Салат мясной
|
2011-01-02
|
NULL
|
Судак по-польски
|
2011-01-02
|
6
|
Суп молочный
|
2011-01-02
|
NULL
|
Суп харчо
|
2011-01-02
|
NULL
|
Суфле яблочное
|
2011-01-02
|
2
| GROUP BY b.Блюдо, m.Дата;
Данный запрос позволяет (благодаря правому внешнему соединению) вывести на экран все блюда из меню на 02.01.2011, даже если их никто не заказывал.
Do'stlaringiz bilan baham: |
