|
Основа
|
Вес
|
Труд
|
Блюдо
|
Продукт
|
Вес
|
ID_продукта
|
Продукт
|
Белки
|
Жиры
|
Углеводы
|
К
|
Са
|
Na
|
B2
|
PP
|
C
|
1
|
Салат летний
|
1
|
Овощи
|
200
|
3
|
1
|
4
|
15
|
1
|
Говядина
|
189
|
124
|
NULL
|
3150
|
90
|
600
|
1,5
|
28
|
0
|
1
|
Салат летний
|
1
|
Овощи
|
200
|
3
|
1
|
11
|
100
|
1
|
Говядина
|
189
|
124
|
NULL
|
3150
|
90
|
600
|
1,5
|
28
|
0
|
1
|
Салат летний
|
1
|
Овощи
|
200
|
3
|
1
|
12
|
5
|
1
|
Говядина
|
189
|
124
|
NULL
|
3150
|
90
|
600
|
1,5
|
28
|
0
|
1
|
Салат летний
|
1
|
Овощи
|
200
|
3
|
1
|
15
|
80
|
1
|
Говядина
|
189
|
124
|
NULL
|
3150
|
90
|
600
|
1,5
|
28
|
0
|
…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Результат далек от нужного, так как содержит лишние строки. Если из декартова произведения убрать ненужные строки и столбцы, то можно получить актуальные таблицы, соответствующие операций соединение.
Очевидно, что отбор актуальных строк обеспечивается вводом в запрос фразы WHERE, в которой устанавливается соответствие между кодами блюд в таблицах Блюда и Состав (Блюда.ID_Блюда = Состав.Блюдо) и кодами продуктов в таблицах Состав и Продукты (Состав.Продукт = Продукты.ID_Продукта).
Такой скорректированный запрос имеет вид:
SELECT Блюда.Блюдо, Продукты.Продукт, Состав.Вес
FROM Блюда, Состав, Продукты
WHERE Блюда.ID_Блюда = Состав.Блюдо
and Состав.Продукт = Продукты.ID_Продукта;
Запрос позволяет получить эквисоединение таблиц (соединение при равенстве значений столбцов) Блюда, Состав и Продукты. В строке SELECT указаны лишь те столбцы, которые необходимы в результате:
Результат
|
|
|
Блюдо
|
Продукт
|
Вес
|
Салат летний
|
Майонез
|
15
|
Салат летний
|
Помидоры
|
100
|
Салат летний
|
Зелень
|
5
|
Салат летний
|
Яблоки
|
80
|
Салат мясной
|
Говядина
|
65
|
Салат мясной
|
Майонез
|
20
|
Салат мясной
|
Яйца
|
20
|
Салат мясной
|
Морковь
|
40
|
Салат мясной
|
Помидоры
|
35
|
Салат мясной
|
Зелень
|
20
|
Салат витаминный
|
Сметана
|
50
|
Салат витаминный
|
Лук
|
15
|
Салат витаминный
|
Помидоры
|
55
|
Салат витаминный
|
Зелень
|
20
|
…
|
|
|
Рассмотренный вид соединения является внутренним, так как исключает несовпадающие по значению строки. То есть, если в БД занесено блюдо, но для него ещё не указан состав, то информация о таком блюде не появится в результирующей таблице, а иногда такая информация необходима.
Решить эту проблему помогают внешние соединения. Для получения внешнего соединения применяют один из трех подходов:
Использование подзапроса с предикатом EXISTS
Объединение двух запросов с помощью UNION
Применение специальной конструкции JOIN в предложении FROM
Все они будут рассмотрены далее.
Следует отметить, что в предложении SELECT доступны все поля соединённых таблиц, даже если они не используются в запросе. Каждое имя поля уточняется с помощью точечной нотации, при которой имя поля следует через точку за именем таблицы. Такое уточнение необходимо, когда одно имя поля в запросе встречается более одного раза. (Конечно, эти поля будут находиться в разных таблицах, ведь невозможно создать два поля с одинаковыми именами в одной таблице.) Если не идентифицировать однозначно эти поля, то получим синтаксическую ошибку о неопределённости имён. Уточнение имён необходимо производить вне зависимости от того, ссылается ли запрос на оба поля или нет — каждая ссылка должна быть уточнена.
Когда поле в запросе встречается всего один раз, уточнение имён становится необязательным. Поэтому, мы могли получить такой же результат, воспользовавшись следующим кодом:
SELECT Блюда.Блюдо, Продукты.Продукт, остав.Вес
FROM Блюда, Состав, Продукты
WHERE ID_Блюда = Состав.Блюдо
and Состав.Продукт = ID_Продукта;
Тем не менее, указывать полные имена полей — это хорошая практика, поскольку иначе, взглянув на запрос, вы не всегда сможете сказать, к какой таблице относится каждое поле.
В некотором смысле, такой запрос будет самодокументированным — выполняемые им действия очевидны, поэтому его легче описать в документации.
Псевдонимы таблиц
Другой способ задания имён полей — это использование псевдонимов таблиц. Псевдоним — это альтернативное имя, присвоенное таблице в запросе. Как правило, псевдоним короче реального имени таблицы. Вот как наше соединение записывается с помощью псевдонимов:
SELECT b.Блюдо, p.Продукт, s.Вес
FROM Блюда b, Состав s, Продукты p
WHERE b.ID_Блюда = s.Блюдо and s.Продукт = p.ID_Продукта;
Здесь, таблице Блюда присвоен псевдоним b, таблице Состав — псевдоним s, а таблице Продукты – псевдоним p. Вы можете использовать в качестве псевдонима любые имена, псевдонимы создаются лишь на время выполнения запроса. Многие программисты стараются использовать однобуквенные псевдонимы по мере возможности, поскольку так уменьшается объём кода и повышается его читаемость. Единственная тонкость состоит в том, что как только вы определили псевдоним для таблицы, то в текущем запросе уже не можете использовать реальное имя таблицы, можно указывать только её псевдоним. Псевдонимы действуют только на протяжении одного запроса.
3.3.2. Соединение таблиц с дополнительными условиями.
При формировании соединения создается рабочая таблица, к которой применимы все операции, рассмотренные ранее: отбор нужных строк соединения (WHERE фраза), упорядочение получаемого результата (ORDER BY фраза) и агрегатирование данных (SQL-функции и GROUP BY фраза).
Пример 18. Выручка на каждый день (только продукты без учета трудозатрат).
SELECT z.Дата,
SUM( ( s.Вес*n.Цена/1000 )* z.Количество_порций ) as Выручка
FROM Заказы z, Состав s, Наличие n
WHERE z.Блюдо=s.Блюдо and s.Продукт=n.Продукт
GROUP BY z.Дата;
Результат
|
|
Дата
|
Выручка
|
2011-01-02
|
556,6954
|
2011-01-03
|
203,155
| 3.3.2.1. Соединение таблицы со своей копией.
В ряде приложений возникает необходимость одновременной обработки данных какой-либо таблицы и одной или нескольких ее копий, создаваемых на время выполнения запроса.
Например, при формировании Блюд (таблица БЛЮДА) возможен повторный ввод данных о каком-либо блюде с присвоением ему второго идентификатора. Для выявления таких ошибок, можно соединить таблицу Блюда с ее временной копией, установив в WHERE условие на равенство значений всех одноименных столбцов этих таблиц кроме столбцов с ID_Блюда (для последних надо установить условие неравенства значений).
Временную копию таблицы можно сформировать, указав имя псевдонима за именем таблицы во фразе FROM.
Пример 19.
Найти ошибки при формировании таблицы Блюда (повтор блюд)
SELECT К1.Блюдо,
К1.Основа, К1.ID_Блюда as ID , К2.ID_Блюда as Дубликат
FROM Блюда К1, Блюда К2
WHERE К1.Блюдо = К2.Блюдо AND К1.Основа = К2. Основа
AND К1.ID_Блюда<> К2.ID_Блюда;
Пример 20.
Нарастающий итог количества заказанных порций для блюда с номером 15
SELECT z1.Дата , z1.Блюдо, SUM(z.Количество_порций) as Итог
FROM Заказ z, Заказ z1
WHERE z.Блюдо=z1.Блюдо and z.Дата<=z1.Дата and z1.Блюдо=15
GROUP BY z1.Дата, z1.Блюдо
Результат
|
|
|
Дата
|
Блюдо
|
Итог
|
2011-01-02
|
15
|
6
|
2011-01-03
|
15
|
8
|
В этом случае способ соединения таблиц указывается в предложении FROM с помощью специального ключевого слова вместе с ключевым словом JOIN . JOIN соединяет строки двух таблиц, основываясь на правиле, которое называется условием соединения — оно сравнивает значения в строках обеих таблиц и определяет, какие из них должны быть в результате соединения.
Существует три основных типа соединения:
внутреннее соединение, задаваемое с помощью ключевых слов INNER JOIN
Синтаксис:
FROM таблица_А [ INNER ] JOIN таблица_B ON условие_соединения
внешнее соединение, которое может принимать три формы:
Синтаксис:
FROM таблица_А { LEFT | RIGHT | FULL } [ OUTER ] JOIN таблица_B ON условие_соединения
перекрёстное соединение, задаваемое ключевыми словами CROSS JOIN
Синтаксис
FROM таблица_А CROSS JOIN таблица_B ON условие_соединения
Чтобы визуально представить себе работу JOIN, мы будем использовать две таблицы с именами R1 и R2, показанные на Рис. 3.3.
Do'stlaringiz bilan baham: |
