Декартово произведение таблиц

В литературе [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. Вложенные подзапросы