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

Пишет Serguei_Tarassov,

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

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