Лекція 6. Об'єднання таблиць у запитах

1. Об’єднання таблиць в запитах

Зв’язування за рівністю. Як зазначалось раніше (в курсі «Організація баз даних»), ключові слова SELECT і FROM є обов’язковими елементами оператора SQL, який визначає запит.  Для зв’язування таблиць необхідним елементом оператора SQL є ключове слово WHERE. Імена таблиць для зв’язування вказуються після ключового слова FROM, а сам зв’язок визначається у виразі після ключового слова WHERE.

Зв’язування за рівністю називають також внутрішнім зв’язуванням (INNER JOIN). При такому зв’язку таблиці зв’язуються по загальному полю (стовпцю), який або в одній із таблиць, або в обох таблицях є ключовим.

Синтаксис оператора, який визначає зв’язування таблиць за рівністю, має бути таким.

SELECT Tab1.pole1, Tab2.pole2…

FROM Tab1, Tab2 [,…]

WHERE Tab1.pole1 =Tab2.pole1 [AND …]

або

SELECT Tab1.pole1, Tab2.pole2

FROM Tab1 INNER JOIN Tab2 ON

Tab1.pole1=Tab2.pole1

Приклад.

USE pubs

SELECT ytd_sales AS Sales,

   authors.au_fname + ' '+ authors.au_lname AS Author,

   ToAuthor = (ytd_sales * royalty) / 100,

   ToPublisher = ytd_sales - (ytd_sales * royalty) / 100

FROM titles INNER JOIN titleauthor

   ON titles.title_id = titleauthor.title_id INNER JOIN authors

   ON titleauthor.au_id = authors.au_id

ORDER BY Sales DESC, Author ASC

Зв’язування за нерівністю. При такому зв’язуванні (NON-EQUIJOINS) дві або більше таблиць об’єднуються за умови нерівності значення стовпця однієї таблиці значенню стовпця іншої таблиці. Синтаксис відповідної частини оператора такий.

SELECT Tab1.pole1, Tab2.pole2…

FROM Tab1, Tab2 [,…]

WHERE Tab1.pole1 <>Tab2.pole1 [AND …]

Зовнішнє зв’язування. Таке зв’язування (OUTER JOIN) використовується тоді, коли необхідно отримати усі записи однієї із таблиць, навіть, якщо деякі її записи не мають відповідних записей в іншій таблиці.

В реалізації мови SQL в MS SQL SERVER зовнішнє зв’язування  представлено трьома типами: ліве зовнішнє зв’язування LEFT OUTER JOIN, праве зовнішнє зв’язування RIGHT OUTER JOIN, повне зовнішнє зв’язування FULL OUTER JOIN. При цьому розрізняють ліву таблицю (таку, яка вказується першою після ключового слова FROM) та праву таблицю (таку, яка вказується другою). Загальний синтаксис в MS SQL SERVER такий.

SELECT <SELECT LIST>

FROM <THE LEFT TABLE> <LEFT | RIGHT | FULL> [OUTER] JOIN <THE RIGHT TABLE> ON <JOIN CONDITION>

Також можна використовувати символи *= для LEFT JOIN та =* для RIGHT JOIN після ключового слова WHERE.

 Якщо використовується конструкція LEFT OUTER JOIN, до результату увійдуть всі записи лівої таблиці та записи правої таблиці, які відповідають умові пошуку.  Якщо використовується конструкція RIGHT OUTER JOIN, до результату увійдуть всі записи правої таблиці та записи лівої таблиці, які відповідають умові пошуку. Нарешті, конструкція FULL OUTER JOIN дозволяє отримати всі записи обох таблиць.

Перехресне зв’язування. Таке зв’язування (CROSS JOIN) призводить до отримання декартового добутку усіх записів всіх таблиць. Синтаксис такий.

SELECT pole1, pole2, pole3…

FROM table1 CROSS JOIN table2.

2. Приклад 1

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors a LEFT OUTER JOIN publishers p

   ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Результат буде таким:

au_fname             au_lname                       pub_name         

-------------------- ------------------------------ -----------------

Reginald             Blotchet-Halls                 NULL

Michel               DeFrance                       NULL

Innes                del Castillo                   NULL

Ann                  Dull                           NULL

Marjorie             Green                          NULL

Morningstar          Greene                         NULL

Burt                 Gringlesby                     NULL

Sheryl               Hunter                         NULL

Livia                Karsen                         NULL

Charlene             Locksley                       NULL

Stearns              MacFeather                     NULL

Heather              McBadden                       NULL

Michael              O'Leary                        NULL

Sylvia               Panteley                       NULL

Albert               Ringer                         NULL

Anne                 Ringer                         NULL

Meander              Smith                          NULL

Dean                 Straight                       NULL

Dirk                 Stringer                       NULL

Johnson              White                          NULL

Akiko                Yokomoto                       NULL

Abraham              Bennet                         Algodata Infosystems

Cheryl               Carson                         Algodata Infosystems

(23 row(s) affected)

3. Приклад 2

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors AS a RIGHT OUTER JOIN publishers AS p

   ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Результат такий:

au_fname             au_lname                 pub_name            

-------------------- ------------------------ --------------------

Abraham              Bennet                   Algodata Infosystems

Cheryl               Carson                   Algodata Infosystems

NULL                 NULL                     Binnet & Hardley

NULL                 NULL                     Five Lakes Publishing

NULL                 NULL                     GGG&G

NULL                 NULL                     Lucerne Publishing

NULL                 NULL                     New Moon Books

NULL                 NULL                     Ramona Publishers

NULL                 NULL                     Scootney Books

(9 row(s) affected)

4. Приклад 3

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors a FULL OUTER JOIN publishers p

   ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Результат такий:

au_fname             au_lname                     pub_name            

-------------------- ---------------------------- --------------------

Reginald             Blotchet-Halls               NULL

Michel               DeFrance                     NULL

Innes                del Castillo                 NULL

Ann                  Dull                         NULL

Marjorie             Green                        NULL

Morningstar          Greene                       NULL

Burt                 Gringlesby                   NULL

Sheryl               Hunter                       NULL

Livia                Karsen                       NULL

Charlene             Locksley                     NULL

Stearns              MacFeather                   NULL

Heather              McBadden                     NULL

Michael              O'Leary                      NULL

Sylvia               Panteley                     NULL

Albert               Ringer                       NULL

Anne                 Ringer                       NULL

Meander              Smith                        NULL

Dean                 Straight                     NULL

Dirk                 Stringer                     NULL

Johnson              White                        NULL

Akiko                Yokomoto                     NULL

Abraham              Bennet                       Algodata Infosystems

Cheryl               Carson                       Algodata Infosystems

NULL                 NULL                         Binnet & Hardley

NULL                 NULL                         Five Lakes Publishing

NULL                 NULL                         GGG&G

NULL                 NULL                         Lucerne Publishing

NULL                 NULL                         New Moon Books

NULL                 NULL                         Ramona Publishers

NULL                 NULL                         Scootney Books