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

| рубрика: Программирование | автор: st
Метки: ,

Сергей Тарасов, апрель 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
Бендер Остап Сулейманович Рога и копыта
Бендер Остап Сулейманович НИИ ЧАВО
Гарин Петр Петрович Рога и копыта
Гарин Петр Петрович НИИ ЧАВО
Привалов Александр Иванович Рога и копыта
Привалов Александр Иванович НИИ ЧАВО

blog comments powered by Disqus