SQL-Урок 11. Розширене поєднання таблиць (OUTER JOIN)

В попередньому розділі ми розглянули самі прості способи об'єднання таблиць - за допомогою речень WHERE та INNER JOIN. Ці об'єднання називаються внутрішніми об'єднаннями або об'єднаннями по еквівалентності. Проте SQL має у своєму арсеналі набагато більше можливостей поєднати таблиці, а саме існують також й інші види об'єднань: зовнішні об'єднання, природні об'єднання та самооб'єднання. Але для початку розглянемо, яким чином ми можемо присвоювати таблицям псевдоніми, оскільки, в подальшому, ми будемо змушені використовувати повні назви полів (Таблиця.Поле), якими без скорочень буде дуже важко оперувати через їх велику довжину.

1. Використання псевдонімів таблиць

В попередньому розділі ми дізналися, як можна використовувати псевдоніми для посилання на певні поля таблиці або на розрахункові поля. SQL так само надає нам можливість використовувати псевдоніми замість імен таблиць. Це надає нам такі переваги, як більш короткий синтаксис SQL та дозволяє багато раз використовувати одну й ту ж таблицю в операторі SELECT.

SELECT Seller_name, SUM(Amount) AS Sum1
FROM Sellers AS S, Sumproduct AS SP
WHERE S.City = SP.City
GROUP BY Seller_name

Ми відобразили загальну суму реалізованого товару по кожному продавцю. В нашому SQL запиті ми використали такі псевдоніми: для розрахункового поля SUM(Amount) псевдонім Sum1, для таблиці Sellers псевдонім S та для Sumproduct псевдонім SP. Зауважимо, що псевдоніми таблиць можуть бути застосовані також і в інших реченнях, як ORDER BY, GROUP BY та інших.

2. Самооб'єднання

Розглянемо приклад. Припустимо, нам потрібно дізнатися адресу продавців, які торгують в тій самій країні, що і John Smith. Для цього створимо такий запит:

SELECT City, Country, Seller_name
FROM Sellers
WHERE Country = (SELECT Country FROM Sellers WHERE Seller_name = 'John Smith')

Також, цю задачу ми можемо вирішити і через самооб'єднання, прописавши наступний код:

SELECT S1.Address, S1.City, S1.Country, S1.Seller_name
FROM Sellers AS S1, Sellers AS S2
WHERE S1.Country = S2.Country AND S2.Seller_name = 'John Smith'

Для вирішення цієї задачі використовувалися псевдоніми. Перший раз для таблиці Sellers присвоїли псевдонім S1, другий раз - псевдонім S2. Після цього ці псевдоніми можна застосовувати в якості імен таблиць. В операторі WHERE ми до назви кожного поля додаємо префікс S1, для того, щоб СУБД розуміла поля якої таблиці потрібно виводити (оскільки ми з однієї таблиці зробили дві віртуальні). Речення WHERE спочатку об'єднує таблиці, а потім фільтрує дані другої таблиці по полю Seller_name, щоби повернути лише необхідні значення.

Самооб'єднання часто використовують для заміни підзапитів, які вибирають дані з тієї ж таблиці, що і зовнішній оператор SELECT. Хоча кінцевий результат виходить тим самим, багато СУБД опрацьовують об'єднання набагато швидше ніж підзапити. Варто поекспериментувати, щоби визначити, який запит працює швидше.

3. Природнє об'єднання

Природнє об'єднання - це об'єднання, в якому ви вибираєте тільки ті стовпці, які не повторюються. Зазвичай це робиться за допомогою запису (SELECT *) для однієї таблиці і вказанням переліку полів - для решти таблиць. Наприклад:

SELECT SP.*, S.Country
FROM Sumproduct AS SP, Sellers AS S
WHERE SP.City = S.City

В цьому прикладі метасимвол (*) використовується тільки для першої таблиці. Всі решта стовпці вказані явно, тому дублікати стовпців не вибираються.

4. Зовнішнє об'єднання (OUTER JOIN)

Зазвичай, при об'єднанні зв'язують рядки однієї таблиці з відповідними рядками іншої, проте в деяких випадках може знадобитися включати в результат рядки, які не мають пов'язаних рядків в іншій таблиці (тобто вибираються геть усі рядки з однієї таблиці та додаються лише пов'язані рядки з іншої). Об'єднання такого типу називається зовнішнім. Для цього використовуються ключові слова OUTER JOIN ... ON ... з приставкою LEFT або RIGHT. Розглянемо приклад, попередньо добавивши в таблицю Sellers нового продавця - Semuel Piter, який ще немає продаж:

SELECT Seller_name, SUM(Quantity) AS Qty
FROM Sellers LEFT OUTER JOIN Sumproduct ON Sellers.City=Sumproduct.City
GROUP BY Seller_name

Даним запитом ми витягнули перелік усіх продавців в базі та підрахували для них загальну кількість проданого товару за усі місяці. Бачимо що по новому продавцю Semuel Piter відсутні продажі. Якби ми використали внутрішнє об'єднання, то нового продавця ми б не побачили, оскільки він немає записів в таблиці Sumproduct. Ми також можемо змінювати напрямок поєднання не лише прописуючи LEFT або RIGHT, але й просто змінюючи порядок таблиць (тобто такі два записи будуть давати однаковий результат: Sellers LEFT OUTER JOIN Sumproduct та Sumproduct RIGHT OUTER JOIN Sellers).

Також деякі СУБД дозволяють здійснювати зовнішнє об'єднання за спрщеним записом, використовуючи знаки *= та =*, що відповідає LEFT OUTER JOIN та RIGHT OUTER JOIN відповідно. Таким чином попередній запит можна було б переписати так:

SELECT Seller_name, SUM(Quantity) AS Qty
FROM Sellers, Sumproduct
WHERE Sellers.City *= Sumproduct.City

Нажаль Access не підтримує скорочений запис для зовнішнього об'єднання.

5. Повне зовнішнє об'єднання (FULL OUTER JOIN)

Також існує й інший тип зовнішнього об'єднання - повне зовнішнє об'єднання, яке відображає усі рядки з обидвох таблиць та пов'язує лише ті, які можуть бути пов'язані. Синтаксис повного зовнішнього об'єднання наступний:

SELECT Seller_name, Product
FROM Sellers FULL OUTER JOIN Sumproduct ON Sellers.City=Sumproduct.City

Знову ж, повне зовнішнє об'єднання не підтримують такі СУБД: Access, MySQL, SQL Server та Sybase. Як обійти цю несправедливість, ми розглянемо в наступному розділі.

Статті по темі:

  • SQL-Урок 12. Комбіновані запити (UNION)