Материал этой заметки послужил основой для одной глав книги "СУБД для программиста. Базы данных изнутри".
Лучше один раз увидеть, чем 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 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 |
Бендер Остап Сулейманович | Рога и копыта |
Бендер Остап Сулейманович | НИИ ЧАВО |
Гарин Петр Петрович | Рога и копыта |
Гарин Петр Петрович | НИИ ЧАВО |
Привалов Александр Иванович | Рога и копыта |
Привалов Александр Иванович | НИИ ЧАВО |
Сергей Тарасов, апрель 2006. Заметка также опубликована в журнале "Мир ПК" №12-2007
Комментарии
Типы соединений в SQL. Шпаргалка
Пишет Дмитрий (не проверено),
Опечатка в разделе "Полное соединение".
Написано "Также проводим соединение по неключевому атрибуту - номеру телефона".
А в примере соединение приведено для "ON contacts.company_id = companies.company_id" вместо телефона "ON contacts.phone = companies.phone".
Спасибо
Пишет st,
Спасибо, исправил.