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


Загальні відомості


У цій статті описано використання з'єднань у Microsoft Query. Приєднання – це метод, який можна використовувати з базами даних для визначення та ілюстрації зв'язків між двома таблицями. У Microsoft Query можна створювати та маніпулювати різними типами з'єднань. У більшості випадків Microsoft Query приєднується до таблиць у запиті. Об'єднання відображаються лініями, які з'єднують таблиці в області таблиці. Теми, описані в цій статті, містять такі дії:
   What Is a Join?   Inner Joins   SQL Statements   Outer Joins   Subtract Joins   Full Outer Joins   Self Joins   Equi-Joins   Natural Joins   Cartesian Products 

Додаткові відомості


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

Приєднання – це підключення між двома таблицями, у яких дві таблиці об'єднуються відповідно до поля, що вони мають спільну, створюючи нову віртуальну таблицю (яку можна зберегти як реальну таблицю). Наприклад, з такими двома таблицями:
      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 
Практичним прикладом декартових продуктів буде створення списку усіх можливих комбінацій параметрів на товарній продукції, за допомогою цінових підсумків для кожної комбінації.

Посилання


Microsoft Query "посібник користувача", версія 1,0, сторінки 101-114, 123-131 наступні дві книги не входять до складу будь-яких продуктів Microsoft і не виробляються корпорацією Майкрософт. Продукти, які обговорюватимуться сторонніми продуктами, виробляються постачальниками, незалежно від корпорації Майкрософт; Ми не даємо жодних гарантій, неявних або інших, що стосуються продуктивності або надійності цих продуктів. "Розуміння нового SQL: повне керівництво", видавництво Морган Кауфман, Inc., 1993. "Код Джо Celko в SQL для Smarties: Advanced SQL для програмування", "Морган Кауфман видавці, Inc., 1995. Щоб отримати докладні відомості про створення об'єднання в Microsoft Query, натисніть кнопку Пошук у розділі Довідка та введіть:
   joins, overview