Popis používania spojení v programe Microsoft Query

Čo je spojenie?

Pripojenie je spojenie medzi dvomi tabuľkami, v ktorých sa dve tabuľky zlúčia podľa poľa, ktoré majú spoločné, a vytvárajú novú virtuálnu tabuľku (ktorú možno uložiť ako skutočnú tabuľku). Napríklad s týmito dvoma tabuľkami: 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 jednoduché spojenie sa podobá nasledujúcemu hláseniu: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered Tabuľka výsledkov obsahuje v poli Join_Field iba záznamy 2 a 3, pretože sú to len záznamy, ktoré existujú v Color_Table aj Pattern_Table. Praktickým príkladom spojenia je zoznam produktov a výrobcov maloobchodníka. do poľa ID produktu sa môže spojiť tabuľka produkty a tabuľka dodávatelia.

Vnútorné spojenie

Spojenie v predchádzajúcom príklade s názvom vnútorné spojenie predstavuje najjednoduchší typ spojenia. Zvyčajne budete chcieť použiť len zlomok polí v tabuľkách. Napríklad v skutočnom vnútornom spojení, ako je napríklad vyššie uvedené, možno budete chcieť vylúčiť Join_Field, aby sa spojenie vyzeralo takto: Color_Field Pattern_Field Blue Striped Green Checkered V programe Microsoft Query sú vnútorné spojenia predvoleným typom spojenia (Ďalšie informácie nájdete v téme stránka 105 v používateľskej príručke Microsoft Query, verzia 1,0).

Príkaz SQL

Príkaz SELECT v jazyku Structured Query Language (SQL) je typ makra, ktoré môžete použiť pri vytváraní spojenia. Všimnite si, že SQL je veľmi odlišná od iných jazykov makier Microsoft Excelu (Visual Basic for Applications a jazyka makier Excelu 4,0). NIE je potrebné rozumieť SQL, aby sa jednoducho vytvorili spojenia v programe Microsoft Query. Každé spojenie obsahuje príkaz SELECT, ktorý je s ním spojený. Kliknutím na položku SQL na paneli s nástrojmi môžete zobraziť príkaz SELECT pre ľubovoľné spojenie v programe Microsoft Query. Rovnako ako v prípade zapisovača makier Microsoft Excelu môžete pomocou dotazu zaznamenať príkaz SELECT. Na rozdiel od rekordéra makier Microsoft Excelu je vždy zapnutý a nie je možné ho vypnúť. Tu je postup, ako môže príkaz SELECT vyzerať v programe Microsoft Query pre vyššie uvedené vnútorné spojenie: 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 Všimnite si, že používame názov databázy "C:\database", ktorého. mdb je príponou názvu súboru pre databázu Microsoft Accessu, ktorá môže mať viacero tabuliek v jednom súbore. V niektorých iných databázach, ako je napríklad dBASE, Paradox a FoxPro, musí mať každá tabuľka vlastný súbor. V týchto prípadoch sa syntax SQL môže zobraziť nadbytočné, pretože názov tabuľky je vždy rovnaký ako názov súboru bez prípony. Syntax SQL sa líši v závislosti od motorov dotazov. napríklad v programe Microsoft Access dotaz z vyššie uvedeného príkladu sa podobá nasledujúcemu hláseniu: 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]; Cesta k tabuľke sa v programe Microsoft Access nepoužíva, pretože tabuľka sa nachádza v súbore. mdb programu Microsoft Access. Aj v prípade, že je v dotaze pripojená externá tabuľka, príkaz SQL v Microsoft Accesse nezobrazuje cestu k externej tabuľke.

Vonkajšie spojenie

Ďalší typ spojenia sa nazýva vonkajšie spojenie. Pri vonkajšom spojení získate všetky záznamy z jednej tabuľky a len tie záznamy z druhej tabuľky, ktoré majú zhodné hodnoty z prvej tabuľky. Môže to opustiť niektoré položky polí prázdne alebo null. Pri všetkých dvoch tabuľkách, ktoré sa majú spojiť, sú k dispozícii dva možné vonkajšie spojenia, "ľavé vonkajšie spojenie" a "pravé vonkajšie spojenie" (s názvom, pretože zvyčajne zobrazíte tabuľky vedľa seba). Použitie predchádzajúcich dvoch tabuliek v príklade je jedným z dvoch možných vonkajších spojení: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered Ďalšie možné spojenie je nasledovné: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot Poznámka: v spojení sa nezobrazuje slovo NULL pri zobrazení údajov. pri práci s spojeniami Použite kľúčové slovo NULL. V programe Microsoft Query môžete obidva druhy vonkajšieho spojenia jednoducho vytvoriť pomocou myši (Ďalšie informácie o tomto postupe nájdete v téme strana 112 v príručke používateľa programu Microsoft Query, verzia 1,0). Nasleduje postup, ako môže príkaz SQL vyhľadať druhý príklad vonkajšieho spojenia: 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} Ak chcete vytvoriť praktický príklad vonkajšieho spojenia, vytvorte zoznam produktov spoločnosti s údajmi o predaji produktov, ktoré sa predávajú, ale nevynímajúc produkty, ktoré neboli predané. Ak to chcete urobiť, použite pole ID produktu na Pridanie tabuľky produktov a predajnej tabuľky.

Odčítanie spojenia

Tretím druhom spojenia je odčítanie spojenia. Odčítanie spojenia je opakom vonkajšieho spojenia. obsahuje len tie záznamy v jednej tabuľke, ktoré sa nezhodujú so žiadnym záznamom v inej tabuľke. Podobne ako pri vonkajších spojeniach sa k dvom tabuľkám, ku ktorým sa chcete pridať, dostanete dvoma možnými odčítanými spojeniami. Avšak, nie sú zvyčajne označované ako "Left odčítať spojenie" alebo "právo odčítať spojenie." Odčítanie spojenia zvyčajne vracia polia iba z jednej tabuľky, pretože podľa definície polia druhej tabuľky vracajú len hodnoty NULL. Nasleduje jeden možný odčítanie spojenia: join_Field Color_Field 1 Red a tu je ďalší: Join_Field Pattern_Field 4 Polka-Dot V programe Microsoft Query sa spojenie odčítanie vytvorí najprv vytvorením vonkajšieho spojenia a potom pomocou kritérií "IS NULL" v príslušnom poli (Pattern_Field v prvom uvedenom príklade; Color_Field v druhom príklade), aby sa vylúčili záznamy, ktoré sa zhodujú medzi tabuľkami. Nasleduje postup, ako môže príkaz SQL Hľadať prvé odčítanie spojenia vyššie: 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) Ak chcete vytvoriť praktický príklad odčítania spojenia, zoznam zákazníkov, ktorí sa v poslednej dobe neuložili. Ak to chcete urobiť, použite pole Identifikácia objednávky na pridanie sa do tabuľky Zákazníci a do tabuľky objednávky.

Úplné vonkajšie spojenie

Štvrtý typ spojenia je úplným vonkajším spojením. Úplné vonkajšie spojenie je kombináciou vonkajšieho spojenia s jeho bezplatným odpočítaním spojenia. Úplné vonkajšie spojenie obsahuje všetky záznamy z oboch tabuliek a zlúči tie záznamy, ktoré sú bežné medzi oboma tabuľkami. Toto je úplné vonkajšie spojenie: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot V programe Microsoft Query sa vytvorí úplné vonkajšie spojenie vložením operátora UNION do vlastného riadkovania medzi príkazom SELECT a odčítanie príkazu SELECT (viď vyššie). Ak chcete vytvoriť úplné vonkajšie spojenie v programe Microsoft Query, použite nasledujúci postup na vytvorenie príslušnej vety SQL:

1. Vytvorte vonkajšie spojenie a potom v ponuke Súbor kliknite na položku nový a vytvorte odčítanie spojenia.

2. Skopírujte SQL odčítanie spojenia.

3. Prejdite na vonkajšie spojenie, zadajte wordovú úniu na samostatnom paneli pod príkazom SQL na vonkajšie spojenie, prilepte odčítanie SQL spojenia pod slovom UNION a zatvorenie okna odčítanie spojenia.

Poznámka: Výsledky nasledujúcej tabuľky neobsahujú hodnotu Join_Field "4", pretože záznam, ktorý Join_Field hodnotu rovná 4, sa nachádza v tabuľke Pattern_Table. Pod dones't vety SQL vyberte pole Pattern_Table. John_Field. Join_Field Color_Field Pattern_Field (NULL) Polka-Dot 1 Red (NULL) 2 Blue Striped 3 Green Checkered Tu je postup, ako môže SQL Hľadať vyššie uvedené úplné vonkajšie spojenie (prilepia sa príkazy pod operátorom ZJEDNOTEnia): 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) Ak chcete vytvoriť praktický príklad úplného vonkajšieho spojenia, zlúčte prekrývajúce sa zoznamy zákazníkov používané rôznymi oddeleniami vrátane faxových čísel (ktoré boli len v prvom zozname) a názvov internetových e-mailov (ktoré sú len v druhom zozname). Všetky oddelenia môžu naďalej používať svoj čiastkový zoznam, pričom majú k dispozícii úplný zoznam spojených s pripojením. Mohli by sa stať súčasťou poľa ID zákazníka.

Vlastné spojenie

Piaty druh spojenia je vlastným spojením. Vlastné pripojenie je spojenie, v ktorom je pole v tabuľke zhodné s iným poľom v kópii tej istej tabuľky. Pomocou tejto tabuľky príkladu: Table_Three Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 a jeho kópiu, a to takto: Table_Three_01 Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 Vlastné spojenie by sa mohlo použiť na vytvorenie zoznamu názvov zamestnancov s názvami nadriadeného. Employee_ID v Table_Three by sa pripojili k Reports_Tou v Table_Three_01. Nasledujúci postup sa môže na prvý pohľad Zobraziť: Employee_Name Employee_Name Bob Jim Sue (NULL) Jim Sue Jane Jim Keďže je však mätúce, že pre obidve polia bude mať rovnaký názov poľa, zmeňte jeden z názvov polí takto: Employee_Name Supervisor Bob Jim Sue (NULL) Jim Sue Jane Jim Nižšie je uvedený postup, ako môže SQL vyhľadať vyššie samostatné spojenie: 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 Keď vrátite údaje do Microsoft Excelu, nepoužíva sa na Premenovanie poľa v programe Microsoft Query. Je to pravda, pretože Microsoft Excel používa pôvodný názov poľa. Ďalšie informácie o tomto probléme nájdete v nasledujúcom článku databázy Microsoft Knowledge Base: 121551 : XL5: pole namiesto názvu stĺpca v MSQuery vrátené do programu Excel. makro programu Microsoft Excel musí zmeniť názov stĺpca pri každom obnovení vrátených údajov (pokiaľ nevrátite údaje v kontingenčnej tabuľke, pričom v tomto prípade môže samotný Pivot vytvoriť a zachovať názov vlastného poľa).

Equi – spojenie a prirodzené spojenie

Takmer všetky spojenia vrátane všetkých uvedených príkladov sú equi a prirodzené spojenia. Význam týchto výrazov má málo význam pre priemerného používateľa Microsoft Query, ale ďalšie dva odseky sa snažia vysvetliť podmienky pre tých, ktorí môžu byť zvedaví. Equi-JOIN je spojenie, v ktorom sa načítajú záznamy na základe toho, či sú polia spojenia zhodné s hodnotami v oboch tabuľkách. Môže sa to zdať len definícia spojenia, ale nie je. Príkladom spojenia bez rovnosti je spojenie, v ktorom sú záznamy v prvej tabuľke pripojené k týmto záznamom v druhej tabuľke, v ktorej je spojené pole v prvej tabuľke väčšie ako (namiesto rovná sa) spojené pole v druhej tabuľke (alebo menej ako alebo čokoľvek okrem). Prirodzene, vráti viac záznamov ako Equi. Prirodzeným spojením je ten, v ktorom sa vráti len jedna z týchto dvoch tabuliek Spojených polí. Keďže tieto dve polia sú zhodné s definíciou v Equi, je nadbytočné tak, aby obsahovali oboje. Ak sa chcete pripojiť k non-EQUAL, je dôležité, aby ste zahrnuli obidva tieto polia. Takže, Equi-spája a prirodzené spojenia spoločne. Ak chcete, aby sa spojenie Equi (najviac sa spájalo) s prirodzeným spojením tým, že sa vráti iba jeden zo Spojených polí, Ak však niekedy použijete nerovnaké spojenie, môžete tiež chcieť vytvoriť neprirodzené spojenie vrátením oboch Spojených polí. K dispozícii sú ďalšie typy spojení. Celé spektrum pripojení bolo naposledy definované v programe 1992 a táto norma sa nazýva SQL-92. Niektoré spojenia nie sú pre používateľov Microsoft Excelu dôležité, pretože tieto spojenia robia veci, ktoré sú v Microsoft Exceli jednoduchšie.

Produkt Karteziánsky

Pri pokuse o vrátenie údajov z dvoch alebo niekoľkých tabuliek bez spojenia sa vytvorí tzv. Karteziánsky produkt. Karteziánsky produkt je definovaný ako všetky možné kombinácie riadkov vo všetkých tabuľkách. Uistite sa, že ste sa pripojili pred pokusom o vrátenie údajov, pretože Karteziánsky produkt v tabuľkách s mnohými záznamami a/alebo v mnohých tabuľkách môže trvať niekoľko hodín. Toto je karteziánsky produkt, ktorý sa používa v dvoch príkladových tabuľkách. Všimnite si, že táto tabuľka obsahuje len 3 záznamy typu 3 záznamy, ktoré obsahujú celkový počet 9 záznamov. Predstavte si však, že namiesto toho bola v tabuľke 100 záznamov krát 1 000 záznamov krát 10 000 záznamov; Tabuľka by mala obsahovať záznamy 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 Niektorí používatelia občas chcú používať Karteziánsky produkt. Väčšina používateľov, ktorí ich dostanú, sa však náhodne a často ich zamieňa. Keďže väčšina používateľov vylúči väčšinu polí v spojení, skutočný Karteziánsky produkt môže jednoducho vyzerať ako nepochopiteľný: Color_Field Red Red Red Blue Blue Blue Green Green Green Ak sa do Pattern_Table pridajú záznamy 100, tento dotaz bude mať 309 záznamov (103 záznamov pre každú červenú, modrú a zelenú). Produkty Karteziánsky majú relatívne jednoduché príkazy SELECT. Nasleduje postup, ako môže SQL vyhľadať vyššie uvedený produkt Karteziánsky: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table Praktickým príkladom produktu Karteziánsky by bolo vytvorenie zoznamu všetkých možných kombinácií možností v produkte tovaru s celkovými cenovými hodnotami pre každú kombináciu.