Зовнішні об’єднання

У той час як внутрішні обєднання містять тільки перетин двох множин даних, зовнішні обєднання розширюють це безліч за рахунок невідповідних даних лівого або правого набору даних (рис 96)

Зовнішні обєднання вирішують істотну проблему для безлічі запитів, включаючи в результат дані, незалежно від їх відповідності У наведеному вище прикладі клієнт-замовлення ця проблема проілюстрована досить добре Якщо нам потрібно перерахувати всіх клієнтів разом із замовленнями, виконаними ними за останній час, то внутрішнє обєднання відсіє замовників, які вчинили покупки раніше початковій дати діапазону Цей тип помилки досить часто зустрічається в додатках баз даних

Рис 96 Зовнішнє обєднання включає не тільки відповідні один одному рядки двох джерел даних, а й невідповідні рядки поза перетину множин

Деякі рядки в результуючому наборі даних, сформованому зовнішнім обєднанням, будуть виглядати точно так само, як рядки з внутрішнього обєднання, – вони будуть містити стовпці з кожного джерела У той же час деякі рядки, що не знайшли відповідності на протилежній стороні обєднання, будуть містити лише стовпці зовнішньої таблиці – стовпці протилежного боку будуть містити порожні значення

При створенні запитів в конструкторі ви можете в будь-який момент змінити тип запиту з внутрішнього, прийнятого за замовчуванням, на зовнішній Це можна зробити за допомогою контекстного меню, а також вікна властивостей обєднання (рис 97) Конструктор запитів відмінно справляється з демонстрацією типу обєднання, використовуючи для цього спеціальні символи

У коді SQL зовнішнє обєднання оголошується за допомогою ключових слів left outer або right outer перед ключовим словом join (технічно ключове слово outer необовязкове)

SELECT *

FROM Tablel

LEFT|RIGHT [OUTER] JOIN Table2 ON Tablelcolumn = Table2column

Незважаючи на те що деякі слова в SQL є необовязковими (такі, як INNER або OUTER) або можуть замінюватися скороченнями (наприклад, ргос для procedure), проходження повного синтаксису підвищує читаність коду У той же час багато розробники опускають необовязковий синтаксис

Puc 97 У вікні властивостей обєднання відображаються стовпці, які беруть участь у ньому Тут можна змінити умову обєднання (=,>, <і т.п.) і додати ліву чи праву частину зовнішнього об'єднання (все рядки з таблиці Product, всі рядки з таблиці OrderDetails)

Зовсім не складно відрізнити зовнішнє праве обєднання від лівого У коді поняття ліве і праве відносяться до таблиці, всі записи якої будуть включені в результат, незалежно від знайдених відповідностей Ця таблиця (іноді звана керуючої) зазвичай перераховується в запиті першого з цієї причини ліве зовнішнє обєднання використовується частіше правого Я вважаю, що основну плутанину в питання відмінності правого зовнішнього обєднання від лівого вносить графічне представлення Вся справа в тому, що слова праве і ліве відносяться до порядку перерахування таблиць в коді SQL, а їх розташування на екрані в графічному поданні тільки вносить плутанину

Щоб наведений раніше як приклад запит клієнт-контакт повертав всіх замовників незалежно від наявності у них замовлення, потрібно лише замінити внутрішнє обєднання лівим зовнішнім обєднанням

SELECT ContactCode, OrderNumber FROM dboContact

LEFT OUTER JOIN dbo[Order]

ON [Order]ContactID = ContactContactID ORDER BY ContactCode

Дане ліве зовнішнє обєднання включить всі рядки з таблиці Contact і відповідні їм рядки з таблиці [Order] Скорочений результат цього запиту виглядає так:

Contact       [Order]

ContactCode        OrderNumber

106&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 10

107&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp NULL

108&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp NULL

Оскільки контакти з номерами 107 і 108 не мають відповідних їм рядків у таблиці [Order], в її стовпцях результуючого набору даних повертаються порожні значення

Мова Transact-SQL розширив синтаксис зовнішнього обєднання ANSI SQL-89 за н0ВІНка рахунок Додавання зірочки праворуч від знаку рівності в умові пропозиції

2005 where Незважаючи на те що цей синтаксис працює у версії SQL Server 2000,

у версії SQL Server 2005 він уже не підтримується Внутрішні обєднання ANSI SQL-89 продовжують працювати, однак зовнішні вимагають використання синтаксису ANSI SQL-92

Зовнішні обєднання і необовязкові зовнішні ключі

Зовнішні обєднання часто задіюють, якщо друга таблиця має обмеження зовнішнього ключа, що впливають на першу таблицю, і допускають порожні значення в стовпці зовнішнього ключа Наявність цього необовязкового зовнішнього ключа припускає, що якщо вторинна рядок звертається до первинної рядку, то первинна повинна існувати Водночас допустимо, щоб вторинна рядок утримувалася від звернення до первинної таблиці

Ще одним прикладом використання необовязкового зовнішнього ключа є стовпець пріоритетів або попереджень Проте в даному випадку він повинен вказувати на допустиму рядок у таблиці пріоритетів

Навчальна база даних OBXKites використовує аналогічну схему пріоритетів замовлень, тому звіт по всім замовленням з їх пріоритетами вимагає наявності зовнішнього обєднання

SELECT OrderNumber, OrderPriorityName FROM dbo [Order]

LEFT OUTER JOIN dboOrderPriority ON [Order]OrderPrioritylD =

OrderPriorityOrderPrioritylD

Дане ліве зовнішнє обєднання витягує всі замовлення і пріоритети, відповідні їм (якщо такі є) Сценарій OBXKites_Populate sql встановлює двом замовленнями пріоритет Rush (негайно)

OrderNumber OrderPriorityName

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Rush

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp NULL

3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Rush

4&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp NULL

5&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp NULL

6&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp NULL

7&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp NULL

8&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp NULL

9&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp NULL

10&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp NULL

Зворотні відносини (звані також рекурсивними і власними) використовують і необовязкові зовнішні ключі В обліковій базі даних Family зовнішніми ключами є поля Mot he ID і Father ID, повязані з полем Ре г son ID матері і батька Даний необовязковий зовнішній ключ дозволяє вводити інформацію про людей, навіть якщо дані про їхніх батьків ще не введені в базу даних У той же час, якщо поля MotherlD і FatherlD заповнені, вони повинні вказувати на особу, інформація про який занесена в базу даних

Повні зовнішні обєднання

Повний зовнішнє обєднання повертає всі дані з обох наборів незалежно від їх перетину (рис 98) Функціонально таке обєднання ідентично злиттю з вимогою унікальності правого і лівого зовнішніх обєднань (про злиття мова піде трохи пізніше в цьому розділі)

У реальному житті посилальна цілісність зменшує потребу в повних зовнішніх обєднаннях, так як будь-яка рядок з вторинної таблиці повинна мати відповідність в первинній таблиці (залежно від обовязковості зовнішнього ключа), так що найчастіше цілком достатньо лівого зовнішнього обєднання Повні зовнішні обєднання особливо корисні для очищення даних, що не мають обмежень, оскільки відфільтровують неприпустимі значення

Наступний приклад ілюструє таку ситуацію і порівнює повне зовнішнє обєднання з правим і лівим обєднанням Таблиця Two є підлеглою і має зовнішній ключ, що посилається на таблицю One У ній не існує обмежень на зовнішній ключ, тому можуть зявлятися деякі невідповідності, які легко виявити за допомогою повного зовнішнього обєднання:

CREATE TABLE dboOne (

OnePK INT,

Thingl VARCHAR(15)

)

CREATE TABLE dboTwo (

TwoPK INT,

OnePK INT,

Thing2 VARCHAR(15)

)

Дані в цьому прикладі містять рядки, які порушують посилальну цілісність Зовнішній ключ (OnePK) для рядків Plane і Cycle в таблиці Two не має відповідників у таблиці One Наступний пакет інструкцій вставляє в таблиці вісім рядків:

INSERT dboOne(OnePK, Thingl)

VALUES (1, Old Thing)

INSERT dboOne(OnePK, Thingl)

VALUES (2, New Thing)

INSERT dboOne(OnePK, Thingl)

VALUES (3, Red Thing)

INSERT dboOne(OnePK, Thingl)

VALUES (4, Blue Thing)

INSERT dboTwo(TwoPK, OnePK, Thing2)

VALUES(1,0, Plane)

INSERT dboTwo(TwoPK, OnePK, Thing2)

VALUES{2,2, Train)

INSERT dboTwo(TwoPK, OnePK, Thing2)

VALUES(3,3, Car)

INSERT dboTwo(TwoPK, OnePK, Thing2)

VALUES(4,NULL, Cycle)

Внутрішнє обєднання таблиць One і Two поверне тільки два рядки, для яких було знайдено відповідність:

SELECT Thingl, Thing2 FROM dboOne JOIN dboTwo

ON OneOnePK = TwoOnePK

Результат буде наступним:

Thingl            Thing2

New Thing     Train

Red Thing      Car

Ліве зовнішнє обєднання розширює цей список за рахунок включення рядків з таблиці One, що не мають відповідностей в таблиці Two:

SELECT Thingl, Thing2 FROM dboOne

LEFT OUTER JOIN dboTwo ON OneOnePK = TwoOnePK

Тепер з таблиці One повертаються всі рядки, але в той же час відсутні два рядки з таблиці Two:

Thingl            Thing2

Old Thing    NULL

New Thing     Train

Red Thing         Car

Blue Thing   NULL

Повний зовнішнє обєднання поверне всі рядки з обох таблиць, незалежно від того, чи знайдено для них відповідність чи ні:

SELECT Thingl, Thing2 FROM dboOne

FULL OUTER JOIN dboTwo ON OneOnePK = TwoOnePK

Тепер позиції Plane і Car перераховані поряд з усіма рядками з таблиці One: Thingl Thing2

NULL            Plane

New Thing     Train

Red Thing         Car

NULL            Cycle

Blue Thing   NULL

Old Thing    NULL

Як було показано в прикладі, повне зовнішнє обєднання є прекрасним інструментом пошуку всіх даних, включаючи некоректні Створюючи запити відмінностей (про них ми поговоримо пізніше в цьому розділі), побудовані на зовнішніх обєднаннях, можна виявляти і анулювати некоректні дані

Приміщення в зовнішні обєднання умов

Якщо мова йде про внутрішні обєднаннях, умова виробляє той же ефект, будучи поміщеним в пропозицію JOIN або WHERE, однак це твердження не відноситься до зовнішніх обєднанням Коли умова знаходиться у реченні JOIN, SQL Server включає в результат все рядки з зовнішньої таблиці, а потім використовує умова для включення рядків з вторинної таблиці Коли обмеження поміщено в пропозицію WHERE, виконується обєднання, а потім умова застосовується до обєднаних рядках Наступні два приклади демонструють ефект від приміщення умови в різні пропозиції

У першому запиті ліве зовнішнє обєднання включає всі рядки з таблиці One, а потім обєднує їх з тими рядками з таблиці Two, для яких значення поля ОпеРК в обох таблицях одно і значення поля Thinngl одно New Thing У результаті ми побачимо всі рядки з таблиці One, але менше рядків з таблиці Two:

SELECT Thingl, Thing2 FROM dboOne

LEFT OUTER JOIN dboTwo ON OneOnePK = TwoOnePK AND OneThingl = New Thing1

Результат буде наступним:

Thingl           Thing2

Old Thing   NULL

New Thing Train

Red Thing  NULL

Blue Thing NULL

Другий запит виконує ліве зовнішнє обєднання, яке дає в результаті чотири рядки Після цього пропозиція WHERE застосовує до цього результату обмеження, залишаючи тільки один рядок – з товаром New Thing

SELECT Thingl, Thing2 FROM dboOne

LEFT OUTER JOIN dboTwo ON OneOnePK = TwoOnePK WHERE OneThingl = New Thing

Результат вийде наступним:

Thingl           Thing2

New Thing   Train

Аналогія обєднань

Коли я навчав студентів створенню запитів, то для пояснення різних типів обєднань часто використовував таку аналогію Уявіть собі церкву пілігримів в XVII столітті, розділену за статевою ознакою Всі чоловіки в ній сиділи по одну сторону, а жінки – по інший Тепер уявіть собі кожну сторону церкви як таблицю бази даних, а різні комбінації людей, що йдуть з церкви, – як різні типи обєднань

Якщо всі подружні пари встануть зі своїх місць, візьмуть один одного під руку і вийдуть з церкви, то це буде внутрішнє обєднання між чоловіками і жінками Результуючий набір даних покидають церкву буде містити тільки відповідні один одному пари

Якщо встануть всі чоловіки і кожен чоловік візьме руку дружини, а всі інші вийдуть поодинці або групами, то це буде прикладом лівого зовнішнього обєднання Строй покидають церкву буде складатися як з подружніх пар, так і неодружених чоловіків

Аналогічно, якщо встануть всі жінки і вийдуть з церкви зі своїми чоловіками (зрозуміло, у кого вони є), це буде прикладом правого зовнішнього обєднання У цьому випадку всі незаміжні жінки покинуть церкву поодинці

У разі повного зовнішнього обєднання церква-покинуть усі, при цьому подружні пари вийдуть під руку

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*