Типы соединений в SQL. Шпаргалка — Механика софтостроения

Сергей Тарасов, апрель 2006.

Заметка также опубликована в журнале "Мир ПК" №12-2007. Материал этой заметки послужил основой для одной глав книги "СУБД для программиста. Базы данных изнутри".

Лучше один раз увидеть, чем 100 раз услышать. Для чего служат различные виды соединений таблиц в SQL-запросах проще всего запомнить на практике.

В примере будем использовать две связанные таблицы: контактные лица и компании. Код приведен для MS SQL Server 2000 и выше.

CREATE TABLE companies (
  company_id INT NOT NULL,
  company_name VARCHAR(64) NOT NULL,
  phone VARCHAR(16) NULL,
  CONSTRAINT pk_companies
    PRIMARY KEY (company_id)
)

CREATE TABLE contacts (
  contact_id INT NOT NULL,
  contact_name VARCHAR(64),
  phone VARCHAR(16) NULL,
  company_id INT NULL,
  CONSTRAINT pk_contacts
    PRIMARY KEY (contact_id),
  CONSTRAINT fk_contact_company
    FOREIGN KEY (company_id)
    REFERENCES companies(company_id)
)
GO

Заполним таблицы данными.

INSERT INTO companies
  VALUES (1, 'Рога и копыта', null)
INSERT INTO companies
  VALUES (2, 'НИИ ЧАВО', '322-223')

INSERT INTO contacts
  VALUES (1, 'Бендер Остап Сулейманович', null, 1)
INSERT INTO contacts
  VALUES (2, 'Гарин Петр Петрович', '322-223', null)
INSERT INTO contacts
  VALUES (3, 'Привалов Александр Иванович', '322-223', 2)

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

company_id	company_name	phone
1	Рога и копыта	NULL
2	НИИ ЧАВО	322-223

Контакты (contacts)

contact_id	contact_name	phone	company_id
1	Бендер Остап Сулейманович	NULL	1
2	Гарин Петр Петрович	322-223	NULL
3	Привалов Александр Иванович	322-223	2

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

Обычное эквисоединение

Оно же внутреннее (inner) соединение.

SELECT contact_name, company_name
FROM contacts
    INNER JOIN companies ON contacts.company_id = companies.company_id
ORDER BY contact_name

contact_name	company_name
Бендер Остап Сулейманович	Рога и копыта
Привалов Александр Иванович	НИИ ЧАВО

Внешнее соединение слева

SELECT contact_name, company_name
FROM contacts
    LEFT OUTER JOIN companies ON contacts.company_id = companies.company_id
ORDER BY contact_name

contact_name	company_name
Бендер Остап Сулейманович	Рога и копыта
Гарин Петр Петрович	NULL
Привалов Александр Иванович	НИИ ЧАВО

Внешнее соединение справа

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

SELECT contact_name, company_name
FROM contacts
    RIGHT OUTER JOIN companies ON contacts.phone = companies.phone
ORDER BY contact_name

contact_name	company_name
NULL	Рога и копыта
Гарин Петр Петрович	НИИ ЧАВО
Привалов Александр Иванович	НИИ ЧАВО

Выполним для чистоты эксперимента еще и внешнее соединение слева по тому же атрибуту

SELECT contact_name, company_name
FROM contacts
    LEFT OUTER JOIN companies ON contacts.phone = companies.phone
ORDER BY contact_name

contact_name	company_name
Бендер Остап Сулейманович	NULL
Гарин Петр Петрович	НИИ ЧАВО
Привалов Александр Иванович	НИИ ЧАВО

Полное соединение

SELECT contact_name, company_name
FROM contacts
    FULL OUTER JOIN companies ON contacts.phone = companies.phone
ORDER BY contact_name

Также проводим по неключевому атрибуту - номеру телефона. Как нетрудно убедиться, является объединением множеств, полученных внешними соединениями слева и справа.

contact_name	company_name
NULL	Рога и копыта
Бендер Остап Сулейманович	NULL
Гарин Петр Петрович	НИИ ЧАВО
Привалов Александр Иванович	НИИ ЧАВО

Перекрестное соединение

Оно же декартово произведение в терминах реляционной алгебры

SELECT contact_name, company_name
FROM contacts CROSS JOIN companies
ORDER BY contact_name

contact_name	company_name
Бендер Остап Сулейманович	Рога и копыта
Бендер Остап Сулейманович	НИИ ЧАВО
Гарин Петр Петрович	Рога и копыта
Гарин Петр Петрович	НИИ ЧАВО
Привалов Александр Иванович	Рога и копыта
Привалов Александр Иванович	НИИ ЧАВО