Соединение нескольких таблиц в SQL

admin

3 года назад

Краткая инструкция по соединению нескольких таблиц в базе данных, которые позволят связать и вывести необходимые данные с помощью SQL-запросов. Подходит как для MariaDB/MySQL, так и для PostgreSQL.

К примеру, имеется у нас самая простая примитивная база данных contracts для ведения списка заключённых договоров с контрагентами, в которой содержатся 3 таблицы с данными:

contracts со столбцами id (порядковый номер), contract_id (номер договора), org_id (id компании-контрагента), serv_id (id оказываемой услуги), contract_date_start (дата заключения договора) и contract_date_end (дата окончания действия договора);
contractors со столбцами company_id (порядковый номер компании-контрагента), company_name (наименование компании), company_address (адрес компании), responsible_person (ответственное лицо) и phone_number (телефон компании или ответственного лица);
services со столбцами service_id (порядковый номер услуги) и service_type (вид оказываемой услуги).

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

Самый простой способ связи таблиц — внутреннее соединение с помощью оператора INNER JOIN.

Синтаксис выглядит следующим образом:

SELECT 
	column1, column2, column5, columnN 
FROM 
	table1 
	INNER JOIN table2
	ON column2 = column3;

То есть, данные будут выводиться из таблиц table1 и table2, а оператор ON говорит о том, столбец column2 из таблицы table1 будет равен column3 из таблицы table2.

Таким образом, мы соединили таблицы table1 и table2 и настроили между ними связь при помощи столбцов column2 и column3.

Из имеющегося примера выше, соединим две таблицы contracts и contractors и настроим связь колонок org_id с company_id:

SELECT 
	contract_id, company_name, contract_date_start, contract_date_end 
FROM 
	contracts 
	INNER JOIN contractors 
	ON org_id = company_id;

Результат:

Также можем усложнить задачу, соединив три таблицы:

SELECT 
	contract_id, company_name, service_type, contract_date_start, contract_date_end 
FROM 
	contracts 
	INNER JOIN contractors 
	ON org_id = company_id 
	INNER JOIN services 
	ON serv_id = service_id;

В данном запросе используются две связи столбцов: org_id с company_id и serv_id с service_id.

Результат:

Как видно из скриншотов выше, у внутреннего соединения есть недостаток, связанный с исключением вывода результатов из-за несоответствия данных связей. Здесь нам поможет внешнее соединение OUTER JOIN. Различают два вида внешнего соединения: левое LEFT OUTER JOIN и правое RIGHT OUTER JOIN. Отличаются они тем, в левом соединении в выводе добавятся пустые или неиспользованные строки таблицы слева, а в правом — справа.

Для левого соединения синтаксис выглядит следующим образом:

SELECT 
	column1, column2, column5, columnN 
FROM 
	table1 
	LEFT OUTER JOIN table2
	ON column2 = column3;

а для правого — так:

SELECT 
	column1, column2, column5, columnN 
FROM 
	table1 
	RIGHT OUTER JOIN table2
	ON column2 = column3;

Из имеющегося примера выше, склеим две таблицы contracts и contractors с помощью левого и правого внешнего соединения и настроим связь колонок org_id с company_id:

SELECT 
	contract_id, company_name, contract_date_start, contract_date_end 
FROM 
	contracts 
	LEFT OUTER JOIN contractors 
	ON org_id = company_id;

SELECT 
	contract_id, company_name, contract_date_start, contract_date_end 
FROM 
	contracts 
	RIGHT OUTER JOIN contractors 
	ON org_id = company_id;

Из скриншотов видно, что при использовании левого внешнего соединения добавился номер договора (contract_id) 22/0188, но наименование компании (company_name) пустое, так как этот договор не привязан ни к одной из компаний, содержащихся в БД. При использовании правого соединения — наоборот, появилось наименование компании (company_name) Лучшие из лучших, но нет ни одного договора в БД, к которому привязан этот контрагент.

Результаты левого и правого внешнего соединения можно объединить с помощью оператора UNION.

Для такого объединения синтаксис будет выглядеть так:

(SELECT 
	column1, column2, column5, columnN 
FROM 
	table1 
	LEFT OUTER JOIN table2
	ON column2 = column3)
	
UNION

(SELECT 
	column1, column2, column5, columnN 
FROM 
	table1 
	RIGHT OUTER JOIN table2
	ON column2 = column3);

Для нашего примера объединим левое и правое внешние соединения следующим образом:

(SELECT 
	contract_id, company_name, contract_date_start, contract_date_end 
FROM 
	contracts 
	LEFT OUTER JOIN contractors 
	ON org_id = company_id)

UNION

(SELECT 
	contract_id, company_name, contract_date_start, contract_date_end 
FROM 
	contracts 
	RIGHT OUTER JOIN contractors 
	ON org_id = company_id);

При использовании оператора UNION ALL левое и правое соединения будут показаны отдельно друг от друга, но в одной таблице:

(SELECT 
	contract_id, company_name, contract_date_start, contract_date_end 
FROM 
	contracts 
	LEFT OUTER JOIN contractors 
	ON org_id = company_id)

UNION ALL

(SELECT 
	contract_id, company_name, contract_date_start, contract_date_end 
FROM 
	contracts 
	RIGHT OUTER JOIN contractors 
	ON org_id = company_id);

Для написания данной статьи были использованы следующие скрипты:

для СУБД PostgreSQL

CREATE DATABASE contracts OWNER db_user;

\c contracts;

CREATE TABLE contractors (
company_id SERIAL PRIMARY KEY,
company_name TEXT NOT NULL,
company_address TEXT NOT NULL,
responsible_person TEXT NOT NULL,
phone_number TEXT NOT NULL
);

CREATE TABLE services (
service_id SERIAL PRIMARY KEY,
service_type TEXT NOT NULL
);

CREATE TABLE contracts (
id SERIAL PRIMARY KEY,
contract_id TEXT NOT NULL,
org_id INTEGER DEFAULT 0,
serv_id INTEGER DEFAULT 0,
contract_date_start DATE,
contract_date_end DATE
);

INSERT INTO contractors (company_id, company_name, company_address, responsible_person, phone_number) VALUES (1, 'Титаны', 'г. Москва, Тверская ул., д. 17', 'Сидоров Василий Петрович', '+7 (916) 123-45-67');
INSERT INTO contractors (company_id, company_name, company_address, responsible_person, phone_number) VALUES (2, 'Гиганты', 'г. Москва, Тверская ул., д. 15', 'Иванов Тимофей Константинович', '+7 (926) 321-45-99');
INSERT INTO contractors (company_id, company_name, company_address, responsible_person, phone_number) VALUES (3, 'МегаКонтакт', 'г. Москва, Дмитровское шоссе, д. 112', 'Пупкин Василий Александрович', '+7 (909) 766-55-35');
INSERT INTO contractors (company_id, company_name, company_address, responsible_person, phone_number) VALUES (4, 'Лучшие из лучших', 'г. Москва, Ленинградское шоссе, д. 15', 'Петров Иван Андреевич', '+7 (910) 452-12-15');
INSERT INTO contractors (company_id, company_name, company_address, responsible_person, phone_number) VALUES (5, 'Наша забота', 'г. Москва, Дмитровское шоссе, д. 112', 'Лукашин Вениамин Владимирович', '+7 (903) 767-85-25');

INSERT INTO services (service_id, service_type) VALUES (1, 'ИТ-услуги, обслуживание оргтехники');
INSERT INTO services (service_id, service_type) VALUES (2, 'Поддержка видеонаблюдения и сигнализации');
INSERT INTO services (service_id, service_type) VALUES (3, 'Услуги телефонной связи');
INSERT INTO services (service_id, service_type) VALUES (4, 'Интернет-провайдеры');
INSERT INTO services (service_id, service_type) VALUES (5, 'Поставка расходных материалов');
INSERT INTO services (service_id, service_type) VALUES (6, 'Поставка питьевой воды');

INSERT INTO contracts (id, contract_id, org_id, serv_id, contract_date_start, contract_date_end) VALUES (1, '19/0008', 2, 3, '2019-01-15', '2022-12-29');
INSERT INTO contracts (id, contract_id, org_id, serv_id, contract_date_start, contract_date_end) VALUES (2, '20/0057', 1, 2, '2020-02-08', '2022-11-30');
INSERT INTO contracts (id, contract_id, org_id, serv_id, contract_date_start, contract_date_end) VALUES (3, '21/0148', 3, 1, '2021-01-12', '2022-07-31');
INSERT INTO contracts (id, contract_id, org_id, serv_id, contract_date_start, contract_date_end) VALUES (4, '22/0175', 5, 8, '2021-03-29', '2023-05-31');
INSERT INTO contracts (id, contract_id, org_id, serv_id, contract_date_start, contract_date_end) VALUES (5, '22/0188', 7, 4, '2022-04-18', '2023-10-31');

для СУБД MariaDB/MySQL

CREATE DATABASE contracts;

GRANT ALL PRIVILEGES ON contracts.* to 'db_user'@'localhost';
FLUSH PRIVILEGES;

USE contracts;

CREATE TABLE contractors (
company_id int(8) unsigned NOT NULL auto_increment,
company_name varchar(255) NOT NULL default ' ',
company_address varchar(255) NOT NULL default ' ',
responsible_person varchar(255) NOT NULL default ' ',
phone_number varchar(32) NOT NULL default ' ',

PRIMARY KEY (company_id),
UNIQUE KEY company_name (company_name)
) ENGINE=MyISAM COMMENT='Company Data';

CREATE TABLE services (
service_id int(8) unsigned NOT NULL auto_increment,
service_type varchar(255) NOT NULL default ' ',

PRIMARY KEY (service_id),
UNIQUE KEY service_type (service_type)
) ENGINE=MyISAM COMMENT='Information about services';

CREATE TABLE contracts (
id int(8) unsigned NOT NULL auto_increment,
contract_id varchar(32) NOT NULL default ' ',
org_id int(8) default 0,
serv_id int(8) default 0,
contract_date_start DATE,
contract_date_end DATE,

PRIMARY KEY (id),
UNIQUE KEY service_type (contract_id)
) ENGINE=MyISAM COMMENT='List of contracts';

INSERT INTO contractors (company_id, company_name, company_address, responsible_person, phone_number) VALUES (NULL, 'Титаны', 'г. Москва, Тверская ул., д. 17', 'Сидоров Василий Петрович', '+7 (916) 123-45-67');
INSERT INTO contractors (company_id, company_name, company_address, responsible_person, phone_number) VALUES (NULL, 'Гиганты', 'г. Москва, Тверская ул., д. 15', 'Иванов Тимофей Константинович', '+7 (926) 321-45-99');
INSERT INTO contractors (company_id, company_name, company_address, responsible_person, phone_number) VALUES (NULL, 'МегаКонтакт', 'г. Москва, Дмитровское шоссе, д. 112', 'Пупкин Василий Александрович', '+7 (909) 766-55-35');
INSERT INTO contractors (company_id, company_name, company_address, responsible_person, phone_number) VALUES (NULL, 'Лучшие из лучших', 'г. Москва, Ленинградское шоссе, д. 15', 'Петров Иван Андреевич', '+7 (910) 452-12-15');
INSERT INTO contractors (company_id, company_name, company_address, responsible_person, phone_number) VALUES (NULL, 'Наша забота', 'г. Москва, Дмитровское шоссе, д. 112', 'Лукашин Вениамин Владимирович', '+7 (903) 767-85-25');

INSERT INTO services (service_id, service_type) VALUES (NULL, 'ИТ-услуги, обслуживание оргтехники');
INSERT INTO services (service_id, service_type) VALUES (NULL, 'Поддержка видеонаблюдения и сигнализации');
INSERT INTO services (service_id, service_type) VALUES (NULL, 'Услуги телефонной связи');
INSERT INTO services (service_id, service_type) VALUES (NULL, 'Интернет-провайдеры');
INSERT INTO services (service_id, service_type) VALUES (NULL, 'Поставка расходных материалов');
INSERT INTO services (service_id, service_type) VALUES (NULL, 'Поставка питьевой воды');

INSERT INTO contracts (id, contract_id, org_id, serv_id, contract_date_start, contract_date_end) VALUES (NULL, '19/0008', 2, 3, '2019-01-15', '2022-12-29');
INSERT INTO contracts (id, contract_id, org_id, serv_id, contract_date_start, contract_date_end) VALUES (NULL, '20/0057', 1, 2, '2020-02-08', '2022-11-30');
INSERT INTO contracts (id, contract_id, org_id, serv_id, contract_date_start, contract_date_end) VALUES (NULL, '21/0148', 3, 1, '2021-01-12', '2022-07-31');
INSERT INTO contracts (id, contract_id, org_id, serv_id, contract_date_start, contract_date_end) VALUES (NULL, '22/0175', 5, 8, '2021-03-29', '2023-05-31');
INSERT INTO contracts (id, contract_id, org_id, serv_id, contract_date_start, contract_date_end) VALUES (NULL, '22/0188', 7, 4, '2022-04-18', '2023-10-31');