Об’єднання джерел даних у запиті

При включенні кількох джерел даних у запит можна використовувати об’єднання для обмеження записів, які ви хочете бачити, залежно від того, як джерела даних пов’язані одне з одним. Можна також використовувати об’єднання для записів з обох джерел даних, так що кожна пара записів з джерел стає одним записом у результатах запиту.

У цій статті розглядаються різні типи об’єднання і показується, як використовувати їх у запитах.

Примітка :  У цій статті, для пояснення джерела даних використовується таблиця термінів. Ви можете також використовувати запит в об’єднанні так само, як таблицю.

Огляд

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

Ліве або праве зовнішнє об’єднання: показ усіх рядків з однієї таблиці та відповідних рядків з іншої таблиці

Повне зовнішнє об’єднання: показ усіх рядків з обох таблиць і поєднання їх, якщо є спільні значення

Перехресне об’єднання: усі можливі комбінації рядків

Порівняльне об’єднання: об’єднання записів на основі нерівності значень полів

Видалення об’єднання

Огляд

Об’єднання призначене для запитів, зв’язаних із таблицями: визначаючи те, як дані з двох джерел можуть бути об’єднані на основі спільних значень даних. Тут наведена ілюстрація об’єднання в запиті в режимі конструктора, де показані відкриті у діалоговому вікні параметри об’єднання:

Вікно параметрів об’єднання.

Ця лінія між таблицями являє собою об’єднання. Двічі клацніть об’єднання, щоб відкрити діалогове вікно Параметри об’єднання (показане) і перегляньте або змініть об’єднання.

Об’єднання іноді є спрямованим. Ця область у діалоговому вікні показує, які таблиці знаходяться в об’єднанні, і поля, які використовуються для об’єднання таблиць.

Ця область визначає тип об’єднання: параметр 1 є внутрішнім об’єднанням, 2 є лівим зовнішнім об’єднанням, а 3 є правим зовнішнім об’єднанням.

Можна використати поля з обох таблиць і відобразити з кожної таблиці дані, які відносяться до даного завдання. У внутрішньому об’єднанні ніяких інших даних немає. У зовнішнього об’єднання в результати запиту також включені не пов’язані записи з однієї таблиці.

Деякі об’єднання створюються автоматично

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

Типи об’єднань

Існують чотири основні типи об’єднань: внутрішні об’єднання, зовнішні об’єднання, перехресні об’єднання і порівняльні об’єднання. Перехресні і порівняльні об’єднання приєднується є складними типами об’єднань і використовуються рідко, але ви повинні знати про них, щоб мати повне уявлення про те, як об’єднання працює.

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

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

Зовнішні об’єднання: правильно поєднуються всі пов’язані дані, а також решта записів з однієї таблиці

Зовнішнє об’єднання схоже на внутрішнє об’єднання, але з додаванням решти рядків з однієї з таблиць. Зовнішні об’єднання є спрямованими: ліве зовнішнє об’єднання використовує всі записи з лівої таблиці – першої таблиця в об’єднанні, а праве зовнішнє об’єднання використовує всі записи з правої таблиці – другої таблиці в об’єднанні.

Повне зовнішнє об’єднання: усі дані об’єднуються, якщо це можливо

У деяких системах зовнішнє об’єднання може задіяти всі рядки з обох таблиць, коли рядки об’єднуються зі своїми відповідниками. Це називається повним зовнішнім об’єднанням і Access не підтримує його явно. Однак для досягнення того ж ефекту ви можете використати перехресне об’єднання й умови.

Перехресне об’єднання: усі дані об’єднуються всіма можливими способами

Частіше за все перехресне об’єднання є побічним ефектом ситуації, коли у запит додають дві таблиці, а потім забувають об’єднати їх. Access інтерпретує це так, ніби ви хочете побачити всі записи з однієї таблиці в поєднанні з усіма записами з іншої таблиці – всі можливі комбінації записів. Бо жодні дані не вдається об’єднати, цей вид об’єднання рідко дає корисні результати. Але в кількох ситуаціях перехресне об’єднання є саме тим, що вам потрібно.

Порівняльне об’єднання: аналогічне звичайному об’єднанню, але під час об’єднання виконується порівняння рядків

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

На початок сторінки

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

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

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

Як можна використовувати внутрішнє об’єднання?

Для створення внутрішнього об’єднання перетягніть поле з одного джерела даних в поле в іншому джерелі даних у сітці макета запиту. Програма Access об’єднує дві таблиці за полями та відображає лінію між ними.

Автоматичні внутрішні об’єднання

У більшості випадків вам не потрібно робити нічого для використання внутрішнього об’єднання. Якщо додані в запит таблиці вже мають зв’язки, Access автоматично створює внутрішнє об’єднання між кожною парою зв’язаних таблиць під час додавання таблиць. Якщо цілісність зв’язків є обов’язковою, Access також відображає "1" над лінією об’єднання, щоб показати, яка з таблиць знаходиться на стороні "один" у зв’язок "один-до-багатьох", а символ нескінченності (), щоб показати, яка таблиця знаходиться на стороні "багатьох".

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

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

Синтаксис SQL для внутрішнього об’єднання

Внутрішні об’єднання в SQL визначаються реченням FROM, як показано нижче:

FROM таблиця1 INNER JOIN таблиця2 ON таблиця1.поле1 compopr таблиця2.поле2

Операція INNER JOIN складається з таких частин.

Частина

Опис

таблиця1, таблиця2

Імена таблиць, записи яких буде об’єднано.

поле1, поле2

Імена об’єднаних полів. Якщо поля не числові, вони повинні мати однаковий тип даних, але не повинні мати однакові імена.

compopr

Будь-який реляційний оператор порівняння: "=", "<", ">", "<=", ">=" або "<>"

Додаткові відомості про синтаксис операції INNER JOIN можна знайти в розділі Операція INNER JOIN.

На початок сторінки

Ліве або праве зовнішнє об’єднання: показ усіх рядків з однієї таблиці та відповідних рядків з іншої таблиці

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

Зовнішнє об’єднання може бути лівим зовнішнім об’єднанням або правим зовнішнім об’єднанням. У лівому зовнішньому об’єднанні запит включає в себе всі рядки з першої таблиці в реченні FROM інструкції SQL і тільки ті рядки з іншої таблиці, у яких об’єднуване поле містить значення, спільні для обох таблиць. У правому зовнішньому об’єднанні запит включає в себе всі рядки з другої таблиці в реченні FROM інструкції SQL і тільки ті рядки з іншої таблиці, у яких об’єднуване поле містить значення, спільні для обох таблиць.

Примітка : Можна легко визначити ліву і праву таблиці в даному об’єднанні, двічі клацнувши об’єднання і переглянувши діалогове вікно Параметри об’єднання. Або, у режимі SQL, можна дізнатися про подробиці об’єднання з речення FROM.

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

Як можна використовувати зовнішнє об’єднання?

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

Зміна внутрішнього об’єднання на зовнішнє об’єднання

  1. У режимі конструктора запиту двічі клацніть об’єднання, яке потрібно змінити.

    Відкриється діалогове вікно Параметри об’єднання.

  2. У діалоговому вікні Параметри об’єднання зверніть увагу на значення параметра 2 і параметра 3.

  3. Виберіть параметр, який ви хочете використовувати, і натисніть кнопку OK.

  4. Access відображає об’єднання і стрілку, яка вказує на джерело даних, де всі рядки будуть включені в джерело даних, у яке будуть включені тільки ті рядки, які задовольняють умові об’єднання.

Неоднозначне зовнішнє об’єднання

Якщо ви створили запит, який містить параметри LEFT JOIN та INNER JOIN, Access не зможе визначити, яку операцію об’єднання слід виконати в першу чергу. Оскільки результати різняться в залежності від того, ліве об’єднання чи внутрішнє об’єднання виконуватиметься в першу чергу, Access відображає повідомлення про помилку неоднозначного зовнішнього об’єднання.

Для виправлення цієї помилки змініть запит так, щоб було ясно, яке об’єднання необхідно виконувати в першу чергу.

Зовнішні об’єднання та запит оновлення

У веб-застосунку Access поле в запиті до кількох таблиць допускає оновлення тільки при дотриманні таких умов.

  • Поле з таблиці "більшість із багатьох" запиту – таблиця, яка шукає дані в іншій таблиці.

  • Результати запиту включають в себе первинний ключ із таблиці "більшість із багатьох".

  • Якщо таблиця "більшість із багатьох" є частиною зовнішнього об’єднання, вона знаходиться на внутрішній стороні об’єднання: ліва таблиця лівого зовнішнього об’єднання або правої таблиці правого зовнішнього об’єднання.

Якщо будь-яка з умов не виконується, поле буде доступне тільки для читання, чи то в аркуші запиту, чи то в поданні, що ґрунтується на запиті.

Синтаксис SQL для зовнішнього об’єднання

Зовнішні об’єднання в SQL визначаються в реченні FROM, як показано нижче:

FROM таблиця1 [ LEFT | RIGHT ] JOIN таблиця2
ON таблиця1.поле1 compopr таблиця2.поле2

Операції LEFT JOIN і RIGHT JOIN складаються з таких частин:

Частина

Опис

таблиця1, таблиця2

Імена таблиць, записи яких буде об’єднано.

поле1, поле2

Імена об’єднаних полів. Поля повинні мати однаковий тип даних, але не повинні мати однакові імена.

compopr

Будь-який реляційний оператор порівняння: "=", "<", ">", "<=", ">=" або "<>"

Додаткові відомості про синтаксис зовнішнього об’єднання можна знайти в наведених нижче статтях Операції LEFT JOIN, RIGHT JOIN.

На початок сторінки

Повне зовнішнє об’єднання: показ усіх рядків з обох таблиць і поєднання їх, якщо існують спільні значення

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

  1. Створіть запит і додайте дві таблиці. Якщо Access створює будь-які об’єднання, видаліть їх.

  2. Додайте будь-які поля, для яких потрібно отримати результати запиту.

  3. Додайте об’єднувані поля з обох таблиць, і зніміть прапорець у рядку Показ для них.

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

  5. У рядку Or під щойно введеним критерієм введіть Is Null.

  6. У наступному рядку Або (безпосередньо під першим), під другим полем об’єднання другої таблиці, введіть Null.

Запит повертає комбінований набір таких даних.

  • Рядки, де збігаються значення полів об’єднання, а також дані, комбіновані належним чином (по суті, внутрішнє об’єднання)

  • Рядки, де немає значення для поля об’єднання в першій таблиці (по суті, поза зовнішнім лівим об’єднанням)

  • Рядки, де немає значення для поля об’єднання в другій таблиці (по суті, поза правим зовнішнім об’єднанням)

На початок сторінки

Перехресне об’єднання: усі можливі комбінації рядків

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

Навіщо потрібно використовувати перехресне об’єднання?

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

Як я можу використовувати перехресне об’єднання?

Додайте дві таблиці в запит, не створюйте об’єднання між ними, і включіть поля з обох таблиць в результати запиту. Access об’єднує кожен рядок з кожної таблиці або запиту, який явно не будо об’єднано з будь-якою іншою таблицею або запитом до всіх інших рядків в результатах запиту. Розглянемо приклад із знижками з попереднього розділу. Припустимо, у вас є 91 клієнт, і ви хочете подивитися на п’ять можливих варіантів знижки. Перехресне об’єднання дасть 455 рядків (добуток 91 і 5).

Якщо ви додаєте таблиці, зв’язані між собою, Access автоматично створює внутрішнє об’єднання. Ви можете видалити це автоматичне об’єднання при необхідності створити перехресне об’єднання.

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

На початок сторінки

Порівняльне об’єднання: об’єднання записів на основі нерівності значень полів

Об’єднання не повинні бути основані на рівності полів об’єднання. Об’єднання може бути засноване на будь-якому операторі порівняння, такому як більше (>), менше (<) або не дорівнює (<>). Об’єднання, які не засновані на рівності, називають порівняльним об’єднанням.

Якщо ви хочете об’єднати рядки з двох джерел даних на основі значень нееквівалентних полів, можна використати порівняльне об’єднання. Як правило, порівняльне об’єднання засноване на операторах порівняння більше (>), менше (<), більше або дорівнює (>=), або менше або дорівнює (<=). Порівняльне об’єднання, яке засноване на операторі не дорівнює (<>) може повернути майже стільки ж рядків, що й перехресне об’єднання, і результати можуть бути складними для інтерпретації.

Як можна використовувати порівняльне об’єднання?

Програма Access явно не підтримує порівняльне об’єднання в режимі конструктора, але ви можете добитися того ж ефекту за допомогою перехресного об’єднання і умов.

  1. Створіть запит і додайте до порівняльного об’єднання дві таблиці. Якщо Access створює будь-які об’єднання автоматично, видаліть їх.

  2. Додайте потрібні поля виводу.

  3. Додайте об’єднувані поля з першої таблиці, і зніміть прапорець у рядку Показ.

  4. У рядку Умови введіть потрібний оператор нерівності і поле об’єднання другої таблиці.

Запит повертає рядки, у яких об’єднуються дані з двох таблиць на основі вказаного вами оператора нерівності.

На початок сторінки

Видалення об’єднання

Якщо Access автоматично створює небажане об’єднання, або якщо ви помилково створюєте об’єднання – наприклад, об’єднання між двома полями, які містять різнорідні типи даних – ви можете видалити об’єднання.

  1. У сітці макета запиту клацніть об’єднання, яке потрібно видалити.

  2. Натисніть клавішу Delete.

- або -

  • У сітці макета запиту клацніть правою кнопкою миші об’єднання, яке потрібно видалити, і виберіть пункт Видалити.

На початок сторінки

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×