Теоретико-множественные операции при работе с базами данных

Теоретико-множественные операции при работе с базами данных

Любой разработчик, в проекте которого есть более 1 таблицы, рано или поздно начинает выполнять запросы к базе данных с использованием нескольких таблиц.

Некоторые типы запросов требуют выполнения так называемых JOIN операций. Именно об этих операция сегодня я и хотел поговорить.

Для понимания материала, изложенного ниже, читатель должен знать основы теории множеств. Нам понадобятся понятие множества, операции объединения, пересечения, разности, симметрической разности. Далее эти понятия поясняться не будут. Иллюстрация соответствующих операций производится при помощи диаграмм Венна.

Сама тема не нова. Однако многие статьи дают теорию так, что не понятно, куда ее дальше девать. Наше изложение будет максимально приближено к практическим целям.

Для указания столбца, по которому производится операция, могут использоваться ключевые слова USING и ON. Первое используется, если имена столбцов в таблицах совпадают, второе может использоваться как при совпадающих, так и при различающихся именах столбцов. Как правило, удобнее использовать ON, особенно при выполнении операций с 3 и более таблицами.

Первая операция, которую мы рассмотрим, называется INNER JOIN (или эквивалент CROSS JOIN).

Данная операция эквивалентна, операции пересечения множеств. То есть из таблиц выбираются только те строки, которые присутствуют и в первой таблице, и во второй.

Операция пересечения таблиц

Для иллюстрации операции создадим 2 таблицы:

user user_data
id name id user_id user_data
1 Вася 1 1 Блог1
2 Петя 2 3 Блог2
3 Маша 3 1 Блог3
4 Коля 4 0 Блог4

Итак, у нас есть пользователи и таблица с информацией для этих пользователей (пусть это будут блоги). Требуется выбрать блоги, у которых указан пользователь, вместе со своим пользователем.

Если долго не мудрить, то легко построить запрос вида:

Результатом его выполнения является множество строк, для которых в первой таблице есть id, а во второй такой же user_id:

user user_data
id name id user_id user_data
1 Вася 1 1 Блог1
1 Вася 3 1 Блог3
3 Маша 2 3 Блог2

Однако если подумать, то последний запрос можно заменить INNER JOIN операцией.

Второй тип операций FULL OUTER JOIN.

Следует заметить, что в популярной система баз данных Mysql данный вид запросов отсутствует, но в силу своей значимости мы о нем поговорим.

Итак, данный тип операций эквивалентен объединению множеств.

Объединение таблиц

Воспользуемся таблицами предыдущего примера.

или эквивалентно для Mysql:

В результате будет получен набор строк 3 типов:

1. Строки от операции пересечения (INNER JOIN)

2. Строки первой таблицы, для которых не нашлось пары во второй таблице (в столбцах второй таблицы вы найдете NULL)

3. Строки второй таблицы, для которых не нашлось пары в первой таблице (в столбцах первой таблицы вы найдете NULL)

Итак, результатом нашей операции является следующий набор данных:

user user_data
id name id user_id user_data
1 Вася 1 1 Блог1
1 Вася 3 1 Блог3
2 Петя (NULL) (NULL) (NULL)
3 Маша 2 3 Блог2
4 Коля (NULL) (NULL) (NULL)
(NULL) (NULL) 4 0 Блог4

Теперь предположим нам нужно выбрать всех пользователей, у которых нет блогов, и блоги, у которых нет пользователей. Данная операция в теории множеств называется симметрической разностью и при помощи диаграмм Венна может быть представлена так:

Симметрическая разность таблиц

Такой запрос можно выполнит при помощи нашего FULL OUTER JOIN, но нужно указать, что объединяемые поля первой или второй таблицы равны NULL:

Для Mysql данный запрос может быть выполнен эквивалентной конструкцией:

В результате получим набор данных, такого вида:

user user_data
id name id user_id user_data
2 Петя (NULL) (NULL) (NULL)
4 Коля (NULL) (NULL) (NULL)
(NULL) (NULL) 4 0 Блог4

Третий, и как мне кажется наиболее используемый тип запросов тип, - операции LEFT OUTER JOIN (или просто LEFT JOIN).

В запросах с операцией LEFT JOIN левая таблица является управляющей. Из левой таблицы производится полная выборка, при этом осуществляется поиск соответствующих строк в правой таблице. Если соответствующих строй не найдено, то в результат пойдет одна строка, у которой на местах полей правой таблицы будет NULL. Если же будут найдены соответствия, то все строки удовлетворяющие условию склейки таблиц будут внесены в результат.

Условно операцию можно представить в виде диаграммы Венна так:

Результат операции left join над таблицами

Сам запрос можно записать в виде:

Чтобы получить разность множеств нужно модифицировать запрос следующим образом:

Следующим образом:

Разность таблиц

В таком виде не очень понятно, как использовать данную операцию. Эффективное использование операции LEFT JOIN получается, когда между соответствующими таблицами установлено отношение "один к одному", или левая таблица относится к правой как "много к одному".

Практический пример:

Рассмотрим таблицы самого первого примера, но будем предполагать, что каждый блог имеет пользователя, добавившего этот блог:

user user_data
id name id user_id user_data
1 Вася 1 1 Блог1
2 Петя 2 3 Блог2
3 Маша 3 1 Блог3
4 Коля 4 2 Блог4

На сайте необходимо вывести все блоги с указанием авторов:

Тогда нужно выполнить следующий запрос:

Результатом станет набор данных:

user user_data
id name id user_id user_data
1 Вася 1 1 Блог1
3 Маша 2 3 Блог2
1 Вася 3 1 Блог3
2 Петя 4 2 Блог4

При выполнении таких запросов, число строк в результате не превышает числа строк в левой таблице.

Если же поменять таблицы местами (левая таблица относится к правой как "один ко многим"), то при большом числе связей между таблицами количество строк в результате может быть огромным. Если говорить про Mysql, то сервер при выполнении таких запросов может упасть, но это тема другой статьи.

Операция RIGHT JOIN.

Если есть LEFT JOIN, то почему бы не быть и RIGHT JOIN:) Данный запрос отличается от своего левого собрата лишь тем, что управляющей таблицей будет правая, а не левая. Более того, Mysql при выполнении запросов все RIGHT JOIN приводит к LEFT JOIN.

Операция NATURAL JOIN.

Про него я рассказывать не буду, так как использовать его не рекомендуется. При данном типе операций база сама выбирает, по каким столбцам проводить склейку строк, поэтому для выполнения запросов такого типа надо очень тонко понимать, как работает СУБД.

Запросы типа STRAIGHT JOIN.

Запросы данного типа полностью эквивалентны запросам INNER JOIN. Отличие лишь в том, что правая таблица читается первой.

Последним типом запросов является декартова выборка (или просто JOIN)

В запросах такого типа, каждой строке первой таблицы сопоставляется каждая строка второй таблицы. Таким образом, если первая таблица содержит N строк, а вторая - M строк, то результат будет содержать NxM строк.

Для нашего случая запрос такого типа может быть сформулирован так:

А результатом станет:

user user_data
id name id user_id user_data
1 Вася 1 1 Блог1
2 Петя 1 1 Блог1
3 Маша 1 1 Блог1
4 Коля 1 1 Блог1
1 Вася 2 3 Блог2
2 Петя 2 3 Блог2
3 Маша 2 3 Блог2
4 Коля 2 3 Блог2
1 Вася 3 1 Блог3
2 Петя 3 1 Блог3
3 Маша 3 1 Блог3
4 Коля 3 1 Блог3
1 Вася 4 2 Блог4
2 Петя 4 2 Блог4
3 Маша 4 2 Блог4
4 Коля 4 2 Блог4

Таким образом, проанализировав свой запрос можно построить эффективную его версию с использованием теоретико-множественных операций.

Нет комментариев.
Добавить комментарий
Вам необходимо включить показ изображений в браузере для того чтобы увидеть код

Дайте нам знать, что вы - живой человек. Для нас это важно!
Кликните, если плохо видно

Прежде чем высказать свое мнение, прочитайте пожалуйста: правила

Рейтинг@Mail.ru