Дисклеймер: Данная статья является кратким изложением базовых концепций и не претендует на максимальный охват темы. Соединением называется механизм выборки из нескольких таблиц записей, связанных по определенному условию и является одной из самых распространенных задач при работе с базами данных.
Соединение таблиц связано с понятием декомпозиции базы данных, поэтому сначала кратко разберемся, что это такое.
Декомпозиция — подход, при котором данные хранятся не в одной общей таблице, а в нескольких, более специфичных и связываются между собой по определенному полю, называемому внешним ключом. Декомпозиция облегчает поддержку и масштабируемость базы данных, но при ее использовании, что бы вывести полные данные записи, расположенные в разных таблицах, необходимо задействовать соединение.
Пример:
База данных новостного сайта.
В ней хранятся статьи, у каждой статьи есть автор, категория, комментарии.
Технически можно хранить все в одной общей таблице, но по мере увеличения количества записей какой-либо из сущностей, такую таблицу очень быстро станет сложно поддерживать.
Пояснение:
Например, что бы переименовать категорию, нужно пройти по всем строкам со статьями из этой категории и в каждой внести изменения, а что то бы просто посмотреть какие есть категории, нужно делать выборку по всей таблице с удалением дублей по колонке категорий.
Поэтому используется декомпозиция — каждая сущность (статья, автор, категория, комментарий) хранятся в отдельных таблицах и связываются между собой через внешние ключи.
Внешний ключ — столбец в таблице, в каждой строке которого хранится значение уникального идентификатора (обычно это первичный ключ) из связанной таблицы.
Таким образом, внешний ключ — своего рода указатель на строку другой таблицы, который используется при соединении.
Внешний ключ не является обязательным. Можно просто добавить столбец со значениями идентификаторов из связанной таблицы, не указывая, что это внешний ключ.
Но если такой столбец назначить внешним ключом, то на таблицу накладывается так называемое «ограничение» (constrains) — жесткое правило, гарантирующее, что в связанной таблице точно есть нужные данные. Такое ограничение используется для обеспечения «ссылочной целостности (консистентости)» базы данных — гарантии, что свзязи указывают на существующие данные.
Пример таблиц
Articles
| id | title | text | author_id | category_id |
| 1 | Hello World! | Lorem Ispum | 1 | 1 |
Categories
| id | name |
| 1 | Business |
| 2 | Travel |
Возвращаемся к соединению.
Предположим, есть задача получить записи статей, а так же ее автора и категорию.
Это можно сделать через операцию соединения таблиц используя оператор JOIN.
Предположим, есть задача получить записи статей c названиями категорий.
Это можно сделать через операцию соединения таблиц используя оператор JOIN.
Формат
SELECT поля_таблиц
FROM таблица_1
JOIN таблица_2
ON условие_соединения
JOIN таблица_3
ON условие_соединения
... Пример простого соединения 2-х таблиц (статьи и категории):
SELECT articles.id, articles.title, categories.name
FROM articles
JOIN categories
ON categories.id = articles.category_id;В результате будут выведены все заголовки статей и их категории.
Если нужны все столбцы из articles:
SELECT articles.*, categories.name
FROM articles
JOIN categories
ON categories.id = articles.category_id;Если нужны все столбцы из обеих таблиц:
SELECT *
FROM articles
JOIN categories
ON categories.id = articles.category_id;Соединение может быть внутренним (INNER) или внешним (OUTER)
Если тип соединения не указан, то по умолчанию используется внутреннее соединение.
Внешнее соединение может быть левое (LEFT) или правое (RIGHT)
Для внешнего соединения модификатор OUTER можно не указывать, тип (LEFT/RIGHT) — обязательно.
Примеры указания типов:
INNER JOIN — явно указано внутренне соединение
JOIN — тип не указан, то же самое, что INNER JOIN
LEFT OUTER JOIN = LEFT JOIN
RIGHT OUTER JOIN = RIGHT JOIN
Разница между типами соединений.
- Внутреннее соединение возвращает только те строки, для которых выполняется условие соединения.
В примере: статьи, у которых есть категории. Если в статье указана несуществующая категория — такая запись не попадет в результат.
Внутреннее соединение можно релизовать через оператор WHERE
SELECT articles.id, articles.title, categories.name
FROM articles, categories
WHERE articles.category_id = categories.id;- Внешнее соединение возвращает все строки. Если условие не выполняется (в статье ссылка на несущуствующую категорию) в итогой таблице вместо значений будет подставленно NULL.
Варианты внешнего соединения:
LEFT JOIN — берутся все записи из левой таблицы, из правой — только совпавшие по условию соединения
RIGHT JOIN — берутся все записи из правой таблицы, из левой — только совпавшие по условию соединения
Положение таблицы (левая или правая) определяется ее позицией в запросе. Та что стоит первая - левая.SELECT *
FROM левая_таблица l
LEFT JOIN правая_таблица r
ON l.user_id = r.id;Пример LEFT JOIN
SELECT a.*, c.name
FROM articles a
LEFT JOIN categories c
ON a.category_id = c.id;В результате будут выведены все статьи, если у статьи нет категории — в c.name будет NULL
Если у категории нет статьи — категория не появится в результатах.
Пример RIGHT JOIN
SELECT a.*, c.name
FROM articles a
RIGHT JOIN categories c
ON a.category_id = c.id;В результате будут выведены все категории, если у категории нет статьи — в столбцах a.* будет NULL
Тот же результат можно получить через LEFT JOIN, поменяв таблицы местами, поэтому на практике RIGHT JOIN редко используется.
Еще раз: LEFT JOIN - выводим все строки из первой таблицы, а из второй те, для которых выполняется условие соединения.Фильтрация результатов с помощью оператора WHERE.
Порядок выполнения таких запросов:
FROM
JOIN
WHERE
SELECT
Пример:
Необходимо вывести названия и ID всех категорий, в которых нет статей.
Решение
SELECT c.id, c.name
FROM categories c
LEFT JOIN articles a
ON c.id=a.category_id
WHERE a.id IS NULL;Как работает:
Шаг 1. FROM .. JOIN ..
Создается соединение, в результате получается промежуточная таблица со всеми строками из таблицы категорий. Столбцы — все из обеих таблиц. Если у категории нет статьи — в ячейках (id, title, text, …) значение NULL
Шаг 2. Применяется фильтр WHERE, остаются только строки с aricles.id = NULL
Шаг 3. Применяется SELECT, остаются только categories.id/name.