Under ganska lång tid, i början av min karriär som webbutvecklare, arbetade jag med den här databasen så gott jag kunde, men jag visste inte mycket. Han komponerade enkla primitiva frågor, och ibland infogade han frågor i loopar. Då fick jag tyvärr inte tag i rätt bok om MySQL och fick lära mig genom att trial and error. Många artiklar på Internet förmedlade på något sätt inte omedelbart den underbara MySQL-frågan - JOIN.
I den här publikationen kommer jag att prata om alla möjliga alternativ för att arbeta med JOIN och dessutom kommer jag att presentera principen för driften av varje kommando - visuellt.
Separat är det värt att notera punkterna 5, 6 och 7. I själva verket kopplar dessa frågor inte samman två tabeller, utan utesluter snarare kolumner från en tabell som finns i den andra. I själva verket kan detta vara mycket användbart.
Inre JOIN 
En av de vanligaste förfrågningarna inträffar extremt ofta. Den här frågan returnerar alla poster från den vänstra tabellen (tabell A) och poster från (tabell B), men returnerar bara matchande kolumner.
Exempelbegäran:
Visa kod SQL
| VÄLJA< select_list>FRÅN Tabell_A A INRE FOGA Tabell_B B PÅ A. Nyckel = B. Nyckel |
Vänster JOIN 
Den här frågan returnerar alla kolumner från den vänstra tabellen (tabell A), såväl som alla kolumner från den högra tabellen (tabell B), men bara de som matchar kolumnerna från den vänstra tabellen.
Exempelbegäran:
Visa kod SQL
| VÄLJA< select_list>FRÅN Tabell_A A VÄNSTER FÖRENA TABELL_B B PÅ A. Tangent = B. Tangent |
Rätt GÅ MED 
Liknar den föregående frågan, men kommer att returnera alla kolumner från den högra tabellen (Tabell B), samt alla kolumner från den vänstra tabellen (Tabell A) som matchar kolumnerna från den högra tabellen.
Exempelbegäran:
Visa kod SQL
| VÄLJA< select_list>FRÅN Tabell_A A RÄTT FÖRENA TABELL_B B PÅ A. Nyckel = B. Nyckel |
Yttre JOIN 
Ofta skrivs denna fråga som FULL OUTER JOIN eller FULL JOIN, alla varianter utför en åtgärd, nämligen att returnera alla kolumner från båda tabellerna, medan de matchande kolumnerna kommer att överlappas av kolumnerna från den vänstra tabellen.
Exempelbegäran:
Visa kod SQL
| VÄLJA< select_list>FRÅN tabell_A A FULLSTÄNDIG YTTRE JOIN Tabell_B B PÅ A. Nyckel = B. Nyckel |
Vänster Exklusive JOIN 
Den här frågan returnerar alla kolumner från den vänstra tabellen (tabell A) som inte matchar kolumnerna från den högra tabellen (tabell B).
Exempelbegäran:
Visa kod SQL
SQL JOIN-operatorn är utformad för att sammanfoga två eller flera databastabeller baserat på matchande villkor. Denna operatör finns endast i relationsdatabaser. Det är tack vare JOIN som relationsdatabaser har så kraftfull funktionalitet som tillåter inte bara datalagring, utan även deras, åtminstone den enklaste, analys med hjälp av frågor. Låt oss titta på huvudnyanserna av att skriva SQL-frågor med JOIN-operatören, som är gemensamma för alla DBMS (databashanteringssystem). För att sammanfoga två tabeller har SQL JOIN-operatorn följande syntax:
VÄLJ COLUMN_NAMES (1..N) FRÅN TABLE_NAME_1 GÅ MED TABLE_NAME_2 PÅ VILLKOR
En eller flera länkar med JOIN-operatören kan följas av en valfri WHERE eller HAVING-sektion, där, precis som i en enkel SELECT-fråga, urvalsvillkoret anges. Gemensamt för alla DBMS:er är att i denna konstruktion kan istället för JOIN, INNER JOIN, LEFT OUTER JOIN, HÖGER YTTRE JOIN, FULL YTTRE JOIN, CROSS JOIN (eller alternativt ett kommatecken) anges.
INRE FÖRENADEEn fråga med INNER JOIN-operatorn är utformad för att sammanfoga tabeller och visa den resulterande tabellen där data fullständigt skär varandra enligt villkoret som anges efter PÅ.
En enkel JOIN gör samma sak. Således är ordet INNER valfritt.
Exempel 1. Det finns en databas för en annonsportal. Den innehåller en tabell över kategorier (reklamkategorier) och delar (delar, eller på annat sätt - rubriker, som hör till kategorier). Till exempel hör delarna Lägenheter och Stugor till kategorin Fastigheter och delarna Bilar och Motorcyklar tillhör kategorin Transport. Dessa tabeller med ifyllda data ser ut så här:
Delartabell:
Observera att i tabellen Delar har böcker en katt - en länk till en kategori som inte finns i tabellen Kategorier, och i tabellen Kategorier har utrustning ett Cat_ID - primärnyckel, som inte hänvisas till i tabellen Delar. Det krävs att data från dessa två tabeller kombineras så att den resulterande tabellen innehåller fälten Part, Cat och Price och så att data helt överlappar enligt villkoret. Villkoret är en matchning mellan kategoriidentifieraren i tabellen Kategorier och länken till kategorin i tabellen Delar. För att göra detta, skriv följande begäran:
VÄLJ PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Pris FRÅN DELAR INNER JOIN KATEGORIER PÅ PARTS.Cat = CATEGORIES.Cat_ID
| Del | Katt | Pris |
| Lägenheter | 505 | 210,00 |
| Bilar | 205 | 160,00 |
| Brädor | 10 | 105,00 |
| Garderober | 30 | 77,00 |
Det finns inga böcker i den resulterande tabellen eftersom denna post refererar till en kategori som inte finns i tabellen Kategorier och Utrustning eftersom denna post har en främmande nyckel i tabellen Kategorier som inte hänvisas till i tabellen Delar.
I vissa fall, när du går med i tabeller, kan du skapa mindre besvärliga frågor med predikatet EXISTS och utan att använda JOIN.
Det finns en databas "Theater". Play-tabellen innehåller data om produktioner. Lagbord - om skådespelarnas roller. Skådespelarbordet handlar om skådespelare. Direktörstabell - om direktörer. Tabellfält, primär- och främmande nycklar kan ses i figuren nedan (vänsterklicka för att förstora).
Exempel 3. Skriv ut en lista över skådespelare som spelar mer än en roll i en föreställning och antalet roller.
Använd JOIN-operatören 1 gång. Använd HAVING, GROUP BY .
Ledtråd. Operatorn HAVING tillämpas på antalet roller som räknas av COUNT-aggregatfunktionen.
VÄNSTER YTTRE FOGAEn fråga med operatorn LEFT OUTER JOIN är utformad för att sammanfoga tabeller och visa en resulterande tabell där data är helt genomskärs av villkoret som anges efter ON, och kompletteras med poster från den första (vänstra) tabellen, även om de inte gör det uppfylla villkoret. Poster i den vänstra tabellen som inte uppfyller villkoret kommer att ha ett kolumnvärde från den högra tabellen som är NULL (odefinierat).
Exempel 4. Databasen och tabellerna är desamma som i exempel 1.
För att få en resulterande tabell där data från två tabeller helt skär varandra efter villkor och kompletteras med all data från tabellen Parts som inte uppfyller villkoret, skriv följande fråga:
VÄLJ PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Pris FRÅN DELAR VÄNSTER YTTRE JOIN KATEGORIER PÅ PARTS.Cat = CATEGORIES.Cat_ID
Resultatet av frågan blir följande tabell:
| Del | Katt | Pris |
| Lägenheter | 505 | 210,00 |
| Bilar | 205 | 160,00 |
| Brädor | 10 | 105,00 |
| Garderober | 30 | 77,00 |
| Böcker | 160 | NULL |
I den resulterande tabellen, till skillnad från tabellen från exempel 1, finns det Böcker, men värdet på kolumnen Pris är NULL, eftersom denna post har en kategoriidentifierare som inte finns i tabellen Kategorier.
HÖGER YTTRE JOINEn fråga med operatorn RIGHT OUTER JOIN är utformad för att sammanfoga tabeller och visa en resulterande tabell i vilken data är fullständigt genomskuren av villkoret som anges efter ON, och kompletteras med poster från den andra (höger) tabellen, även om de inte gör det uppfylla villkoret. Poster i den högra tabellen som inte uppfyller villkoret kommer att ha ett kolumnvärde från den vänstra tabellen som är NULL (odefinierat).
Exempel 5.
För att få den resulterande tabellen, där data från två tabeller helt skär varandra enligt villkoret och kompletteras med all data från tabellen Kategorier som inte uppfyller villkoret, skriv följande fråga:
VÄLJ PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Pris FRÅN DELAR HÖGER YTTRE JOIN KATEGORIER PÅ PARTS.Cat = CATEGORIES.Cat_ID
Resultatet av frågan blir följande tabell:
| Del | Katt | Pris |
| Lägenheter | 505 | 210,00 |
| Bilar | 205 | 160,00 |
| Brädor | 10 | 105,00 |
| Garderober | 30 | 77,00 |
| NULL | 45 | 65,00 |
I den resulterande tabellen, till skillnad från tabellen från exempel 1, finns det en post med kategori 45 och pris 65,00, men värdet för Part-kolumnen är NULL, eftersom denna post har en kategoriidentifierare som inte hänvisas till i Parts-tabellen .
FULL YTTRE JOIN (full yttre JOIN)En fråga med operatorn FULL OUTER JOIN är utformad för att sammanfoga tabeller och visa en resulterande tabell där data är fullständigt genomskuren av villkoret som anges efter ON, och kompletteras med poster från den första (vänster) och andra (höger) tabellen, även om de inte uppfyller villkoret. Poster som inte uppfyller villkoret kommer att ha kolumner från den andra tabellen som är NULL (odefinierad).
Exempel 6. Databasen och tabellerna är desamma som i de tidigare exemplen.
För att erhålla den resulterande tabellen, där data från de två tabellerna helt skär varandra efter villkor och kompletteras med all data från både tabellen Delar och tabellen Kategorier som inte uppfyller villkoret, skriv följande fråga:
VÄLJ PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Pris FRÅN DELAR FULLSTÄNDIG YTTRE JOIN-KATEGORIER PÅ PARTS.Cat = CATEGORIES.Cat_ID
Resultatet av frågan blir följande tabell:
| Del | Katt | Pris |
| Lägenheter | 505 | 210,00 |
| Bilar | 205 | 160,00 |
| Brädor | 10 | 105,00 |
| Garderober | 30 | 77,00 |
| Böcker | 160 | NULL |
| NULL | 45 | 65,00 |
Den resulterande tabellen innehåller poster av böcker (från den vänstra tabellen) och med kategori 45 (från den högra tabellen), och den första av dem har ett odefinierat pris (kolumn från den högra tabellen), och den andra har en odefinierad del (kolumn) från den vänstra tabellen).
Alias för sammanfogade bordI tidigare frågor angav vi de fullständiga namnen på dessa tabeller med namnen på de extraherade kolumnerna från olika tabeller. Sådana frågor ser besvärliga ut: samma ord upprepas flera gånger. Är det möjligt att på något sätt förenkla designen? Det visar sig att det är möjligt. För att göra detta bör du använda tabellalias - deras förkortade namn. Ett smeknamn kan också bestå av en bokstav. Valfritt antal bokstäver i ett alias är möjligt, det viktigaste är att begäran efter förkortning är förståelig för dig. Allmän regel: I den sektion av frågan som definierar kopplingen, det vill säga runt ordet JOIN, måste du ange de fullständigt kvalificerade tabellnamnen, och varje namn måste följas av ett tabellalias.
Exempel 7: Skriv om frågan från exempel 1 med alias för de sammanfogade tabellerna.
Begäran kommer att vara följande:
VÄLJ P.Part, C.Cat_ID AS Cat, C.Pris FRÅN DELAR P INNER JOIN CATEGORIES C ON P.Cat = C.Cat_ID
Frågan kommer att returnera samma sak som frågan i exempel 1, men den är mycket mer kompakt.
GÅ MED och gå med i fler än två bordRelationsdatabaser måste uppfylla kraven på dataintegritet och icke-redundans, och därför kan data om en affärsprocess finnas inte bara i en, två, utan också i tre eller flera tabeller. I dessa fall används kedjor av sammankopplade tabeller för att analysera data: till exempel innehåller en (första) tabell några kvantitativ indikator, den andra tabellen är ansluten till den första och tredje med främmande nycklar - data skär varandra, men endast den tredje tabellen innehåller ett villkor, beroende på vilket en kvantitativ indikator från den första tabellen kan härledas. Och det kan bli ännu fler bord. Med SQL JOIN-operatorn kan du sammanfoga ett stort antal tabeller i en enda fråga. I sådana frågor följs en kopplingssektion av en annan, och varje efterföljande JOIN ansluter till nästa tabell den tabell som var den andra i föregående länk i kedjan. Så syntaxen är SQL-fråga för att slå samman fler än två tabeller enligt följande:
VÄLJ COLUMN_NAMES (1..N) FRÅN TABLE_NAME_1 JOIN TABLE_NAME_2 PÅ VILLKOR JOIN TABLE_NAME_3 PÅ VILLKOR... GÅ MED TABLE_NAME_M PÅ VILLKOR
Exempel 8. Databasen är densamma som i de tidigare exemplen. I det här exemplet kommer tabellen Annonser att läggas till i tabellerna Kategorier och Delar, som innehåller data om annonser som publicerats på portalen. Här är ett fragment av annonstabellen, där det bland posterna finns register över de annonser vars publiceringstid löper ut den 04/02/2018.
| Hjälpa | Part_ID | Datum_start | Datum_slut | Text |
| 21 | 1 | "2018-02-11" | "2018-04-20" | "Jag säljer..." |
| 22 | 1 | "2018-02-11" | "2018-05-12" | "Jag säljer..." |
| ... | ... | ... | ... | ... |
| 27 | 1 | "2018-02-11" | "2018-04-02" | "Jag säljer..." |
| 28 | 2 | "2018-02-11" | "2018-04-21" | "Jag säljer..." |
| 29 | 2 | "2018-02-11" | "2018-04-02" | "Jag säljer..." |
| 30 | 3 | "2018-02-11" | "2018-04-22" | "Jag säljer..." |
| 31 | 4 | "2018-02-11" | "2018-05-02" | "Jag säljer..." |
| 32 | 4 | "2018-02-11" | "2018-04-13" | "Jag säljer..." |
| 33 | 3 | "2018-02-11" | "2018-04-12" | "Jag säljer..." |
| 34 | 4 | "2018-02-11" | "2018-04-23" | "Jag säljer..." |
Låt oss föreställa oss att idag är "2018-04-02", det vill säga detta värde tas av funktionen CURDATE() - det aktuella datumet. Du vill veta vilka kategorier de annonser vars publiceringsfrist är idag tillhör. Kategorinamn finns bara i tabellen KATEGORIER och annonsens utgångsdatum finns bara i tabellen ADS. I tabellen DELAR - delar av kategorier (eller enklare, underkategorier) av publicerade annonser. Men den främmande nyckeln Cat_ID länkar PARTS-tabellen till CATEGORIES-tabellen, och ADS-tabellen länkar den främmande nyckeln Part_ID till PARTS-tabellen. Därför kopplar vi samman tre tabeller i en fråga och denna fråga kan kallas en kedja med maximal korrekthet.
Begäran kommer att vara följande:
Resultatet av frågan är en tabell som innehåller namnen på två kategorier - "Fastigheter" och "Transport":
| Cat_name |
| Fastighet |
| Transport |
Användande SQL-sats CROSS JOIN i sin enklaste form – utan ett sammanfogningsvillkor – implementerar den kartesiska produktoperationen i relationalgebra. Resultatet av en sådan sammanfogning blir sammanlänkningen av varje rad i den första tabellen med varje rad i den andra tabellen. Tabeller kan skrivas i en fråga antingen genom en CROSS JOIN-operator eller separerade med kommatecken.
Exempel 9. Databasen är fortfarande densamma, tabellerna är kategorier och delar. Implementera den kartesiska produktdriften för dessa två tabeller.
Begäran kommer att vara följande:
VÄLJ (*) Kategorier CROSS JOIN Delar
Eller utan att uttryckligen ange CROSS JOIN - avgränsade med kommatecken:
VÄLJ (*) Kategorier, delar
Frågan returnerar en tabell med 5 * 5 = 25 rader, varav ett fragment ges nedan:
| Cat_ID | Cat_name | Pris | Part_ID | Del | Katt |
| 10 | Byggmaterial | 105,00 | 1 | Lägenheter | 505 |
| 10 | Byggmaterial | 105,00 | 2 | Bilar | 205 |
| 10 | Byggmaterial | 105,00 | 3 | Brädor | 10 |
| 10 | Byggmaterial | 105,00 | 4 | Garderober | 30 |
| 10 | Byggmaterial | 105,00 | 5 | Böcker | 160 |
| ... | ... | ... | ... | ... | ... |
| 45 | Teknik | 65,00 | 1 | Lägenheter | 505 |
| 45 | Teknik | 65,00 | 2 | Bilar | 205 |
| 45 | Teknik | 65,00 | 3 | Brädor | 10 |
| 45 | Teknik | 65,00 | 4 | Garderober | 30 |
| 45 | Teknik | 65,00 | 5 | Böcker | 160 |
Som framgår av exemplet, om resultatet av en sådan begäran har något värde, så är det kanske ett visuellt värde i vissa fall när det inte finns något behov av att visa strukturerad information, särskilt inte ens det enklaste analytiska provet. Förresten, du kan ange kolumnerna som ska visas från varje tabell, men även då kommer informationsvärdet för en sådan fråga inte att öka.
Men för CROSS JOIN kan du ställa in ett anslutningsvillkor! Resultatet blir ett helt annat. När du använder kommaoperatorn istället för att explicit specificera CROSS JOIN, specificeras kopplingsvillkoret inte av ON-ordet, utan av WHERE-ordet.
Exempel 10. Samma annonsportaldatabas, kategorier och delar tabeller. Med hjälp av en korskoppling kopplar du ihop tabeller så att data helt överlappar enligt villkoret. Villkoret är en matchning mellan kategoriidentifieraren i tabellen Kategorier och länken till kategorin i tabellen Delar.
Begäran kommer att vara följande:
Frågan returnerar samma sak som frågan i exempel 1:
| Del | Katt | Pris |
| Lägenheter | 505 | 210,00 |
| Bilar | 205 | 160,00 |
| Brädor | 10 | 105,00 |
| Garderober | 30 | 77,00 |
Och denna slump är ingen tillfällighet. En fråga med en korskoppling enligt kopplingsvillkoret är helt lik en fråga med en inre koppling - INNER JOIN - eller, givet att ordet INNER är valfritt, bara JOIN.
Vilken frågevariant som ska användas är alltså en fråga om stil eller till och med en vana hos databasspecialisten. Kanske kan en korsfogning med ett villkor på två bord vara mer kompakt. Men fördelen med en korskoppling för mer än två bord (detta är också möjligt) är mycket diskutabel. I det här fallet listas WHERE-villkoren för korsningen genom ordet AND. Denna design kan vara krånglig och svår att läsa om det också finns en WHERE-sats med hämtningsvillkor i slutet av frågan.
Relationsdatabaser och SQL-språk
I MySQL hämta med JOIN kan du göra på olika sätt. Vi kommer att försöka överväga alla dessa typer av förfrågningar. Här är en lista över alla frågor som involverar JOIN:
Och här är en illustration av dessa typer av JOIN:
Jag kommer att bifoga filer från vår sida till artikeln, bland vilka det kommer att finnas join.php där jag kommer att visa alla poster med olika JOIN-operatorer.
INRE FÖRENADEVi börjar med den här INNER JOIN-operatorn, eftersom den här operatorn aktiveras som standard om du bara skriver JOIN i frågan. Denna operatör väljer alla poster från två tabeller där villkoret efter ON-operatorn är uppfyllt. Vi har två tabeller Filer och meddelanden:
Meddelandetabell:
Frågan med JOIN blir följande:
SELECT * FROM Messages INNER JOIN-filer PÅ Messages.fid=Files.fid
Som ett resultat kommer följande poster att visas:
Filtabell:
LEFT JOIN kommer att behövas när vi visar alla meddelandeposter, och om det finns en bifogad fil eller inte kommer vi att kontrollera genom PHP.
LEFT JOIN utan korsningar med höger bordLEFT JOIN returnerar alla poster från den vänstra tabellen, förutom de vars fid matchar i den högra tabellen.
Meddelandetabell:
En fråga med en LEFT JOIN utan korsningar skulle vara:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid WHERE Files.fid IS NULL
Som ett resultat får vi följande exempel:
Filtabell:
RIGHT JOIN kommer att behövas när vi visar alla bifogade filer, oavsett om de används eller inte, bara alla filer.
HÖGER JOIN utan korsningarRIGHT JOIN utan skärningspunkter visar alla poster från den högra tabellen, förutom de som har korsningar med den vänstra tabellen.
Meddelandetabell:
En fråga med en RIGHT JOIN utan korsningar skulle vara:
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid WHERE Messages.fid IS NULL
På så sätt får vi följande data:
| mitt- | brödtext | fid | väg |
| NULL | NULL | 1 | /filer/1.png |
RIGHT JOIN kommer att behövas när du visar alla bifogade filer som inte är bifogade till några meddelanden. Till exempel om vi vill visa filer som inte används.
FULLSTÄNDIG YTTRE JOINTrots att i SQL-språk Det finns en FULLSTÄNDIG YTTRE JOIN, men MySQL har inte denna operatör. Faktum är att en sådan operatör är en enorm belastning på servern. Nu har vi 3 filer och 3 meddelanden, medan 4 rader bildas som ett resultat av förfrågan. Jag är inte säker på om det är en bra idé att skriva en fråga som kombinerar två frågor, LEFT JOIN och RIGHT JOIN. Men det är fortfarande möjligt att efterlikna en FULL OUTER JOIN-förfrågan.
Meddelandetabell:
Att emulera en fråga med FULL OUTER JOIN skulle vara som följer:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid UNION SELECT * FROM Messages RIGHT JOIN Filer ON Messages.fid = Files.fid
I den här frågan använder vi UNION-operatorn för att sammanfoga två frågor LEFT JOIN och RIGHT JOIN.
Som ett resultat kommer vi att få följande register:
| mitt- | brödtext | fid | väg |
| 1 | Testa | 2 | /filer/2.png |
| 2 | Hej | NULL | NULL |
| 3 | Hej | 3 | /filer/3.png |
| NULL | NULL | 1 | /filer/1.png |
Och här har jag svårt att säga varför det krävs en FULL YTTRE JOIN. Men eftersom det är i SQL kommer det förmodligen att behövas senare.
FULL YTTRE JOIN utan korsningarEn annan typ av JOIN är ännu galnare än bara FULL OUTER JOIN, nämligen FULL OUTER JOIN utan korsningar. Jag kan inte ens föreslå var denna typ av JOIN kan användas. För som ett resultat tar vi emot filer som inte används och meddelanden utan filer. Och som du säkert redan gissat så finns den här operatören inte heller i MySQL. Allt som återstår är att emulera det med två operatorer: LEFT JOIN utan uppräkningar och RIGHT JOIN utan korsningar.
Emulering av en FULL OUTER JOIN-förfrågan utan korsningar:
$sql = "VÄLJ * FRÅN Meddelanden LEFT JOIN Filer ON Messages.fid = Files.fid VAR Files.fid ÄR NULL UNION SELECT * FROM Messages RIGHT JOIN Filer ON Messages.fid = Files.fid WHERE Messages.fid ÄR NULL";
Som ett resultat (källtabellerna är desamma som i exemplet med FULL OUTER JOIN) får vi:
| mitt- | brödtext | fid | väg |
| 2 | Hej | NULL | NULL |
| NULL | NULL | 1 | /filer/1.png |
Det är förmodligen allt, i nästa lektion kommer vi att börja skriva ännu mer komplexa frågor till flera tabeller samtidigt.
Anledningen till att jag skrev den här artikeln var en del debatt i en av de länkade grupperna relaterade till MySQL, samt kommunikation med kollegor och Habro-folk :-)
I den här artikeln ville jag skriva vad JOINs är i MySQL i allmänhet och hur du kan optimera frågor med dem.
Vad är JOINs i MySQLI MySQL används termen JOIN mycket mer utbrett än du kanske tror. Här kan JOIN kallas inte bara en fråga som kombinerar resultat från flera tabeller, utan även en fråga till en tabell, till exempel är SELECT på en tabell också en join.
Detta beror på att exekveringsalgoritmen för join i MySQL är implementerad med hjälp av kapslade loopar. Dessa. Varje efterföljande JOIN är en extra kapslad loop. För att exekvera en fråga och returnera alla poster som uppfyller ett villkor, utför MySQL en loop och kör genom posterna i den första tabellen parallellt och kontrollerar efterlevnad av villkoren som beskrivs i förfrågan när poster hittas som uppfyller villkor, en kapslad loop genom den andra tabellen letar efter poster som matchar den första och uppfyller kontrollvillkoren, etc. .d.
Exempel på en vanlig fråga med INNER JOIN
VÄLJADär P - villkor för sammanslagning av tabeller och filter i WHERE-villkoret.
Du kan föreställa dig följande pseudokod för att utföra en sådan begäran.
FÖR varje rad t1 i Tabell1 (Där konstruktionen t1||t2||t3 betyder sammanlänkning av kolumner från olika tabeller.
Om frågan innehåller OUTER JOINs, till exempel, LEFT OUTER JOIN
VÄLJADå kommer algoritmen för att köra denna MySQL-fråga att se ut ungefär så här
FÖR varje rad t1 i T1 (Så, som vi kan se, är JOINs bara en grupp av kapslade loopar. Så varför går UNION och SELECT och frågor med SUBQUERY också med i MySQL?
MySQL-optimeraren försöker föra frågor till den form i vilken det är bekvämare för den att bearbeta och exekvera frågor enligt standardschemat.
Med SELECT är allt klart - bara en slinga utan kapslade slingor. Alla UNION exekveras som separata frågor och resultaten läggs till i en temporär tabell, och sedan fungerar MySQL med denna tabell, d.v.s. gå igenom skivorna i den. Det är samma historia med Subquery.
Genom att föra allt till en mall, till exempel, skriver MySQL om alla RIGHT JOIN-frågor med LEFT JOIN-ekvivalenter.
Men strategin att köra frågor genom kapslade loopar medför vissa begränsningar, till exempel på grund av detta schema stöder MySQL inte exekvering av FULL OUTER JOIN-frågor.
Men resultatet av en sådan fråga kan erhållas med UNION av två frågor på LEFT JOIN och RIGHT JOIN
Ett exempel på själva begäran kan ses på wikilänken.
Till skillnad från andra RDBMS genererar MySQL inte bytekod för att exekvera en fråga istället, MySQL genererar en lista med satser i en trädform som sökmotorn följer när frågan körs.
Detta träd ser ut så här och kallas "vänsterdjupt träd"
Till skillnad från balanserade träd (bushy plan), som används i andra DBMS (till exempel Oracle)
Låt oss nu gå vidare till den mest intressanta delen - gå med optimering.
MySQL-optimeraren, nämligen den del som är ansvarig för att optimera JOINs, väljer i vilken ordning befintliga tabeller ska slås samman, eftersom du kan få samma resultat (dataset) med olika ordningsföljder av tabeller i sammanslagningen. MySQL-optimeraren utvärderar kostnaden för olika planer och väljer den med lägst kostnad. Enheten för utvärdering är driften av en enda läsning av en 4 kilobyte sida med data från en slumpmässig plats på disken.
För den valda planen kan du ta reda på kostnaden genom att utföra kommandot
VISA SESSIONSSTATUS SOM "Last_query_cost";
Uppskattningen baseras på statistik: antalet minnessidor som tas upp av tabellen och/eller indexen för denna tabell, kardinalitet (antal unika värden) index, längd på poster och index, deras fördelning osv. Under sin utvärdering antar inte optimeraren att några delar kommer att hamna i cachen.
Ibland kan analysatoroptimeraren inte analysera alla möjliga genomförandeplaner och väljer fel. Till exempel, om vi har INNER JOIN på 3 bord, då möjliga alternativ analysatorn har 3! = 6, och om vi slår samman 10 tabeller, så finns det redan 10 möjliga alternativ! = 3628800... MySQL kan inte analysera så många alternativ, så i det här fallet använder den en girig sökalgoritm.
Men du bör inte tillämpa det här hacket på alla frågor, i hopp om att optimera på matchningar och spara tid på att upprätta en exekveringsplan för frågor med optimeraren och lägga till STRAIGH_JOIN till alla frågor med joins, eftersom Dataförändringarna och sammanslagningen, vilket är optimalt nu, kan upphöra att vara optimalt med tiden, och då kommer frågorna att börja släpa väldigt mycket.
Som nämnts ovan placeras resultaten av kopplingar i tillfälliga tabeller, så det är ofta lämpligt att använda en "deriverad tabell" där vi ställer alla villkor vi behöver för urvalet, och även specificerar LIMIT och sorteringsordning. I i detta fall vi kommer att bli av med dataredundans i den tillfälliga tabellen, och även sortera i ett tidigt skede (baserat på resultatet av ett prov, och inte den slutliga limningen, vilket kommer att minska storleken på de poster som kommer att sorteras).
Ett standardexempel på tillvägagångssättet som beskrivs ovan. Ett enkelt urval för en många-till-många-relation: nyheter och taggar för det.
VÄLJAOch till sist, ett litet problem som jag ibland ställer under intervjuer :-)
Det finns en nyhetsbloggsida. Det finns sådana enheter som nyheter och kommentarer om dem.
Uppgiften är att skriva en fråga som visar en lista med 10 nyheter av en viss typ (angivna av användaren), publikationer sorterade efter tid i kronologisk ordning, och inte heller visa mer än 10 senaste kommentarer för var och en av dessa nyheter, d.v.s. om det finns fler kommentarer visar vi bara de sista 10.
Allt måste göras på en begäran. Ja, det här kanske inte är det mesta bästa sättet, och du är fri att föreslå en annan lösning :-)
Taggar: Lägg till taggar
I den här handledningen kommer du att lära dig hur du använder JOINS (INNER och OUTTER) i MySQL med syntax, bilder och exempel.
MySQL JOINS används för att hämta data från flera tabeller. En JOIN utförs när två eller flera tabeller sammanfogas i en SQL-sats.
Det finns olika typer MySQL-anslutningar:
Låt oss titta på MySQL JOIN-syntaxen och studera MySQL JOIN-exempel.
INNER JOIN (enkel koppling)Troligtvis har du redan skrivit frågor som använder MySQL INNER JOIN. Detta är den vanligaste typen av anslutning. MySQL INNER JOINS returnerar alla rader från flera tabeller där kopplingsvillkoren är uppfyllda.
SyntaxINNER JOIN-syntax i MySQL:
VÄLJ kolumner
FRÅN tabell 1
INNER JOIN bord2
I den här figuren returnerar MySQL INNER JOIN en skuggad region:
MySQL INNER JOIN kommer att returnera poster där tabell1 och tabell2 skär varandra.
ExempelNedan är ett exempel på MySQL INNER JOIN:
MySQL
Vi har ytterligare en ordertabell med tre fält (order_id, leverantör_id och order_datum). Den innehåller följande data:
| 500125 | 10000 | 05.05.2015 |
| 500126 | 10001 | 08.02.2016 |
| 500127 | 10004 | 06.01.2017 |
Om vi kör MySQL SELECT-satsen (som innehåller en INNER JOIN) nedan:
MySQL
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FRÅN leverantörer INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Raderna för Microsoft och NVIDIA från leverantörstabellen kommer att utelämnas eftersom leverantörs-id-värdena 10002 och 10003 inte finns i båda tabellerna. Raden order_id 500127 från ordertabellen kommer att utelämnas eftersom leverantörs-id 10004 inte finns i leverantörstabellen.
Gammal syntaxSom en sista anmärkning är det värt att notera att MySQL INNER JOIN-exemplet ovan kan skrivas om med den gamla implicita syntaxen enligt följande (men att använda INNER JOIN-syntax rekommenderas):
En annan typ av join kallas MySQL LEFT OUTER JOIN. Denna typ av join returnerar alla rader från tabeller med en vänsterjoin specificerad i ON-satsen, och endast de rader från en annan tabell där fälten som sammanfogas är lika.
SyntaxSyntax för LEFT OUTER JOIN i MySQL:
VÄLJ kolumner
FRÅN tabell 1
VÄNSTER JOIN tabell2
ON table1.column = table2.column;
I vissa databaser ersätts LEFT OUTER JOIN med LEFT JOIN.
I den här figuren returnerar MySQL LEFT OUTER JOIN en skuggad region:
MySQL LEFT OUTER JOIN kommer att returnera alla poster från tabell1 och endast de poster från tabell2 som korsar tabell1.
ExempelMySQL
VÄLJ suppliers.supplier_id, suppliers.supplier_name, orders.order_date FRÅN leverantörer LEFT JOIN orders ON suppliers.supplier_id = orders.supplier_id;
VÄLJ suppliers.supplier_id,suppliers.supplier_name,orders.order_date FRÅN leverantörer LEFT JOIN beställningar ON suppliers.supplier_id=orders.supplier_id; |
Detta LEFT OUTER JOIN-exempel returnerar alla rader från leverantörstabellen, och endast de rader från ordertabellen där fälten som sammanfogas är lika.
Om värdet för leverantör_id i tabellen leverantörer inte finns i ordertabellen kommer alla fält i ordertabellen att visas som NULL i resultatuppsättningen.
Låt oss titta på några data för att förstå hur LEFT OUTER JOIN fungerar:
Vi har en leverantörstabell med två fält (leverantörs-id och leverantörsnamn) som innehåller följande data:
Om vi kör MySQL SELECT-satsen (som innehåller en LEFT OUTER JOIN) nedan:
MySQL
VÄLJ suppliers.supplier_id, suppliers.supplier_name, orders.order_date FRÅN leverantörer VÄNSTER YTTRE JOIN beställningar PÅ suppliers.supplier_id = orders.supplier_id;
Rader för Microsoft och NVIDIA kommer att inkluderas eftersom LEFT OUTER JOIN användes. Du kommer dock att märka att order_date-fältet för dessa poster är NULL.
En annan typ av join kallas MySQL RIGHT OUTER JOIN. Den här typen av join returnerar alla rader från tabeller med en höger-join specificerad i ON-satsen, och endast de rader från en annan tabell där fälten som sammanfogas är lika.
SyntaxSyntax för RIGHT OUTER JOIN i MySQL:
VÄLJ kolumner
FRÅN tabell 1
HÖGER JOIN tabell2
ON table1.column = table2.column;
I vissa databaser ersätts RIGHT OUTER JOIN med RIGHT JOIN.
I den här figuren returnerar MySQL RIGHT OUTER JOIN den skuggade regionen:
MySQL RIGHT OUTER JOIN kommer att returnera alla poster från tabell2 och endast de poster från tabell1 som korsar tabell2.
ExempelNedan är ett exempel på MySQL RIGHT OUTER JOIN:
MySQL
VÄLJ orders.order_id, orders.order_date, suppliers.supplier_name FRÅN leverantörer RIGHT JOIN orders ON suppliers.supplier_id = orders.supplier_id;
