Связи между таблицами SQL — обзор основных отношений и типов присоединения

Связи в базе данных MS SQL помогают нормализировать БД, настроить отношение между данными таблиц и сделать эффективные выборки данных. Главное — понять, как настраивать и использовать связи между таблицами MS SQL. Это необходимое условие для работы с любой БД. В статье рассмотрим, какие есть типы связей между таблицами.

Зачем нужны связи между таблицами в базе данных

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

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

Вот зачем нужны связи таблиц в базе данных:

• Уменьшают количество дублированных данных — одно и то же значение, например имя клиента, хранится в одном месте.
• Данные удобно обновляются — если они изменяются в одной таблице, это автоматически отражается в связанных с ней таблицах.
• Повышается производительность — можно извлекать только нужные данные, а не перебирать всё.

Без связей данные превращаются в неуправляемый массив, с которым трудно работать, особенно если проект растет. Связи между таблицами SQL — это каркас, с которым проще хранить, изменять и анализировать информацию в базе.

Что такое ключи (keys)

Ключи — это специальные поля или комбинации полей в таблице, которые делают записи уникальными и задают связи между таблицами. С их помощью можно управлять целостностью данных, исключать дублирование и создавать логические отношения.

Есть несколько типов ключей.

Первичный ключ (Primary Key)

Первичный ключ — это столбец или комбинация столбцов, которые однозначно идентифицируют каждую запись в таблице. Он:

• не может содержать повторяющихся значений;
• не может быть пустым (NULL);
• часто используется для связи с другими таблицами как внешний ключ.

Пример: поле CustomerID в таблице клиентов.

Составной ключ (Composite Key)

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

Пример: таблица заказов с полями OrderID и ProductID, которые вместе образуют уникальную комбинацию.

Уникальный ключ (Unique Key)

Уникальный ключ гарантирует, что значения в столбце (или их комбинации) не будут повторяться. В отличие от первичного ключа, уникальный ключ может принимать значение NULL, но только один раз.

Пример: поле Email в таблице пользователей — каждый email должен быть уникальным.

Внешний ключ (Foreign Key)

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

Пример: поле CustomerID в таблице заказов ссылается на CustomerID в таблице клиентов — это связь «многие к одному».

Типы соединения JOINs

Когда отношения между таблицами установлены, можно делать выборки данных из этих связанных таблиц.

Есть несколько механизмов соединения двух таблиц в запросе: это основные типы JOINs для MS SQL SERVER. И они практически всегда совпадают для всех реляционных СУБД.

Рассмотрим основные типы JOINs. Будем считать, что у нас есть левая и правая таблицы, которые соединяем через JOIN.

Есть несколько механизмов, которые позволяют соединить две таблицы в запросе. Например, Oracle содержит Natural Join, который соединяет колонки с одинаковыми именами в таблицах. Используется крайне редко.

Обо всех способах соединения вы узнаете на курсе «Аналитик данных». Уделяйте учебе всего два часа в день — и научитесь обновлять, удалять и добавлять записи, использовать разные соединения, анализировать большие объемы данных. Около 70% от объема курса — практика. Если будет сложно, помогут кураторы и наставники.

LEFT (OUTER) JOIN

Всегда выводите данные по левой таблице. Если правая таблица не содержит связанных данных, то выводите NULL для этих значений.

select *
from A left join B on A.ID = B.A_ID

RIGHT (OUTER) JOIN

Обратное от Left Join. Используют редко. Всегда можно переписать на Left Join — тогда запрос легче читать. В частных случаях бывает, что Right Join дает лучшую статистику выполнения и оптимизирует запрос.

A left join B = B right join A:

select *
from B right join A on A.ID = B.A_ID

INNER JOIN

Выводят значения для строки из левой таблицы, только если есть связанные данные в правой таблице. Часто используют, чтобы отфильтровать данные левой таблицы и выводить только те записи, по которым есть значения в правой.

select *
from A inner join B on A.ID = B.A_ID

-- аналог запроса на left join
select *
from A left join B on A.ID = B.A_ID
where B.ID is not null

CROSS JOIN

Это пересечение всех строк из левой таблицы со всеми строками правой таблицы.

select *
from A cross join B

FULL JOIN

Представьте, что это смешанное сочетание Left Join и Right Join. Сначала выводятся значения левой таблицы, а значения правой заполняются NULL, потом — наоборот.
Запрос выводит пересечение значений. Если нет пересечений, то выводит значения по A и B c NULL.

select *
from A full join B on A.ID = B.A_ID

Типы отношений между таблицами

Используем Foreign Key и JOINs и создадим реальный пример бизнес-задачи. А еще рассмотрим настройку связей между таблицами. Введем сущности Clinics, Doctors, Patients и Appointments:

• Доктор работает или не работает только в одной клинике.
• У доктора может быть вышестоящий менеджер.
• Пациент может обращаться в разные клиники к разным докторам.

Создадим таблицы БД пока без связей с суррогатными Primary Keys:

CREATE TABLE dbo.Clinics (
ID int,
Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT Clinics_PK PRIMARY KEY (ID)
);

CREATE TABLE dbo.Patients (
ID int,
Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT Patients_PK PRIMARY KEY (ID)
);

CREATE TABLE dbo.Doctors (
ID int,
Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Clinic_ID int NULL,
Manager_ID int NULL,
CONSTRAINT Doctors_PK PRIMARY KEY (ID)
);

CREATE TABLE dbo.Appointments (
ID int,
[Date] datetime2(0) NOT NULL,
Patient_ID int NOT NULL,
Doctor_ID int NOT NULL,
CONSTRAINT Appointments_PK PRIMARY KEY (ID)
);

Фейковые данные:

insert into dbo.Clinics values (1, 'First Clinic'), (2, 'Second Clinic')
insert into dbo.Doctors values (10, 'Doctor', 1, NULL), (11, 'Assistent Doctor', 1, 10), (12, 'Another Doctor', 2, NULL), (13, 'Retired Doctor', NULL, NULL), (15, 'Assist 2 Doctor', 1, 11)
insert into dbo.Patients values (100, 'First Patient'), (101, 'Second Patient')
insert into dbo.Appointments values (1000, GETDATE(), 100, 10), (1001, GETDATE(), 101, 10), (1002, GETDATE(), 100, 12)

Отношения «один к одному»

Используйте эту связь, когда значению из таблицы соответствует только одна запись из внешней таблицы. «Доктор может работать только в одной клинике». Можем предположить, что связь между Clinics и Doctors будет «один к одному»:

ALTER TABLE dbo.Doctors ADD CONSTRAINT FK_Doctors_ClinicID FOREIGN KEY (Clinic_ID) REFERENCES dbo.Clinics(ID);

select
d.Name,
c.Name
from
dbo.Doctors d
left join dbo.Clinics c on c.ID = d.Clinic_ID

Отношение «один ко многим»

Одной записи из таблицы соответствуют несколько записей из внешней. Данный тип связи очень распространен при построении схемы БД.
«Хотя доктор может принадлежать только одной клинике, клиники, в свою очередь, содержат штат докторов». Это отношение «один ко многим»:

select
c.Name,
d.Name
from
dbo.Clinics c
inner join dbo.Doctors d on d.Clinic_ID = c.ID;

Отношение «многие ко многим»

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

ALTER TABLE dbo.Appointments ADD CONSTRAINT FK_Appointments_DoctorID FOREIGN KEY (Doctor_ID) REFERENCES dbo.Doctors(ID);
ALTER TABLE dbo.Appointments ADD CONSTRAINT FK_Appointments_PatientID FOREIGN KEY (Patient_ID) REFERENCES dbo.Patients(ID);

Это довольно распространенный вопрос на собеседовании для SQL-разработчика. Если программист может на примере объяснить, как строится связь «многие ко многим», — это уже хороший показатель для интервьюера.
Как получить список посещений пользователя с указанием клиники и докторов:

select
p.Name,
a.[Date],
d.Name,
c.Name
from
dbo.Patients p
inner join dbo.Appointments a on a.Patient_ID = p.ID
inner join dbo.Doctors d on d.ID = a.Doctor_ID
inner join dbo.Clinics c on c.ID = d.Clinic_ID
where
p.ID = 100;

Связь с самим собой

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

Таблица Doctors содержит колонку Manager, в которой указано, кто из докторов является менеджером текущего доктора. Здесь связь на строку из той же таблицы докторов.

Как рекурсивно получить список докторов, у которых определенный доктор является вышестоящим менеджером:

with cte as (
select
d.ID,
d.Name
from
dbo.Doctors d
where
d.ID = 10
union all
select
d2.ID,
d2.Name
from
dbo.Doctors d2
inner join cte on cte.ID = d2.Manager_ID
)
select
*
from
cte

Самое важное о связях между таблицами

• 🟦 Связи между таблицами — основа реляционных баз данных. Они помогают структурировать данные, избежать дублирования и обеспечить целостность.
• 🟦 Ключи — Primary Key, Foreign Key и др. — это поля, которые определяют уникальность записей и задают отношения между таблицами.
• 🟦 Есть три основных типа связи: «один к одному» — каждая запись связана только с одной другой; «один ко многим» — одной записи соответствуют несколько других; «многие ко многим» — каждая запись связана с несколькими из другой таблицы, реализуется через промежуточную таблицу.
• 🟦 Типы JOIN позволяют извлекать данные из связанных таблиц.
• 🟦 Рекурсивная связь — связь с самим собой — полезна для иерархий, например для структуры подчиненности в таблице сотрудников.