Базы данных SQL: как правильно связывать таблицы с JOIN и ключами
Если вы работаете с реляционными базами данных, то должны уметь правильно связывать таблицы. Это необходимо для оптимизации запросов, структурирования информации и поддержания ее целостности. Связывать таблицы можно с помощью ключей и специальных команд. В статье разберем самые распространенные подходы.
Работа с базой данныхЧто такое реляционные базы данных
Реляционными называют базы данных, где информация хранится в виде таблиц. На скрине — пример.
Реляционная база данныхРеляционными такие базы назвали потому, что хранение данных в них организовано на принципах реляционной алгебры и теории отношений. Это позволяет связывать таблицы между собой и за счет этого легко искать и сопоставлять информацию.
Основные элементы структуры реляционной базы данных:
Таблицы — двухмерные структуры для упорядоченного хранения информации.
Столбцы — вертикальные поля, описывающие типы хранимых данных.
Строки — горизонтальные поля, содержащие основные сведения.
Ключи — атрибуты для идентификации и связывания сведений из разных таблиц.
Причин популярности реляционных базы данных много, в их числе:
Строгая структура данных: информация четко распределена, поэтому в ней удобно ориентироваться.
Поддержка SQL: стандартный язык запросов SQL позволяет делать выборки, вставки, обновлять и удалять данные.
Целостность данных: информация остается точной, неизменной и непрерывной на всех этапах работы с ней.
Моделирование сложных структур данных: благодаря связям между таблицами можно структурировать информацию, избегая дублирования и ошибок.
Удобство интеграции: реляционные базы данных интегрируются с различными приложениями, фреймворками и аналитическими инструментами.
Поддержка индексов: быстрое выполнение запросов даже при больших объемах данных.
Реляционные базы данных бывают локальными и облачными. Локальные находятся на компьютерах пользователей, а облачные — в облачных инфраструктурах, которые поддерживаются провайдерами. Например, использование управляемых решений, таких как Evolution Managed PostgreSQL от Cloud.ru, позволяет сосредоточиться на проектировании структур данных и написании запросов, полностью делегируя вопросы установки, обновления, резервного копирования и масштабирования базы данных провайдеру.
Ключи в SQL
Для идентификации записей в базе данных и связывания таблиц применяются так называемые ключи. Они бывают первичными, составными, внешними и уникальными.
Первичный ключ
Primary Key условно считается цифровым паспортом табличных записей. Он представляет собой поле/несколько полей, которое позволяет идентифицировать записи и дает гарантии, что они будут уникальными.
У первичного ключа БД есть особенности:
Значение Primary Key в таблице всегда уникально. Двух записей с одним параметром первичного ключа не бывает.
Всем записям присвоены уникальные идентификаторы, поэтому исключается пустое значение ключа.
Все сведения в базах данных упорядочены в соответствии с Primary Key.
Первичный ключ — важный элемент таблицы, поскольку он помогает ее индексировать и тем самым повышает производительность запросов. Также Primary Key способствует целостности данных.
На скрине — пример таблицы с именами студентов.
Таблица с первичным ключомВ этой таблице StudentID — первичный ключ. Применяя ключ для конкретного учащегося, можно вывести все данные о нем.
А чтобы создать первичный ключ нужно действовать следующим образом:
Алгоритм создания таблицы с первичным ключомСоставной ключ
По сути составной — это первичный ключ, только из двух и более столбцов. Следовательно, функции у него такие же.
Особенности составного ключа:
В ключе присутствуют значения из нескольких столбцов.
Уникальность составного ключа достигается за счет комбинации содержимого и привязанных к нему столбцов.
Значения в ключе не бывают нулевыми.
В таблице ниже составным ключом будут столбцы StudentID и CourseID.
Таблица с составным ключомА создается составной ключ так:
Алгоритм создания таблицы с составным ключомУникальный ключ
Unique Key ключ тоже связывает уникальные значения с данными из таблицы. Его отличия от первичного такие:
В одной таблице может быть не один уникальный ключ.
Такой ключ состоит из значений одного или нескольких столбцов, но комбинации этих значений всегда уникальны.
Уникальный ключ иногда содержит значения NULL.
В таблице-примере первичным ключом является OrderID, двумя комбинированными уникальными — OrderNumber+OrderDate и CustomerID+TotalAmount:
Таблица с уникальным ключомСоздается уникальный ключ с помощью алгоритма:
Создание уникального ключаВнешний ключ
Foreign Key связывает записи в первой таблице с записями во второй. Это происходит через поле, которое служит первичным ключом в главной таблице. Внешний ключ позволяет добиться четкой структуры базы данных и обеспечить ссылочную ценность, то есть согласованность сведений из двух таблиц.
Нюансы, связанные с внешним ключом в базах данных SQL:
В таблице бывает несколько Foreign Key.
Во внешнем ключе допустимы нулевые значения, если это не противоречит правилам организации хранения данных.
Ключ не определяет структуру таблицы и порядок хранения информации.
Таблица с внешним ключомЧтобы создать внешний код, нужно ввести:
Создание таблицы с внешним ключомВиды связи в базах данных
Есть три варианта связи таблиц в базах данных: «один к одному», «один ко многим», «многие ко многим». Разбираемся, как установить такие связи, на примере таблиц MySQL.
Один к одному
Подход One-to-One описывает связь между двумя таблицами, где каждая запись в первой таблице имеет одну соответствующую запись во второй. Схема позволяет организовать данные по логическим группам, избежать дублирования и повысить производительность запросов.
Чтобы установить такую связь, нужны первичный и внешний ключи. Как действовать:
Установите в одной из связываемых таблиц Primary Key, идентифицирующий нужную запись.
В другой таблице, с которой устанавливаете связь, создайте Foreign Key. Он будет ссылаться на первичный ключ в родительской таблице.
Визуализация связи «один к одному»На скринах — код в MySQL для каждой из связываемых таблиц.
Родительская таблица и дочерняя таблицыВ дочерней таблице есть первичный ключ EmployeeID. Он же — внешний в родительской таблице. Именно EmployeeID позволяет установить связь между записями.
Один ко многим
One-to-Many применяется, когда каждая запись в первой таблице может иметь отношение к нескольким записям второй таблицы. Но при этом каждая запись второй таблицы может относиться только к одной записи первой.
Связь One-to-Many отвечает за моделирование зависимости в данных. Например, в одном подразделении компании числятся десятки сотрудников, но каждый работник относится только к одному подразделению.
Чтобы получить отношение «Один-ко-многим», в главной таблице сформируйте первичный ключ, а в другой — внешний ключ, который будет отсылкой на первичный.
В результате каждая запись во второй таблице будет соотнесена с одной записью в главной. Зато у записи в родительской может быть несколько связанных записей в дочерней.
На скрине пример связи между тремя таблицами — одной родительской и двумя дочерними.
Визуализация связи «один ко многим»Чтобы создать такую связь, используйте:
Родительская таблица Department 
Дочерние таблицы Employee и Project Многие-ко-многим
Many-to-Many применяется, если каждая запись в главной таблице может соотноситься с несколькими записями в дочерней, и наоборот.
Чтобы получилась связь «Многие-ко-многим», сделайте две таблицы с первичным ключом в каждой. Затем подготовьте еще одну таблицу для хранения внешних ключей из основных таблиц и установите через нее связи с помощью внешних ключей.
На скрине — наглядное представление связи:
Визуализация связи «многие ко многим»Создается связь «многие ко многим» с помощью кода:
Код для связи «многие ко многим»Соединение таблиц с помощью JOIN
Чтобы связать данные из разных таблиц, можно использовать команды JOIN в SQL. Рассмотрим варианты JOIN-команд и их применение.
INNER JOIN
Команда INNER JOIN в SQL позволяет объединять таблицы на основе общего столбца. Она выбирает только те записи, которые есть в обеих таблицах и соответствуют условию соединения. Если совпадений нет — строка не попадает в результат.
Чтобы было понятнее, разберем пример с двумя таблицами. У нас есть:
Таблица Users
user_id | username | email |
1 | Ivan | ivan@mail.com |
2 | Anna | anna@mail.com |
3 | Sergey | sergey@mail.com |
Таблица Orders
order_id | order_date | amount | user_id |
101 | 2023-09-01 | 250.00 | 1 |
102 | 2023-09-02 | 180.50 | 2 |
103 | 2023-09-03 | 90.00 | 4 |
Тогда пример запроса для их объединения:
Результатом выполнения команды будет таблица:
user_id | username | order_id | amount |
1 | Ivan | 101 | 250.00 |
2 | Anna | 102 | 180.50 |
В таблице мы видим, что пользователь Ivan связан с заказом №101. Anna — с заказом №102. Пользователь Sergey в таблице заказов отсутствует, поэтому он не попал в результат. Заказ №103 с user_id=4 тоже не учитывается, поскольку пользователя нет в таблице Users.
LEFT JOIN и RIGHT JOIN
LEFT JOIN возвращает все записи из левой таблицы, даже если для них нет соответствий в правой. RIGHT JOIN действует по тому же принципу, но наоборот — возвращает записи из правой таблицы и добавляет данные из левой, если они есть. Если совпадений не найдется, то на месте «пустых» значений будет NULL.
Пользователи (users):
id | name |
1 | Иван |
2 | Мария |
3 | Алексей |
Заказы (orders):
id | user_id | product |
1 | 1 | Телефон |
2 | 2 | Ноутбук |
3 | 3 | Планшет |
Пример кода для связывания таблиц LEFT JOIN:
Результат:
name | product | |
Иван | Телефон | |
Мария | Ноутбук | |
Алексей | NULL |
Клиент все равно выводится, даже если он ничего не заказывал.
Пример кода для связывания таблиц RIGHT JOIN:
Результат:
name | product |
Иван | Телефон |
Мария | Ноутбук |
NULL | Планшет |
Заказ с планшетом есть, но пользователь с id=4 отсутствует в таблице users.
FULL OUTER JOIN
FULL OUTER JOIN объединяет строки из обеих таблиц, даже если нет совпадений по условию соединения. Если совпадение найдено — строки объединяются, если нет — команда ставит NULL.
Таблица Users
user_id | username | email |
1 | Ivan | ivan@mail.com |
2 | Anna | anna@mail.com |
3 | Sergey | sergey@mail.com |
Таблица Orders
order_id | order_date | amount | user_id |
101 | 2023-09-01 | 250.00 | 1 |
102 | 2023-09-02 | 180.50 | 4 |
Запрос для связывания FULL OUTER JOIN:
Что получается:
user_id | username | order_id | amount |
1 | Ivan | 101 | 250.00 |
2 | Anna | NULL | NULL |
3 | Sergey | NULL | NULL |
NULL | NULL | 102 | 180.50 |
Пользователь Ivan совпал с заказом №101, поэтому данные из обеих таблиц объединились. Anna и Sergey есть в таблице Users, но заказов у них нет, поэтому в полях Orders стоит NULL. Для заказа №102 с user_id = 4 не нашлось пользователя, поэтому в полях Users будет NULL.
CROSS JOIN
CROSS JOIN в SQL это команда, которая объединяет две таблицы, создавая все возможные комбинации строк между ними. Каждая строка из первой таблицы объединяется со всеми строками из второй. Условия соединения в этом случае не нужны — команда просто перемножает строки. Например, если в первой таблице 3 строки, а во второй 4, то на выходе получится 12 строк.
Таблица Products
product_id | product_name |
1 | Laptop |
2 | Phone |
Таблица Colors
color_id | color_name |
1 | Black |
2 | White |
3 | Silver |
Запрос с CROSS JOIN:
Результат выполнения:
product_name | color_name |
Laptop | Black |
Laptop | White |
Laptop | Silver |
Phone | Black |
Phone | White |
Phone | Silver |
Практические примеры SQL-запросов с JOIN
В некоторых проектах приходится использовать сложные запросы, состоящие из нескольких команд. Приведем два примера.
1 пример: INNER JOIN + LEFT JOIN
Допустим, у нас есть 3 таблицы:
Customers — клиенты.
Orders — заказы.
Payments — платежи.
Делаем такой запрос:
INNER JOIN связывает клиентов и заказы. LEFT JOIN добавляет информацию о платежах. Если платежа еще нет, будет значение NULL.
2 пример: RIGHT JOIN + INNER JOIN
Имеем три таблицы:
Employees — сотрудники.
Departments — отделы.
Projects — проекты.
Запрос:
RIGHT JOIN гарантирует, что каждый сотрудник попадает в результат, даже если не числится ни в одном отделе. INNER JOIN оставляет только тех сотрудников, которые участвуют в проектах.
Агрегатные функции и групповые операции
Агрегатные функции в базах данных — это функции, которые работают не с отдельными строками, а с наборами данных, позволяя обобщать и группировать информацию.
Некоторые агрегатные функции в SQL:
COUNT() — количество строк.
SUM() — сумма значений.
AVG() — среднее значение.
MIN() — минимальное значение.
MAX() — максимальное значение.
Групповые операции реализуются с помощью GROUP BY. Она объединяет строки по какому-то признаку, чтобы к каждой группе применить агрегатные функции.
Например, нужно подсчитать количество заказов у каждого клиента. Есть две таблицы — Customers (customer_id, name) и Orders (order_id, customer_id, amount).
Применяем команду:
Этот запрос покажет всех клиентов и количество их заказов. Даже если заказов нет, благодаря команде LEFT JOIN клиент все равно попадет в результат, но с нулевым значением.
Оптимизация данных с помощью индексов
Чтобы повысить производительность запросов в базах данных, можно использовать индексы. Это дополнительные структуры, которые помогут оперативно искать нужные записи. Они особенно полезны, если вы объединяете таблицы с большим количеством данных. Индексы будут выполнять роль указателей.
Типов индексов для баз данных SQL много. Основные классификации:
Кластеризованные и некластеризованные. Первые определяют порядок хранения данных в таблице. Вторые — создают отдельную структуру данных с указателями на табличные строки.
Уникальные и неуникальные. Первые гарантируют уникальность всех значений в столбце таблицы. Вторые — допускают, что значения могут повторяться.
Bitmap — индекс для столбцов, где много повторяющихся значений. Для представления табличных данных он использует битовые карты.
Хеш — индекс, позволяющий искать данные по точным совпадениям.
Полнотекстовый — индекс, который позволяет быстро ориентироваться в текстовых данных и поддерживает сложные запросы.
Синтаксис простейшего индекса: CREATE INDEX index_name ON table_name (column_name). Уникальный индекс создается по такому алгоритму: CREATE UNIQUE INDEX unique_order_idx.
Если нужно изменить или удалить индекс, пригодится алгоритм:
При работе с индексами учитывайте, что у каждой базы данных своя специфика и свои синтаксисы. Доступные возможности оптимизации лучше уточнять из документации по вашей базе.
Заключение
Правильное связывание таблиц с помощью ключей и операторов JOIN — фундаментальный навык для работы с реляционными базами данных. Освоение этих инструментов позволяет не только эффективно извлекать и объединять разрозненные данные в целостную информацию, но и закладывать основы производительности, целостности и масштабируемости вашей системы.
Понимание различий между типами связей и JOIN-операций превращает сложные задачи по работе с данными в структурированный и предсказуемый процесс, что в конечном итоге является ключом к созданию надежных и мощных приложений.