Декартово произведение таблиц
В литературе [2] показано, что соединения - это подмножества декартова произведения. Так как декартово произведение n таблиц - это таблица, содержащая все возможные строки r, такие, что r является сцеплением какой-либо строки из первой таблицы, строки из второй таблицы, ... и строки из n-й таблицы (а мы уже научились выделять с помощью SELECT любое подмножество реляционной таблицы), то осталось лишь выяснить, можно ли с помощью SELECT получить декартово произведение. Для получения декартова произведения нескольких таблиц надо указать во фразе FROM перечень перемножаемых таблиц, а во фразе SELECT – все их столбцы.
Так, для получения декартова произведения Вид_блюд и Трапезы надо выдать запрос
SELECT Вид_блюд.*, Трапезы.*
FROM Вид_блюд, Трапезы;
Получим таблицу, содержащую 5 х 3 = 15 строк:
| В | Вид | Т | Трапеза |
| З | Закуска | Завтрак | |
| З | Закуска | Обед | |
| З | Закуска | Ужин | |
| С | Суп | Завтрак | |
| С | Суп | Обед | |
| С | Суп | Ужин | |
| Г | Горячее | Завтрак | |
| Г | Горячее | Обед | |
| Г | Горячее | Ужин | |
| Д | Десерт | Завтрак | |
| Д | Десерт | Обед | |
| Д | Десерт | Ужин | |
| Н | Напиток | Завтрак | |
| Н | Напиток | Обед | |
| Н | Напиток | Ужин |
В другом примере, где перемножаются таблицы Меню, Трапезы, Вид_блюд, Блюда:
SELECT Меню.*, Трапезы.*, Вид_блюд.*, Блюда.*
FROM Меню, Трапезы, Вид_блюд, Блюда;
образуется таблица (рис 3.1), содержащая 21 х 3 х 5 х 33 = 10395 строк.
Из первых 39 строк этой таблицы лишь две актуальных (отмечены "*"): в них совпадают номера блюд таблиц Меню и Блюда. В остальных – полная чепуха: к закускам отнесены супы и напитки, на завтрак предлагается незапланированный суп и т.д.
3.2.2. Эквисоединение таблиц
Если из декартова произведения убрать ненужные строки и столбцы, то можно получить актуальные таблицы, соответствующие любому из соединений.
| Меню | Трапезы | Вид_блюд | Блюда | |||||||||
| Т | В | БЛ | Т | Трапеза | В | Вид | БЛ | Блюдо | В | Основа | Выход | Труд |
| З | Завтрак | З | Закуска | Салат летний | З | Овощи | 200. | |||||
| З | Завтрак | З | Закуска | Салат мясной | З | Мясо | 200. | |||||
| З | Завтрак | З | Закуска | Салат витаминный | З | Овощи | 200. | 4 * | ||||
| . . . | ||||||||||||
| З | Завтрак | З | Закуска | Суп молочный | С | Молоко | 500. | |||||
| З | Завтрак | З | Закуска | Бастурма | Г | Мясо | 300. | |||||
| . . . | ||||||||||||
| З | Завтрак | З | Закуска | Кофе черный | Н | Кофе | 100. | |||||
| З | Завтрак | З | Закуска | Кофе на молоке | Н | Кофе | 200. | |||||
| З | Завтрак | З | Закуска | Салат летний | З | Овощи | 200. | |||||
| З | Завтрак | З | Закуска | Салат мясной | З | Мясо | 200. | |||||
| З | Завтрак | З | Закуска | Салат витаминный | З | Овощи | 200. | |||||
| З | Завтрак | З | Закуска | Салат рыбный | З | Рыба | 200. | |||||
| З | Завтрак | З | Закуска | Паштет из рыбы | З | Рыба | 120. | |||||
| З | Завтрак | З | Закуска | Мясо с гарниром | З | Мясо | 250. | 3 * | ||||
| . . . |
Рис. 3.1. Иллюстрация декартова произведения
Очевидно, что отбор актуальных строк обеспечивается вводом в запрос WHERE фразы, в которой устанавливается соответствие между:
- кодами трапез (Т) в таблицах Меню и Трапезы (Меню.Т = Трапезы.Т),
- кодами видов блюд (В) в таблицах Меню и Вид_блюд (Меню.В = Вид_блюд.В),
- номерами блюд (БЛ) в таблицах Меню и Блюда (Меню.БЛ = Блюда.БЛ).
Такой скорректированный запрос
SELECT Меню.*, Трапезы.*, Вид_блюд.*, Блюда.*
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ;
позволит получить эквисоединение таблиц Меню, Трапезы, Вид_блюд и Блюда:
| Т | В | БЛ | Т | Трапеза | В | Вид | БЛ | Блюдо | В | Основа | Выход | Труд |
| З | Завтрак | З | Закуска | Салат витаминный | З | Овощи | 200. | |||||
| З | Завтрак | З | Закуска | Мясо с гарниром | З | Мясо | 250. | |||||
| Г | Завтрак | Г | Горячее | Омлет с луком | Г | Яйца | 200. | |||||
| . . . | ||||||||||||
| Г | Ужин | Г | Горячее | Драчена | Г | Яйца | 180. | |||||
| Н | Ужин | Н | Напиток | Компот | Н | Фрукты | 200. | |||||
| Н | Ужин | Н | Напиток | Молочный напиток | Н | Молоко | 200. |
3.2.3. Естественное соединение таблиц
Легко заметить, что в эквисоединение таблиц вошли дубликаты столбцов, по которым проводилось соединение (Т, В и БЛ). Для исключения этих дубликатов можно создать естественное соединение тех же таблиц:
SELECT Т, В, БЛ, Трапеза, Вид, Блюдо, Основа, Выход, Труд
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ;
Реализация естественного соединения таблиц имеет вид
| Т | В | БЛ | Трапеза | Вид | Блюдо | Основа | Выход | Труд |
| З | Завтрак | Закуска | Салат витаминный | Овощи | 200. | |||
| З | Завтрак | Закуска | Мясо с гарниром | Мясо | 250. | |||
| Г | Завтрак | Горячее | Омлет с луком | Яйца | 200. | |||
| ... | ||||||||
| Г | Ужин | Горячее | Драчена | Яйца | 180. | |||
| Н | Ужин | Напиток | Компот | Фрукты | 200. | |||
| Н | Ужин | Напиток | Молочный напиток | Молоко | 200. |
3.2.4. Композиция таблиц
Для исключения всех столбцов, по которым проводится соединение таблиц, надо создать композицию
SELECT Трапеза, Вид, Блюдо, Основа, Выход, Труд
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ;
имеющую вид
| Трапеза | Блюдо | Вид | Основа | Выход | Труд |
| Завтрак | Салат витаминный | Закуска | Овощи | 200. | |
| Завтрак | Мясо с гарниром | Закуска | Мясо | 250. | |
| Завтрак | Омлет с луком | Горячее | Яйца | 200. | |
| . . . | |||||
| Ужин | Драчена | Горячее | Яйца | 180. | |
| Ужин | Компот | Напиток | Фрукты | 200. | |
| Ужин | Молочный напиток | Напиток | Молоко | 200. |
3.2.5. Тета-соединение таблиц
В базе данных ПАНСИОН трудно подобрать несложный пример, иллюстрирующий тета-соединение таблиц. Поэтому сконструируем такой надуманный запрос:
SELECT Вид_блюд.*, Трапезы.*
FROM Вид_блюд, Трапезы
WHERE Вид > Трапеза;
позволяющий выбрать из полученного в п.3.2.1 декартова произведения таблиц Вид_блюд и Трапезы лишь те строки, в которых значение трапезы "меньше" (по алфавиту) значения вида блюда:
| В | Вид | Т | Трапеза |
| З | Закуска | Завтрак | |
| С | Суп | Завтрак | |
| С | Суп | Обед | |
| Н | Напиток | Завтрак |
3.2.6. Соединение таблиц с дополнительным условием
При формировании соединения создается рабочая таблица, к которой применимы все операции, рассмотренные в главе 2: отбор нужных строк соединения (WHERE фраза), упорядочение получаемого результата (ORDER BY фраза) и агрегатирование данных (SQL-функции и GROUP BY фраза).
Например, для получения перечня блюд, предлагаемых в меню на завтрак, можно сформировать запрос на основе композиции (п. 3.2.4):
SELECT Вид, Блюдо, Основа, Выход, 'Номер -', БЛ
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ
AND Трапеза = ’Завтрак’;
Получим
| Вид | Блюдо | Основа | Выход | 'Номер -' | БЛ |
| Закуска | Салат витаминный | Овощи | 200. | Номер - | |
| Закуска | Мясо с гарниром | Мясо | 250. | Номер - | |
| Горячее | Омлет с луком | Яйца | 200. | Номер - | |
| Горячее | Пудинг рисовый | Крупа | 160. | Номер - | |
| Напиток | Молочный напиток | Молоко | 200. | Номер - | |
| Напиток | Кофе черный | Кофе | 100. | Номер - |
В п.3.6 можно познакомиться с достаточно полным примером соединения таблиц с различными дополнительными фразами.
3.2.7. Соединение таблицы со своей копией
В ряде приложений возникает необходимость одновременной обработки данных какой-либо таблицы и одной или нескольких ее копий, создаваемых на время выполнения запроса.
Например, при создании списков студентов (таблица Студенты) возможен повторный ввод данных о каком-либо студенте с присвоением ему второго номера зачетной книжки. Для выявления таких ошибок можно соединить таблицу Студенты с ее временной копией, установив в WHERE фразе равенство значений всех одноименных столбцов этих таблиц кроме столбцов с номером зачетной книжки (для последних надо установить условие неравенства значений).
Временную копию таблицы можно сформировать, указав имя псевдонима за именем таблицы во фразе FROM. Так, с помощью фразы
FROM Блюда X, Блюда Y, Блюда Z
будут сформированы три копии таблицы Блюда с именами X, Y и Z.
В качестве примера соединения таблицы с ней самой сформируем запрос на вывод таких пар блюд таблицы Блюда, в которых совпадает основа, а название первого блюда пары меньше (по алфавиту), чем номер второго блюда пары. Для этого можно создать запрос с одной копией таблицы Блюда (Копия):
SELECT Блюдо, Копия.Блюдо, Основа
FROM Блюда, Блюда Копия
WHERE Основа = Копия.Основа
AND Блюдо < Копия.Блюдо;
или двумя ее копиями (Первая и Вторая):
SELECT Первая.Блюдо, Вторая.Блюдо, Основа
FROM Блюда Первая, Блюда Вторая
WHERE Первая.Основа = Вторая.Основа
AND Первая.Блюдо < Вторая.Блюдо;
Получим результат вида
| Первая.Блюдо | Вторая.Блюдо | Основа |
| Морковь с рисом | Помидоры с луком | Овощи |
| Морковь с рисом | Салат летний | Овощи |
| Морковь с рисом | Салат витаминный | Овощи |
| Помидоры с луком | Салат витаминный | Овощи |
| Помидоры с луком | Салат летний | Овощи |
| Салат витаминный | Салат летний | Овощи |
| Бастурма | Бефстроганов | Мясо |
| Бастурма | Мясо с гарниром | Мясо |
| Бефстроганов | Мясо с гарниром | Мясо |
3.3. Вложенные подзапросы