ВГОРУ

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

YouLibreCalc for Excel logo

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


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

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

Run SQLSELECT 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. Самопоєднання (SELF JOIN)

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

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

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

Run SQLSELECT 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 *) для однієї таблиці і вказанням переліку полів - для решти таблиць. Наприклад:

Run SQLSELECT 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, який ще немає продаж:

Run SQLSELECT Seller_name, SUM(Quantity) AS Qty
FROM Sellers2 
LEFT OUTER JOIN Sumproduct 
ON Sellers2.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

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

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

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

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

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