Operator SQL INKABA JOYINA: izibonelo-syntax nezici

Ukuthuthukisa iyiphi semininingwane ayihlanganisi kuphela ekudalweni nokugcwaliswa kwezikhala amatafula ngolwazi ahlukahlukene, kodwa futhi umsebenzi phambili idatha. Ukuze kahle zenza imisebenzi ngesikhathi kukhethwa idatha kusuka amatafula nokubika, sebenzisa ejwayelekile Khetha design.

Landa idatha kusuka amathebula

Uma sicabangela le nkinga choice idatha noma ukwakhiwa umbiko, ungakwazi ukunquma Izinga ukuhlinzwa. Njengomthetho, lapho esebenzelana nesizwe sina (ngokuvumelana ivolumu) yolwazi, okuyinto akhiwa, isibonelo, ezitolo intanethi noma izinkampani ezinkulu, ukukhethwa idatha akugcini itafula olulodwa. Njengomthetho, isampuli kungenzeka kusukela inombolo kahle esikhulu amathebula hhayi kuphela exhumene, kodwa futhi izidleke imibuzo / sub-imibuzo, okwenza uhlelo, kuye ngokuthi umsebenzi walo. Ngesampuli kwethebula ongasisebenzisa isakhiwo elula:

lapho Person - igama ithebula ofuna ukwenza data ukubuyisa.

Nokho, uma uzodinga ukhethe idatha kusuka amatafula amaningi, elinye imiklamo okujwayelekile ingasetshenziswa ukuze ujoyine amatafula amaningi.

yokuxhuma ezengeziwe amatafula Izindlela

Uma sicabanga ukusetshenziswa izakhiwo ezinjalo ezingeni eyinhloko, singakwazi ukuhlukanisa izindlela ezilandelayo ukuxhuma inani elidingekayo lamatafula isampula, okuyilezi:

1. Operator Inner Joyina.
2. Ngakwesokunxele Joyina noma, sesibili ukuqopha indlela, Kwesokunxele engaphandle Joyina.
3. Cross Joyina.
4. Okugcwele Joyina.

Ukusebenzisa opharetha ukuze ujoyine amathebula in umkhuba, ungafunda ngokucabangela ukusetshenziswa isitatimende SQL - Inner Joyina. Isibonelo ukusetshenziswa kwalo kungaba kanje:

SQL Ulimi futhi Joyina Inner Joyina opharetha ingasetshenziswa hhayi kuphela ngokuhlanganisa amatafula amabili noma ngaphezulu, kodwa futhi ukuxhuma namanye ase-imibuzo, okuyinto kusiza kakhulu umsebenzi abaphathi egciniwe, futhi, njengoba umthetho, kungaba ngokuphawulekayo ukusheshisa ukuqaliswa ethize, kuyinkimbinkimbi nombuzo izakhiwo.

Ukuhlanganisa idatha ku amatafula umugca ngemugca

Uma sicabangela uxhumano inombolo enkulu sub-imibuzo, nedatha ababebuthene ku itafula umugqa owodwa ngomugqa, ungase futhi usebenzise opharetha Union futhi Union Zonke.

Ukusetshenziswa zalezi zakhiwo kuzoncika unjiniyela ukusetha izimpokophelo imiphumela afuna ukufeza ekugcineni.

Incazelo Inner Joyina opharetha

Ezimweni eziningi, ukuba sihlanganise amatafula amaningi ngolimi SQL esetshenziswa Inner Joyina opharetha. Incazelo Inner Joyina SQL kulula kakhulu ukuba baqonde uhlelo isilinganiso ngubani usanda kuqala baqonde yolwazi. Uma sicabanga incazelo mshini umsebenzi kulesi sakhiwo, sithola lesi sithombe esilandelayo. I-opharetha logic sisonke lusekelwe owela kanye nokwenzeka izibonelo zalokho kuphela idatha ngamunye amatafula kufakwe nombuzo.

Uma sibheka lokhu uhlobo lomsebenzi ngokuya incazelo ingcaca, sithola SQL Inner Joyina isakhiwo opharetha, isibonelo okungase okwaboniswa uhlelo ezilandelayo:

Ngokwesibonelo, sinalo amatafula amabili, kuboniswa schematically figure. Nazo ngokufanayo, ube nenamba ezahlukene okufakiwe. Ngo ngayinye amatafula abe amasimu ukuthi zihlobene. Uma sizama ukuchaza umsebenzi opharetha ngesisekelo umdwebo, umphumela wabuya kuyoba iqoqo amarekhodi izibhebhe zombili, lapho amakamelo yimikhakha oxhumene luqondane. Uma sikubeka kalula, nombuzo kubuyisa kuphela labo amarekhodi (e inombolo etafuleni amabili), ogama idatha kuthebula inombolo eyodwa.

I-syntax Inner Joyina

Njengoba kushiwo ngaphambili, opharetha Inner Joyina, okungukuthi, e-syntax yayo, ngokungavamile elula. Ngoba inhlangano ubudlelwano phakathi amatafula ngaphakathi isampula olulodwa tando suffice ukukhumbula bese usebenzisa Umdwebo olandelako kwendikimba kokwakhiwa opharetha, okuyinto ebekiwe umugqa owodwa software SQL-ikhodi, okuyilezi:

• Inner Joyina [Uhlu Igama] ku- [inkambu ayisihluthulelo etafuleni lapho ukuxhuma] = [ensimini pluggable ayisihluthulelo etafuleni].

Ngokuxhumana kulesi sitatimende ngokuyinhloko esetshenziswa amatafula okhiye. Njengomthetho, eqenjini amathebula ukuthi ukugcina ulwazi mayelana nabasebenzi, ngaphambili kuchazwe Umuntu Ukwehlukaniswa futhi okungenani oyedwa anomlando ofanayo. Ngakho, thatha eduze bheka opharetha SQL Inner Joyina, isibonelo lapho okuye kwaboniswa kancane ekuqaleni.

incazelo ISIBONELO futhi uxhumano etafuleni isampula umsindo

Sine itafula Lomuntu, okuyinto igcine ulwazi mayelana nazo zonke abasebenza kule nkampani. Vele uqaphele ukuthi ukhiye master of etafuleni iwumkhakha - Pe_ID. Okuse-ke ngizoya isixha.

Eyesibili ithebula Ukwehlukaniswa izogcina ulwazi mayelana namahhovisi lapho abasebenzi emsebenzini. Yena, ixhunyiwe nge etafuleni Su_Person ensimini Lomuntu. Kusho ukuthini lokhu? Kususelwa schema idatha engase ishiwo ukuthi amayunithi etafuleni yerekhodi ngayinye kulelithebula "Abasebenzi" kuyoba ulwazi mayelana umnyango lapho zisebenza. Kungenxa yalesi zokuxhumana futhi sizosebenza opharetha Inner Joyina.

Ukuze ukusetshenziswa enembile ngaphezulu cabanga opharetha SQL Inner Joyina (izibonelo yokusetshenziswa kwawo amathebula elilodwa noma amabili). Uma sicabanga ngesibonelo etafuleni elifanayo ke zonke ulula:

ISIBONELO ukuxhuma Ngasengibamba izibhebhe zombili futhi subquery

Operator SQL Inner Joyina, izibonelo okuyizinto ukusetshenziswa ukubuyisa idatha kusuka amatafula amaningi zingahlelwa ngendlela esingaphezulu, usebenzela kwetintfo kancane isimiso. Okwenza ukuba umsebenzi Ngasengibamba izibhebhe zombili. Ngokwesibonelo, sinalo Sukani ithebula eligcina ulwazi mayelana nazo zonke neminyango ngamunye izigaba. Kulesi ithebula eqoshiwe inombolo division kanye nenombolo labasebenzi, futhi kumele kulekelelwa igama isampula idatha ye emnyangweni ngamunye. Lapho ubheka ikusasa, kuwufanele ethi ungasebenzisa izindlela ezimbili ukuxazulula le nkinga.

Indlela yokuqala ukuxhuma etafuleni umnyango lokuphuza. Hlela isicelo kuleli cala bangase babe kanje:

Eyesibili indlela yokuxazulula le nkinga - ukusebenzisa subquery lapho hhayi yonke idatha uzobe ezikhethiwe kusuka etafuleni umnyango, kodwa kuphela ezidingekayo. Lokhu kwehluke kakhulu endleleni yokuqala, kuyonciphisa nesikhathi sesicelo.

Kuyaphawuleka ukuthi ilungiselelo elinjalo abanakwethenjwa ngaso sonke isikhathi kusheshiswe isicelo umsebenzi. Ngezinye izikhathi kuba uma kudingekile ukusebenzisa idatha isampula okungeziwe itafula lesikhashana (uma ivolumu likhulu kakhulu), bese-ke lahlanganisa isampula main.

Isibonelo ukusetshenziswa Inner Joyina opharetha amasampula kusuka inqwaba amathebula

Ukwakhiwa kuhilela ukusetshenziswa imibuzo eziyinkimbinkimbi ukuze amanani isampula abalulekile idatha kumathebula subqueries oxhumene. Lezi zimfuneko onganelisa SQL Inner Joyina i-syntax. Izibonelo ukusetshenziswa opharetha, kulesi simo zingase zibe yinkimbinkimbi amasampula hhayi kuphela yezindawo eziningi isitoreji, kodwa futhi nge inani elikhulu subqueries ahlanganisiwe. Ukuze uthole isibonelo ukhonkolo, ungathatha idatha kusuka isampula uhlelo amatafula (Inner Joyina SQL sitatimende). Isibonelo - 3 etafuleni - kulokhu kuzodingeka isakhiwo kunalokho eziyinkimbinkimbi.

Kulokhu axhunyiwe (etafuleni okuyinhloko) kanye ezintathu olunye owethulwa ezimeni eziningana Ukukhetha idatha.

Lapho usebenzisa i-Inner Joyina opharetha kufanele sikhumbule ukuthi eziyinkimbinkimbi nombuzo, isikhathi eside luzoqala, ngakho kufanele sifune izindlela ukwenza ngokushesha futhi ukuxazulula le nkinga.

isiphetho

Ekugcineni, Ngithanda ukusho into eyodwa: ukusebenza nge yolwazi - akusiwona into eyayinzima kunazo zonke izinhlelo, ngakho ke uma ufuna wonke umuntu ongashadile uzokwazi ukuthola ulwazi ukwakha egciniwe, futhi ngokuhamba kwesikhathi, uzuza okuhlangenwe nakho, uthola ukusebenza nabo emsebenzini .