Operator SQL yomukati JOIN: Mienzaniso nemarongerwo uye zvinhu

Kuumba chero Database kunosanganisira kwete chete kusika uye zvinodiwa matafura nemashoko akasiyana-siyana, asi mberi basa pamwe mashoko. To nemazvo kuita mabasa akasiyana-siyana panguva anosarudza mashoko kubva matafura uye nhau, shandisa tichienzanisa Select kugadzira.

Mundotora date kubva matafura

Kana tikafunga dambudziko pakusarudza umboo kana kuvaka mushumo, unogona kuona padanho kunzwisisa hwokushanda. Kazhinji, kana pakubata chakakomba (maererano vhoriyamu) Databases, izvo kuumbwa, somuenzaniso, pakombuyuta zvitoro kana makambani makuru, mashoko kusarudzwa hakungoitwi chete tafura. Kazhinji, mharidzo ingava kubva kumbonyanya yokuwanda kwete chete kwakabatana matafura, asiwo nested queries / Sub-queries, izvo zvinoita programmer, zvichienderana ayo basa. Kuitira muenzaniso tafura imwe anogona kushandisa nyore mamiriro:

apo Person - zita tafura kubva izvo zvaunoda kuti mashoko acho.

Kana, zvisinei, unofanira kusarudza mashoko multiple matafura, mumwe vomureza mazano zvinogona kushandiswa abatane vakawanda pamatafura.

unobatanidza mamwe mahwendefa Nzira

Kana tikafunga kushandisa zvivako zvakadaro panguva chikuru vanochiva tinogona kusiyanisa zvinotevera zvenhengo kubatanidza nhamba inodiwa kuti mharidzo matafura, zvinoti:

1. Operator Inner Join.
2. Akasiya Join kana, ndiyo kurekodha nzira yechipiri, Left wokunze Join.
3. Cross Join.
4. Full Join.

Kushandisa dzaishandisa kubatana matafura kuzviita, unogona kudzidza nokukurukura kushandiswa SQL okuti - Inner Join. Muenzaniso kushandiswa kwayo aizova sezvinotevera:

Language SQL uye Join Inner Join opareta rinogona kushandiswa kwete chete nokuda tichibatanidza maviri kana kupfuura matafura, asiwo kubatanidza neeumwe hwezvekukurukurirana zvikuru basa Database vakuru Sub-queries,, uye, sezvo mutemo, anogona yekudyidzana kuwedzera Implementation nezvevamwe, zvakaoma mubvunzo zvivako.

Kusanganisa date pamahwandefa mutsetse norwonzi

Kana tikafunga kubatana kwakaita vazhinji Sub-queries, uye mashoko vakaungana mune imwe chete tafura mutsetse norwonzi, unogona kushandisa dzaishandisa Union uye Union All.

Kushandiswa zvivako izvi kuchaita kuvimba yokuvaka kuisa zvinangwa uye migumisiro kuti anoda kuwana pakupedzisira.

Description Inner Join opareta

Kazhinji, chokubatanidza akawanda mabwendefa SQL mutauro akashandiswa Inner Join opareta. Description Inner Join muna SQL rakanyatsojeka nyore kunzwisisa paavhareji programmer uyo kungotanga kunzwisisa Databases. Kana tikafunga rondedzero mashandiro basa mamiriro ichi, tinowana mufananidzo inotevera. The Kufunga opareta yose yakavakirwa pamusoro mazambuko uye mukana zvakabudiswa chete mashoko ari mumwe nomumwe matafura dziri mubvunzo.

Kana tikatarisa basa mhando iyi maererano kududzira hwakajeka, isu tiwane SQL Rwomukati Join opareta mamiriro, muenzaniso izvo zvinogona kuratidzwa pachishandiswa anotevera zano:

Somuenzaniso, tine matafura maviri, kuratidzwa schematically iri nhamba. Ivowo, vane yakasiyana nhamba vakanyoresa. In nechimwe matafura vane minda kuti vane ukama. Kana tikaedza kutsanangura basa opareta pachishandiswa mufananidzo, asi vakadzoka Zvichaguma seti zvinyorwa kubva matafura maviri, uko makamuri vari interconnected minda rienderane. Zvichitaurwa zviri nyore, kuti mubvunzo anodzoka zvinyorwa avo chete (mu tafura nhamba mbiri), ane mashoko ari patafura nhamba imwe.

The nemarongerwo yomukati Join

Sezvambotaurwa kare, kuti opareta Rwomumwoyo Join, kureva, nemarongerwo kwayo, zvinoshamisa nyore. Nokuti sangano ukama pakati matafura mukati rimwe mharidzo kuchaita wakakwanisa kurangarira uye kushandisa inotevera schematic Mufananidzo pakuvaka opareta, izvo nezvaro rimwe dzinza-software SQL-code, zvinoti:

• Inner Join [Table Name] pamusoro [huru munda tafura kuti dzaiitirwa] = [pluggable kiyi munda tafura].

Nokuti kukurukurirana mashoko iri kunyanya kushandiswa matafura kiyi. Kazhinji, iri boka matafura kuti kuchengetera mashoko pamusoro vashandi, kare akarondedzera Person East uye pane imwe zvinyorwa zvakafanana. Saka, tora tarisa kuti opareta SQL Rwomukati Join, muenzaniso izvo zvakaratidzwa zvishoma apfuura pedyo.

MUENZANISO tsananguro uye rinobatanidza Audio mharidzo tafura

Tine tafura Munhu, izvo rinochengeta mashoko pamusoro vashandi vose kushanda kambani. Just cherechedza kuti tenzi kiyi tafura riri munda - Pe_ID. Just pairi uye achaenda masumbu.

Chechipiri tafura East achachengeta ruzivo pamusoro mahofisi iyo vashandi vashande. Iyewo, kwakabatana Via Su_Person munda Person tafura. Zvinorevei izvi? Pachishandiswa mashoko schema zvinogona kunzi iri tafura vezvikwata chimwe chinyorwa ari tafura "Vashandi" richava ruzivo pamusoro dhipatimendi umo vanoshanda. Zviri yokukurukura ichi uye achashanda opareta Inner Join.

Kuti uwane nzwisisa kushandiswa funga opareta SQL Rwomukati Join (mienzaniso kushandiswa kwayo mumwe kana miviri matafura). Kana isu funga muenzaniso iwoyo tafura, ipapo zvose zviri nyore:

MUENZANISO batanidza maviri matafura uye subquery

Operator SQL Inner Join, mienzaniso izvo kushandiswa dzosa mashoko multiple matafura zvinogona yakarongwa pamusoro nenzira, zviri kushanda zvishoma kuomesa kwakaita musimboti. Kuti omesa basa mabwendefa maviri. Somuenzaniso, tine Ibvai tafura kuti rinochengeta mashoko pamusoro ose madhipatimendi nerimwe mapoka. In tafura ichi chakanyorwa kupesana nhamba uye mushandi nhamba, uye inofanira vaisanganisa nezita mharidzo Data nerimwe dhipatimendi. Achitarira mberi, kuchitaura kuti unogona kushandisa nzira mbiri kugadzirisa dambudziko iri.

Nzira yokutanga ndiyo kubatanidza tafura dhipatimendi kuti mharidzo. Kuronga chikumbiro ichi nyaya angava sezvinotevera:

The nzira kugadzirisa dambudziko rechipiri - ndiko kushandisa subquery pasingazosiyiwi ose Data zvichabviswa kusarudzwa kubva patafura dhipatimendi, asi chete anodiwa. Izvi zvakasiyana nzira yokutanga, zvichaita kuderedza nguva chikumbiro.

Zviri kucherechedza mutengo kuti wakadaro urongwa havangagoni rikurumidze basa chikumbiro. Dzimwe nguva pane zviitiko kana zvakakodzera kushandisa wokuwedzera mharidzo Data mugomba pfupi tafura (kana bhuku iri guru), uye zvino akanga pamwe huru mharidzo.

Muenzaniso kushandiswa Rwomumwoyo Join opareta kuti ivhu kubva vakawanda matafura

The kuvakwa kunosanganisira kushandisa queries zvakaoma kuti mharidzo zvinokosha yakawanda date matafura uye subqueries interconnected. Izvi zvinodiwa anogona kugutsa SQL Rwomukati Join nemarongerwo. Mienzaniso kushandiswa opareta, muchiitiko ichi kunogona kuoma kwete chete mimwe dzakawanda nzvimbo okuchengetera, asiwo vakawanda nested subqueries. Nokuti muenzaniso kongiri, unogona kutora mashoko kubva wezvaizogona ezvinhu pamatafura (Inner Join SQL mashoko). Muenzaniso - 3 tafura - mune iyi achava nani kunzwisisa chivakwa.

Kana ichi zvakabatanidzwa (chikuru tafura) uye nhatu mberi chinotaurwa akawanda mashoko kusarudzwa ezvinhu.

Kana tichishandisa Inner Join opareta vanofanira kuyeuka kuti zvikuru kunzwisisa kuti mubvunzo, kwenguva refu zvichaita chinozoiswa, unofanira saka tsvaka nzira nokukurumidza kuita uye kugadzirisa dambudziko.

mhedziso

Pakupedzisira, ndinoda kutaura chimwe chinhu: kushanda Databases - hazvisi chinhu zvakaoma mapurogiramu, saka kana uchida ose kuroora munhu achakwanisa kuwana zivo kuvaka Database, uye nokufamba kwenguva, akawana ruzivo, unokwanisa kushanda navo uri nyanzvi yakati .