Типы соединений в SQL. Шпаргалка

Пишет st,

Материал этой заметки послужил основой для одной глав книги "СУБД для программиста. Базы данных изнутри".

Лучше один раз увидеть, чем 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)

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

Компании (companies)

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 <span lang="en-us">LEFT</span> 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
Бендер Остап Сулейманович Рога и копыта
Бендер Остап Сулейманович НИИ ЧАВО
Гарин Петр Петрович Рога и копыта
Гарин Петр Петрович НИИ ЧАВО
Привалов Александр Иванович Рога и копыта
Привалов Александр Иванович НИИ ЧАВО

Сергей Тарасов, апрель 2006. Заметка также опубликована в журнале "Мир ПК" №12-2007

Комментарии

Типы соединений в SQL. Шпаргалка

Пишет Дмитрий (не проверено),

Опечатка в разделе "Полное соединение".

Написано "Также проводим соединение по неключевому атрибуту - номеру телефона".
А в примере соединение приведено для "ON contacts.company_id = companies.company_id" вместо телефона "ON contacts.phone = companies.phone".