Опис використання з'єднань у Microsoft Query

Що таке об'єднання?

Приєднання – це підключення між двома таблицями, у яких дві таблиці об'єднуються відповідно до поля, що вони мають спільну, створюючи нову віртуальну таблицю (яку можна зберегти як реальну таблицю). Наприклад, з такими двома таблицями: Color_Table: Join_Field Color_Field 1 Red 2 Blue 3 Green Pattern_Table: Join_Field Pattern_Field 2 Striped 3 Checkered 4 Polka-Dot простий стик нагадує таке: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered У таблиці результатів містяться лише записи 2 та 3 в полі Join_Field, тому що вони – це лише записи, які існують в обох Color_Table та Pattern_Table. Практичний приклад об'єднання – це список продуктів і виробників роздрібної торгівлі; таблицю "Товари" та таблицю "Постачальники" можна приєднуватись до поля "ІДЕНТИФІКАТОР продукту".

Внутрішнє об'єднання

У попередньому прикладі, званий внутрішнім об'єднанням, є найпростіший тип об'єднання. Зазвичай потрібно використовувати лише частину полів у таблицях. Наприклад, у реальному внутрішньому об'єднанні, як-от у наведеному вище, можливо, потрібно виключити Join_Field, щоб приєднуватися виглядало так: Color_Field Pattern_Field Blue Striped Green Checkered У Microsoft Query внутрішні об'єднання – це тип об'єднання за замовчуванням (для отримання додаткових відомостей див. сторінку 105 в розділі "Довідник користувача Microsoft Query", версія 1,0).

Інструкція SQL

Оператор SELECT (SQL) для структурованої мови запиту – це тип макросу, який можна використовувати під час створення об'єднання. Зверніть увагу, що SQL дуже відрізняється від інших мов макросів Microsoft Excel (Visual Basic для програм і макросів Excel 4,0). Для того, щоб легко створювати об'єднання в Microsoft Query, не потрібно розумітися на SQL. Кожне об'єднання має оператор SELECT, пов'язаний із ним. Щоб переглянути оператор SELECT для будь-якого об'єднання в Microsoft Query, натисніть кнопку "SQL" на панелі інструментів. Як і в програмі "реєстратор макросів Microsoft Excel", можна скористатися запитом, щоб записати оператор SELECT. На відміну від засобу записування макросів Microsoft Excel, засіб "вибір запису" завжди увімкнуто, і його не можна вимкнути. Ось як оператор SELECT може виглядати в Microsoft Query для наведеного вище внутрішнього об'єднання: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table WHERE Color_Table.Join_Field = Pattern_Table.Join_Field Зверніть увагу, що ми використовуємо ім'я бази даних "c: \ база _ даних", чия MDB – розширення імені файлу для бази даних Microsoft Access, яка може містити кілька таблиць в одному файлі. У деяких інших базах даних, таких як dBASE, Paradox і FoxPro, кожна таблиця має мати власний файл. У таких випадках синтаксис SQL може відображатися зайвим, оскільки ім'я таблиці завжди збігається з іменем файлу без розширення. Синтаксис SQL залежить від запитів двигунів; Наприклад, у Microsoft Access запит із наведеного вище прикладу має такий вигляд: SELECT Color_Table.[Color_Field], Pattern_Table.Pattern_Field FROM Pattern_Table INNER JOIN Color_Table ON Pattern_Table.[Join_Field] = Color_Table.[Join_Field]; Шлях до таблиці не використовується у програмі Microsoft Access, оскільки таблиця міститься в файлі Microsoft Access. mdb. Навіть якщо зовнішня таблиця додається та використовується в запиті, у програмі Microsoft Access SQL не відображається шлях до зовнішньої таблиці.

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

Ще один вид об'єднання називається зовнішнім об'єднанням. Під час зовнішнього об'єднання ви отримуєте всі записи з однієї таблиці та лише ті записи з іншої таблиці, які мають відповідні значення з першої таблиці. Це може вийти з деяких елементів поля пустим або "Null". Для всіх двох таблиць, які потрібно об'єднати, є два можливі зовнішні об'єднання, "Ліве зовнішнє об'єднання" та "праве зовнішнє об'єднання" (так, оскільки зазвичай вони відображаються в таблицях поруч). За допомогою двох попередніх таблиць у прикладі нижче наведено один із двох можливих зовнішніх з'єднань. Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered Інший можливий вступ – це такий спосіб: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot Примітка: під час перегляду даних не відображається слово "NULL" під час роботи з об'єднанням використовуйте ключове слово "NULL". У Microsoft Query обидва типи зовнішнього об'єднання можна легко створити за допомогою миші (щоб отримати докладні відомості про цю процедуру, перегляньте статтю 112 "посібник користувача Microsoft Query", версія 1,0). Нижче описано, як SQL-оператор може шукати другий приклад зовнішнього об'єднання. SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `c:\database`.Color_Table LEFT OUTER JOIN `c:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} Щоб створити практичний приклад зовнішнього об'єднання, створіть список продуктів компанії з цифрами збуту для продуктів, які були продані, але не виключаючи продукти, які не було продано. Для цього скористайтеся полем "код продукту", щоб приєднатися до таблиці "Товари" та таблицю збуту.

Об'єднання "відняти"

Третій тип об'єднання – це об'єднання віднімання. Об'єднання віднімання – це протилежність зовнішнього об'єднання; Вона включає лише ті записи в одній таблиці, які не збігаються з будь-яким записом в іншій таблиці. Подібно до зовнішніх з'єднань, для всіх двох таблиць, які потрібно об'єднати, можна відняти два можливі об'єднання. Тим не менше, вони зазвичай не називаються "лівим об'єднанням" або "право віднімання". Поле "відняти" зазвичай повертають поля лише з однієї з таблиць, тому що за визначенням полів іншої таблиці повертаються лише NULL-значення. Нижче наведено один із можливих варіантів приєднання до віднімання. join_Field Color_Field 1 Red а ось інший: Join_Field Pattern_Field 4 Polka-Dot У Microsoft Query об'єднання буде створено за допомогою першого створення зовнішнього об'єднання, а потім за умови "NULL" у відповідному полі (Pattern_Field в першому прикладі вище; Color_Field у другому прикладі), щоб виключити записи, які відповідають між таблицями. Нижче описано, як SQL-оператор може шукати перше приєднання до віднімання: SELECT Color_Table.Join_Field, Color_Table.Color_Field FROM {oj `c:\database`.Color_Table LEFT OUTER JOIN `c:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} WHERE (Pattern_Table.Pattern_Field Is Null) Щоб створити практичний приклад об'єднання "відняти", список клієнтів, які не замовили нещодавно. Для цього використовуйте поле "ІДЕНТИФІКАТОР замовлення", щоб приєднатися до таблиці "клієнти" та таблиці "замовлення".

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

Четвертий тип об'єднання – це повне зовнішнє об'єднання. Повне зовнішнє об'єднання – це комбінація зовнішнього об'єднання зі своїм безкоштовним об'єднанням віднімання. Повне зовнішнє об'єднання включає всі записи з обох таблиць і об'єднує ці записи, поширені між двома таблицями. Нижче наведено повне зовнішнє об'єднання. Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot У Microsoft Query буде створено повне зовнішнє об'єднання, вставивши оператор UNION на власний рядок між оператором SELECT JOIN, а оператор SELECT JOIN (див. вище). Щоб створити повне зовнішнє об'єднання в Microsoft Query, виконайте наведені нижче дії, щоб створити відповідний речення SQL.

1. Створіть зовнішнє об'єднання, а потім у меню Файл виберіть команду Створити та створіть об'єднання.

2. Скопіюйте SQL JOIN.

3. Перейдіть до зовнішнього об'єднання, введіть слово UNION на власний рядок під SQL-оператор зовнішнього об'єднання, вставте SQL-код приєднання під ОБ'ЄДНАННЯМ Word і Закрийте вікно приєднання до віднімання.

Примітка. Результати наведеної нижче таблиці пропускають значення Join_Field "4", оскільки запис, який Join_Field значенням дорівнює 4, міститься в таблиці Pattern_Table. У наведеному нижче реченні SQL dones't виберіть поле Pattern_Table. John_Field. Join_Field Color_Field Pattern_Field (NULL) Polka-Dot 1 Red (NULL) 2 Blue Striped 3 Green Checkered Ось як SQL може шукати вище повне зовнішнє об'єднання (команди під час вставлення оператора UNION): SELECT Color_Table.Join_Field, Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `C:\database`.Pattern_Table LEFT OUTER JOIN `C:\database`.Color_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} UNION SELECT Color_Table.Join_Field, Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `C:\database`.Color_Table LEFT OUTER JOIN `C:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} WHERE (Color_Table.Color_Field Is Null) or(Pattern_Table.Pattern_Field Is Null) Щоб створити практичний приклад повного зовнішнього об'єднання, об'єднайте складені списки клієнтів, які використовуються різними кафедрами, включно з номерами факсу (які були лише в першому списку), а також імена електронних листів Інтернету (тільки у другому списку). Кожний відділ може продовжувати використовувати його частковий список, коли ви маєте повний доступ до списку. Їх можна приєднати до поля "ІДЕНТИФІКАТОР клієнта".

Самостійне приєднання

П'ятий вид приєднання – це самостійне приєднання. Self-JOIN – це зв'язки, у яких поле в таблиці відповідає іншому полю в копії тієї самої таблиці. Використання цього прикладу таблиці: Table_Three Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 і копію, як описано нижче: Table_Three_01 Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 Для створення списку імен працівників за допомогою імен співробітників можна використовувати самостійне приєднання. Employee_ID в Table_Three буде приєднано до Reports_To у Table_Three_01. Нижче описано, як це може виглядати спочатку. Employee_Name Employee_Name Bob Jim Sue (NULL) Jim Sue Jane Jim Проте, оскільки в обох полях є одне ім'я поля, змініть один із імен полів, виконавши наведені нижче дії. Employee_Name Supervisor Bob Jim Sue (NULL) Jim Sue Jane Jim Нижче описано, як SQL може шукати наведений вище самоприєднання: SELECT table_three.Employee_Name, table_three_01.Employee_Name 'Supervisor' FROM `c:\database`.table_three, `c:\database`.table_three_01 WHERE table_three.Employee_ID = table_three_01.Reports_To Під час повернення даних до програми Microsoft Excel не можна перейменувати поле в Microsoft Query. Це справедливо, оскільки Microsoft Excel використовує початкове ім'я поля. Щоб отримати докладніші відомості про цю проблему, ознайомтеся з наведеною нижче статтею в базі знань Microsoft: 121551 : XL5: поле замість імені стовпця в MSQUERY повернуто програмі Excel макрос Microsoft Excel має змінити ім'я стовпця щоразу, коли всі повернуті дані оновлюватимуться (якщо ви не повертаєте дані у зведеній таблиці, у цьому випадку сам стрижень може створювати та зберігати настроюване ім'я поля).

Об'єднання "рівний-JOIN" і "природне об'єднання"

Майже всі відповіді, включно з усіма прикладами, до яких дано до цих пір, є equi-з'єднання та природні об'єднання. Значення цих умов мають важливе значення для середнього користувача Microsoft Query, але наступні два абзаци намагаються пояснити умови для тих, хто може бути цікавим. Приєднання "equi-JOIN" – це об'єднання, у якому записи витягаються на основі того, чи мають поля JOIN відповідні значення в обох таблицях. Це може здатися лише визначенням об'єднання, але це не так. Приклад нерівноправного об'єднання – приєднання до записів у першій таблиці в другій таблиці, у якій поле "приєднання" у першій таблиці перевищує (а не дорівнює) приєднане поле у другій таблиці (або менше, крім того, що дорівнює). Звісно, ця функція повертає більше записів, ніж для equi-JOIN. Природне об'єднання – це той, у якому повертаються лише одна з двох таблиць, до яких приєдналися поля. Оскільки ці два поля за визначенням ідентичні в equi-JOIN, воно буде зайвим для включення обох. Для нерівноправного об'єднання важливо додати обидва поля. Таким чином, ви маєте спільну участь у надбудові equi-JOIN і природні JOIN. Ви хочете, щоб за допомогою equi-JOIN (що описує більшість з'єднань), щоб бути природним об'єднанням, повернувши лише одне з Об'єднаних полів; але якщо ви коли-небудь використовуєте нерівний приєднання, можливо, ви захочете зробити його неприродним об'єднанням, повернувши обидва об'єднані поля. Існують інші типи з'єднань. Повний спектр з'єднань було нещодавно визначено в 1992 і цей стандарт відомий як SQL-92. Деякі об'єднання не важливі для користувачів Microsoft Excel, тому що ці об'єднання полегшують виконання завдань у програмі Microsoft Excel.

Декартів добуток

Під час спроби повернути дані з двох або кількох таблиць без жодних з'єднань створюється те, що називається "Декартів добуток". Декартів добуток визначається як всі можливі комбінації рядків у всіх таблицях. Переконайтеся, що у вас є об'єднання, перш ніж повертати дані, оскільки декартових продуктів у таблицях з багатьма записами та/або в багатьох таблицях може тривати кілька годин. Нижче наводиться декартових продуктів, які використовуються в двох таблицях; Зверніть увагу, що ця таблиця має лише 3 записи Times 3 Records, що дає загальне значення 9 записів. Однак, якщо замість цього, таблиця 100 записує записи Times 1 000 Records Times 10 000 Records; Після цього таблиця міститиме записи 1 000 000 000! Join_Field Join_Field Color_Field Pattern_Field 1 2 Red Striped 1 3 Red Checkered 1 4 Red Polka-Dot 2 2 Blue Striped 2 3 Blue Checkered 2 4 Blue Polka-Dot 3 2 Green Striped 3 3 Green Checkered 3 4 Green Polka-Dot Час від часу деякі користувачі хочуть використовувати Декартові продукти; Однак більшість користувачів, які отримують їх, випадково, і їх часто плутають. Оскільки більшість користувачів виключаються більшість полів в об'єднанні, реальний Декартів продукт може легко виглядати як спантеличуватись, як це: Color_Field Red Red Red Blue Blue Blue Green Green Green Якщо записи 100 додаються до Pattern_Table, цей запит матиме 309 записів (103 записує всі червоні, сині та зелені). Декартові продукти мають порівняно прості SELECT-виписки. Нижче описано, як SQL може шукати наведений вище Декартів продукт: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table Практичним прикладом декартових продуктів буде створення списку усіх можливих комбінацій параметрів на товарній продукції, за допомогою цінових підсумків для кожної комбінації.