Tidigare tittade vi på att använda SELECT -satsen för att hämta data från en enda databastabell. Om Transact-SQL: s möjligheter var begränsade till att endast stödja sådana enkla SELECT-satser, skulle det vara omöjligt att gå med två eller flera tabeller i en fråga för att hämta data från dem. Följaktligen måste all databasdata lagras i en tabell. Även om detta tillvägagångssätt är fullt möjligt, har det en betydande nackdel - data som lagras på detta sätt kännetecknas av hög redundans.
Transact-SQL löser detta problem genom att tillhandahålla GÅ MED operatör, som låter dig hämta data från mer än en tabell. Denna operatör är förmodligen den viktigaste operatören för relationsdatabassystem eftersom den låter dig distribuera data över flera tabeller, vilket ger en viktig egenskap hos databassystem att det inte finns någon dataredundans.
UNION -operatören, som vi täckte tidigare, låter dig också söka efter flera tabeller. Detta uttalande låter dig dock gå med i flera SELECT -satser, medan JOIN -satsen går med i flera tabeller med bara en SELECT -sats. Dessutom ansluter sig UNION -operatören till tabellrader, medan JOIN -operatören går med i kolumner.
Joint -operatören kan också användas med basbord och vyer. JOIN -uttalandet har flera olika former. Den här artikeln diskuterar följande grundläggande former för den här operatören:
naturlig anslutning;
Kartesisk produkt eller tvärbindning;
yttre skarv;
theta join, self-join och semi-join.
Innan vi tittar på de olika formerna av fogar, i det här avsnittet tittar vi på olika varianter av JOIN -operatören.
Två syntaktiska former för implementering av fogar
Du kan använda två olika syntaktiska former för kopplingsoperatören för att gå med i tabeller:
Explicit join syntax (ANSI SQL: 1992 join syntax)
implicit kopplingssyntax (gammaldags sammanfogningssyntax).
ANSI SQL: 1992 -kopplingssyntaxen introducerades av SQL92 -standarden och definierar kopplingsoperationer uttryckligen, d.v.s. med ett lämpligt namn för varje typ av kopplingsoperation. När du uttryckligen deklarerar en anslutning används följande sökord:
-
VÄNSTER GÅ MED;
HÖGER GÅ MED;
HELA GÅ MED.
Sökordet CROSS JOIN definierar den kartesiska produkten från två tabeller. Nyckelordet INNER JOIN definierar en naturlig sammankoppling mellan två tabeller, och VÄNSTER YTTERFÖRENING och HÖGER UTANFÖRENING definierar samma namngivna föreningsoperationer. Slutligen definierar nyckelordet FULL YUTER JOIN sammanslutningen av de vänstra och högra yttre fogarna. Alla dessa kopplingsoperationer diskuteras i följande avsnitt.
Den implicita kopplingsoperatörssyntaxen är "gammal stil" -syntax, där varje koppling implicit definieras av en WHERE -sats med vad som kallas för anslutningskolumner.
Det rekommenderas att använda explicit syntax för anslutningsoperationer, eftersom detta förbättrar tillförlitligheten hos frågor. Av denna anledning använder alla exemplen nedan för kopplingsoperationer uttryckliga syntaxformulär. Men de första exemplen visar också syntaxen "gammal stil".
Naturlig koppling
Begreppen naturlig sammanfogning och equi-join används ofta synonymt, men det är liten skillnad mellan dem. En likvärdighetsföreningsoperation har alltid ett eller flera par kolumner med identiska värden i varje rad. En operation som tar bort sådana kolumner från resultaten av en ekvivalensföreningsoperation kallas en naturlig sammanfogning. Det bästa sättet att förklara naturlig anslutning är genom ett exempel:
ANVÄND SampleDb; VÄLJ Medarbetare. *, Avdelning. * FRÅN Anställd INNER GÅ MED AVDELNING PÅ Employee.DepartamentNumber = Department.Number;
Frågan returnerar all information om alla anställda: för- och efternamn, personalnummer samt avdelningens namn, nummer och plats, medan duplicerade kolumner från olika tabeller visas för avdelningsnumret.
I det här exemplet anger SELECT -satsen för urvalet alla tabellkolumner för medarbetare och avdelning. FROM -satsen i SELECT -satsen definierar tabellerna som ska sammanfogas och specificerar också uttryckligen typen av kopplingsoperationen - INRE KOPPLING... ON -satsen är en del av FROM -satsen och anger vilka kolumner som ska sammanfogas i båda tabellerna. Uttrycket "Employee.DepartamentNumber = Department.Number" definierar kopplingsvillkoret, och båda villkolumnerna heter gå med i kolumner.
En motsvarande implicit ("gammaldags") syntaxfråga skulle se ut så här:
Denna syntaxform har två betydande skillnader med sin uttryckliga form: listan över tabeller som ska sammanfogas specificeras i FROM -satsen och motsvarande kopplingsvillkor anges i WHERE -satsen med hjälp av de sammanfogade kolumnerna.
De tidigare exemplen illustrerar hur kopplingsoperationen fungerar. Men det bör komma ihåg att detta bara är en idé om anslutningsprocessen, sedan i själva verket väljer databasmotorn implementeringen av kopplingsoperationen bland flera möjliga strategier. Tänk dig att varje rad i tabellen Anställda är ansluten till varje rad i tabellen Avdelning. Resultatet är en tabell med sju kolumner (4 kolumner från medarbetartabellen och 3 från avdelningstabellen) och 21 rader.
Alla rader som inte uppfyller kopplingsvillkoret "Employee.Number = Department.Number" tas bort från denna tabell. De återstående raderna representerar resultatet av det första exemplet ovan. Kolumnerna som ska sammanfogas måste ha identisk semantik, d.v.s. båda kolumnerna måste ha samma booleska värde. Kolumnerna som ska sammanfogas behöver inte ha samma namn (eller ens samma datatyp), även om detta ofta är fallet.
Databassystemet kan inte bestämma kolumnens booleska värde. Till exempel kan hon inte avgöra att det inte finns något gemensamt mellan kolumnerna projektnummer och anställdas personaltal, även om de båda är av en heltalstyp. Därför kan databassystemet endast kontrollera datatypen och strängarnas längd. Databasmotorn kräver att de kolumner som ska sammanfogas är av kompatibla datatyper som INT och SMALLINT.
SampleDb -databasen innehåller tre par kolumner, där varje kolumn i paret har samma booleska värde (liksom samma namn). Medarbetarna och avdelningstabellerna kan anslutas till kolumnerna Employee.DepartmentNumber och Department.Number. Kopplingskolumnerna i tabellerna Employee och Works_on är kolumnerna Employee.Id och Works_on.EmpId. Slutligen kan tabellerna Project och Works_on sammanfogas med kolumnerna Project.Number och Works_on.ProjectNumber.
Kolumnnamn i SELECT -satsen kan kvalificeras. I detta sammanhang betyder kvalifikation att för att undvika oklarhet om vilken tabell en kolumn tillhör, innehåller kolumnnamnet namnet på dess tabell (eller tabellalias), åtskilt av en punkt:
tabellnamn.kolumnnamn (tabellnamn.kolumnnamn)
I de flesta SELECT -uttalanden kräver kolumner inte kvalifikation, även om det i allmänhet rekommenderas att använda kolumnkvalificering för att förbättra din förståelse av din kod. Men om kolumnnamnen i SELECT -satsen är tvetydiga (t.ex. kolumnerna Number i tabellerna Project och Department) krävs de kvalificerade kolumnnamnen.
I en SELECT -sats med en joinoperator kan WHERE -satsen, förutom kopplingsvillkoret, innehålla andra villkor, som visas i exemplet nedan:
ANVÄND SampleDb; - Explicit syntax SELECT EmpId, Project.Number, Job, EnterDate, ProjectName, Budget FROM Works_on JOIN Project ON Project.Number = Works_on.ProjectNumber WHERE ProjectName = "Gemini"; - Gammal stil SELECT EmpId, Project.Number, Job, EnterDate, ProjectName, Budget FRÅN Works_on, Project WHERE Project.Number = Works_on.ProjectNumber AND ProjectName = "Gemini";
Att använda det kvalificerade kolumnnamnet Project.Number i exemplet ovan är valfritt, eftersom det inte finns någon oklarhet om deras namn i det här fallet. I det följande kommer alla exempel endast att använda den uttryckliga sammanfogningssyntaxen.
Exemplet nedan visar en annan användning av inner join:
Ansluter sig till mer än två bord
I teorin är antalet tabeller som kan sammanfogas i ett SELECT -uttalande obegränsat. (Men ett sammanfogningsvillkor sammanfogar bara två tabeller!) Men för databasmotorn är antalet tabeller som ska anslutas i en SELECT -sats begränsat till 64 tabeller.
Exemplet nedan visar en sammanslutning av tre SampleDb -databastabeller:
ANVÄND SampleDb; - Återkommer den enda medarbetaren "Vasily Frolov" VÄLJ Förnamn, Efternamn FRÅN Works_on GÅ MED ANSTÄLLARE PÅ Works_on.EmpId = Employee.Id GÅ MED AVDELNING PÅ Employee.DepartamentNumber = Department.Number AND Location = "St. Petersburg" OCH Jobb = "Analytiker" ;
Detta exempel väljer för- och efternamn på alla analytiker (jobb = "analytiker") vars avdelning ligger i S: t Petersburg (plats = "S: t Petersburg"). Resultatet av frågan som visas i exemplet ovan kan endast erhållas om minst tre tabeller är sammanfogade: Works_on, Employee och Department. Dessa tabeller kan sammanfogas med två par sammanfogningskolumner:
(Works_on.EmpId, Employee.Id) (Employee.DepartmentNumber, Department.Number)
Observera att två sammanfogningsvillkor används för att naturligtvis sammanfoga de tre tabellerna, var och en sammanfoga två tabeller. Och när man sammanfogar fyra bord krävs tre sådana sammanfogningsvillkor. I allmänhet måste du tillämpa n - 1 sammanfogningsvillkor för att undvika att få en kartesisk produkt när du går med i tabeller. Naturligtvis är det tillåtet att använda mer än n - 1 sammanfogningsvillkor, liksom andra villkor, för att ytterligare minska antalet element i den resulterande datamängden.
kartesisk produkt
I föregående avsnitt tittade vi på ett möjligt sätt att skapa en naturlig anslutning. I det första steget i denna process är varje rad i tabellen Anställda ansluten till varje rad i avdelningstabellen. Denna operation kallas kartesisk produkt... Frågan om att skapa en koppling mellan anställda och avdelningstabeller med kartesisk produkt visas i exemplet nedan:
ANVÄND SampleDb; VÄLJ Medarbetare. *, Avdelning. * FRÅN ANSTÄLLNINGSKROSS JOIN Avdelning;
Den kartesiska produkten förbinder varje rad i den första tabellen med varje rad i den andra. I allmänhet resulterar den kartesiska produkten i den första tabellen med n rader och den andra tabellen med m rader i en tabell med n * m rader. Således har resultatuppsättningen för frågan i exemplet ovan 7 x 3 = 21 rader (dessa rader innehåller dubblettvärden).
I praktiken används den kartesiska produkten sällan. Ibland får användarna den kartesiska produkten av två tabeller när de glömmer att inkludera ett kopplingsvillkor i WHERE-satsen när de använder den "gamla stilen" implicita kopplingssyntaxen. I detta fall motsvarar det erhållna resultatet inte det förväntade, eftersom innehåller extra rader. Att ha ett oväntat stort antal rader som resultat indikerar att i stället för den nödvändiga naturliga sammanfogningen av de två tabellerna erhölls en kartesisk produkt.
Extern anslutning
I de föregående exemplen med naturlig sammanfogning innehöll resultatuppsättningen endast de raderna från en tabell för vilka det fanns motsvarande rader i den andra tabellen. Men ibland, förutom matchande rader, är det nödvändigt att hämta rader från en eller båda tabellerna utan matchningar. Denna operation kallas yttre skarv.
Exemplet nedan visar ett urval av all information för anställda som bor och arbetar i samma stad. Detta använder tabellen EmployeeEnh som vi skapade i SELECT Statement: Advanced när vi diskuterade UNION -operatören.
ANVÄND SampleDb; VÄLJ DISTINCT EmployeeEnh. *, Department.Location FRÅN EmployeeEnh GÅ MED AVDELNING ON City = Location;
Resultatet av denna fråga:
I det här exemplet görs de nödvändiga strängarna med en naturlig sammanfogning. Om detta resultat skulle inkludera anställda som bor på andra platser, måste den vänstra yttre skarven tillämpas. Denna yttre koppling kallas vänster eftersom den returnerar alla rader från tabellen på vänster sida av jämförelseoperatören, oavsett om det finns matchande rader i tabellen på höger sida. Med andra ord kommer denna yttre koppling att returnera en rad från den vänstra tabellen, även om det inte finns någon matchning i den högra tabellen, med ett NULL -värde för motsvarande kolumn för alla rader med ett felaktigt kolumnvärde i den andra, höger, tabellen . Databasmotorn använder operatören för att utföra en vänster yttre kopplingsoperation VÄNSTER YTTRE GÅ MED.
Den högra yttre kopplingsoperationen är densamma som den vänstra yttre kopplingen, men alla tabellrader på höger sida av uttrycket returneras. Databasmotorn använder operatören för att utföra en högra yttre kopplingsoperation HÖGER YTTRE GÅ MED.
ANVÄND SampleDb; VÄLJ Medarbetare *
Detta exempel väljer anställda (inklusive fullständig information) för städer där anställda antingen bara bor (kolumnen Stad i tabellen EmployeeEnh) eller bor och arbetar. Resultatet av denna fråga:
Som du kan se från sökresultatet, när det inte finns någon matchande rad i den högra tabellen (i det här fallet avdelning) för en rad från den vänstra tabellen (i det här fallet EmployeeEnh), returnerar driften av den vänstra yttre kopplingen fortfarande den raden , fyller alla celler i motsvarande kolumn med NULL för det felaktiga kolumnvärdet i den högra tabellen. Tillämpningen av den högra yttre skarven visas i exemplet nedan:
ANVÄND SampleDb; VÄLJ EmployeeEnh.City, Department. * FRÅN EmployeeEnh RIGHT YUTER JOIN Department Department ON City = Location;
Detta exempel väljer avdelningar (inklusive fullständig information om dem) för städer där anställda antingen bara arbetar eller bor och arbetar. Resultatet av denna fråga:
Förutom de vänstra och högra yttre fogarna finns det också en fullständig yttre skarv, som är föreningen mellan vänster och höger yttre fogar. Med andra ord består resultatuppsättningen av en sådan koppling av alla rader i båda tabellerna. Om en rad i en av tabellerna inte har en motsvarande rad i den andra tabellen är alla celler i raden i den andra tabellen inställda på NULL. Använd operatören för att utföra en fullständig yttre kopplingsoperation HELA YTTRE GÅ MED.
Alla yttre skarvoperationer kan emuleras med hjälp av UNION -operatören tillsammans med funktionen INTE existerar. Således motsvarar frågan som visas i exemplet nedan den vänstra yttre kopplingsfrågan som visas tidigare. I denna begäran utförs ett urval av anställda (inklusive fullständig information) för sådana städer där anställda antingen bara bor eller bor och arbetar:
Den första SELECT -satsen för kopplingen definierar en naturlig sammanfogning av tabellerna EmployeeEnh och Department av kolumnerna Stad och plats. Denna instruktion returnerar alla städer för alla anställda där anställda både bor och arbetar. Dessutom returnerar den andra SELECT -satsen i kopplingen alla rader i EmployeeEnh -tabellen som inte uppfyller villkoret i den naturliga kopplingen.
Andra former av anslutningsverksamhet
I de föregående avsnitten behandlade vi de viktigaste formerna för anslutning. Men det finns andra former av denna operation, som vi kommer att titta på i följande underavsnitt.
Theta -anslutning
Jämförelsevillkoret för sammanfogningskolumnen behöver inte vara jämlikhet, men det kan vara vilken annan jämförelse som helst. En koppling som använder ett gemensamt kolumnjämförelsevillkor för kopplingen kallas theta -anslutning... Exemplet nedan visar en theta join -operation som använder mindre än sats. Denna fråga returnerar alla kombinationer av information om anställda och avdelningar för de fall då en anställds bostadsort alfabetiskt kommer före platsen för någon avdelning där den anställde arbetar:
ANVÄND SampleDb; VÄLJ Förnamn, Efternamn, Stad, Plats FRÅN MedarbetareEnh GÅ MED AVDELNING PÅ Stad
Resultatet av denna fråga:
I det här exemplet jämförs motsvarande värden i kolumnerna Stad och Plats. Varje rad i resultatet jämför stadskolumnvärdet alfabetiskt med motsvarande kolumnvärde för plats.
Gå med själv eller gå med i ett bord för sig själv
Förutom att sammanfoga två eller flera olika bord kan den naturliga kopplingsoperationen tillämpas på ett enda bord. I den här åtgärden sammanfogas tabellen med sig själv och en kolumn i tabellen jämförs med sig själv. Att jämföra en kolumn med sig själv innebär att tabellnamnet används två gånger i FROM -satsen i SELECT -satsen. Därför är det nödvändigt att kunna hänvisa till namnet på samma tabell två gånger. Detta kan göras med minst ett alias. Detsamma gäller för kolumnnamnen i kopplingsklausulen i SELECT -satsen. Kvalificerade namn måste användas för att skilja mellan kolumner med samma namn.
Att gå med i en tabell för sig själv visas i exemplet nedan:
I det här exemplet markeras alla avdelningar (med fullständig information) på samma plats som minst en annan avdelning. Resultatet av denna fråga:
Här innehåller FROM -klausulen två alias för avdelningstabellen: t1 och t2. Den första klausulen i WHERE -klausulen definierar sammankopplingskolumnerna, och den andra tar bort onödiga dubbletter, vilket säkerställer att varje avdelning jämförs med andra avdelningar.
Semi-join
En semi-join liknar en naturlig join, men returnerar bara en uppsättning av alla rader från en tabell för vilken det finns en eller flera matchningar i den andra tabellen. Användningen av en semi-join visas i exemplet nedan:
Resultatet av att köra frågan:
Som du kan se innehåller SELECT i semi-join bara kolumner från medarbetartabellen. Detta är ett kännetecken för halvfogningsoperationen. Denna operation används vanligtvis vid bearbetning av distribuerade frågor för att minimera mängden data som överförs. Databasmotorn använder en semi-join-operation för att implementera en funktionalitet som kallas star join.
Fortsätter att lära sig grunderna SQL och det är dags att prata om enkla JOIN. Och idag kommer vi att titta på hur data kombineras med operatörers medel. VÄNSTERA GÅ MED, HÖGRE GÅ MED, KORSJOINERA OCH INRE INGÅ Med andra ord kommer vi att lära oss att skriva frågor som kombinerar data, och som vanligt kommer vi att studera allt detta genom exempel.
JOIN joins är mycket viktiga i SQL, för utan möjligheten att skriva frågor med kopplingsdata för olika objekt kan SQL -programmeraren helt enkelt inte vara utan, och bara administratören som då och då laddar ur vissa data från databasen, så detta gäller för grunderna i SQL och varje person som hanterar SQL bör ha en uppfattning om vad det är.
Notera! Alla exempel skrivs i Management Studio SQL Server 2008.
Vi har studerat grunderna i SQL under en lång tid, och om du kommer ihåg började vi med select -satsen, och i allmänhet fanns det redan mycket material på denna webbplats om SQL, till exempel:
Och många andra saker, vi har till och med redan betraktat facket och facket alla, men på ett eller annat sätt pratade vi inte mer med dig om att gå med, så idag kommer vi att fylla denna lucka i vår kunskap.
Och vi börjar som vanligt med lite teori.
GÅ MED- detta är sammanslutningen av två eller flera databasobjekt med hjälp av en eller flera tangenter eller, i fallet med en kryssförening, utan någon nyckel alls. Med objekt här menar vi olika tabeller, vyer, tabellfunktioner eller bara sql -underfrågor, d.v.s. allt som returnerar tabelldata.
SQL VÄNSTER och HÖGER GÅ MED
VÄNSTER GÅ MED- detta är föreningen av data med den vänstra tangenten, d.v.s. Låt oss säga att vi sammanfogar två tabeller med vänster sammanfogning, vilket innebär att all data från den andra tabellen dras upp till den första, och i avsaknad av en nyckel visas NULL -värden, med andra ord, alla data från den vänstra tabellen och all data med nyckel från den högra tabellen visas.
HÖGER GÅ MED- detta är samma sammankoppling som vänsteranslutning, bara alla data från den högra tabellen och endast de data från den vänstra tabellen där det finns en kopplingsnyckel visas.
Låt oss nu gå igenom exemplen, och först, låt oss skapa två tabeller:
SKAPA TABELL ((18, 0) NULL, (50) NULL) ON GO SKAPA BORD ((18, 0) NULL, (50) NULL) ON GO
Det här är så enkla tabeller, och till exempel fyllde jag i dem med följande data:
Låt oss nu skriva en fråga som sammanfogar dessa tabeller med siffertangenten, först med VÄNSTER:
VÄLJ t1.nummer som t1_nummer, t1.text som t1_text, t2.nummer som t2_nummer, t2.text som t2_text FRÅN test_tabell t1 VÄNSTER JOIN test_tabell_2 t2 PÅ t1.nummer = t2.nummer
Som du kan se så visas alla data från tabellen t1, men inte alla data från tabellen t2, eftersom raden med nummer = 4 inte finns där, därför visas NULL -värden.
Och vad skulle hända om vi kombinerade med rätt anslutning, men detta skulle vara:
VÄLJ t1.nummer som t1_nummer, t1.text som t1_text, t2.nummer som t2_nummer, t2.text som t2_text FRÅN test_tabell t1 RIGHT JOIN test_table_2 t2 ON t1.nummer = t2.nummer
Med andra ord visades alla rader från t2 -tabellen och motsvarande poster från t1 -tabellen, eftersom alla nycklar som finns i t2 -tabellen också finns i t1 -tabellen, och därför har vi inte NULL -värden.
SQL INNER JOIN
Inre koppling- detta är en förening när alla poster från en tabell och alla motsvarande poster från en annan tabell visas, och de poster som inte finns i en eller i en annan tabell inte visas, d.v.s. bara de poster som matchar nyckeln. Förresten, jag säger direkt att inre skarvning är detsamma som att bara gå utan inre. Exempel:
VÄLJ t1.nummer som t1_number, t1.text som t1_text, t2.nummer som t2_number, t2.text som t2_text FRÅN test_tabell t1 INNER JOIN test_table_2 t2 på t1.nummer = t2.nummer
Låt oss nu försöka kombinera våra tabeller med två nycklar, för detta kommer vi att komma ihåg lite hur man lägger till en kolumn i en tabell och hur man uppdaterar data genom uppdatering, eftersom våra tabeller bara har två kolumner, och det är på något sätt inte bra att kombinera med ett textfält. Lägg till kolumner för att göra detta:
ALTER TABLE test_table ADD number2 INT ALTER TABLE test_table_2 ADD number2 INT
Låt oss uppdatera våra data, lägg bara värdet 1 i kolumnen nummer 2:
UPDATE test_table SET number2 = 1 UPDATE test_table_2 SET number2 = 1
Och låt oss skriva en fråga med sammanfogning på två nycklar:
VÄLJ t1.nummer som t1_nummer, t1.text som t1_text, t2.nummer som t2_nummer, t2.text som t2_text FRÅN test_tabell t1 INNER JOIN test_table_2 t2 ON t1.number = t2.nummer AND t1.nummer2 = t2.nummer2
Och resultatet blir detsamma som i föregående exempel:
Men om vi till exempel i den andra tabellen i en rad ändrar fältnummer2 till värdet, säg 2, blir resultatet helt annorlunda.
UPDATE test_table_2 set number2 = 2 WHERE number = 1
Begäran är densamma, men här är resultatet:
Som du kan se, för den andra tangenten, visades inte en rad.
GÅ MED i SQL CROSS
Korsa medÄr en SQL -koppling genom vilken varje rad i en tabell sammanfogas med varje rad i en annan tabell. Personligen behöver jag sällan denna fackförening, men det krävs fortfarande ibland, så du bör kunna använda den också. Till exempel, i vårt fall kommer det att visa sig, naturligtvis är det inte klart vad, men ändå, låt oss försöka, ju mer syntaxen är något annorlunda:
VÄLJ t1.nummer som t1_number, t1.text som t1_text, t2.nummer som t2_nummer, t2.text som t2_text FRÅN test_tabell t1 CROSS JOIN test_table_2 t2
Här motsvarar varje rad i test_tabellen varje rad från tabellen test_tabell_2, dvs. i tabellen test_table har vi 4 rader, och i tabellen test_table_2 3 rader 4 multiplicerar 3 och det blir 12, som vi har visat 12 rader.
Och slutligen, låt oss visa hur du kan kombinera flera bord, för detta kommer jag, till exempel, att kombinera vårt första bord med det andra flera gånger, det är naturligtvis ingen idé att gå med i det här fallet, men du kommer att se hur du kan göra detta ändå låt oss komma igång:
VÄLJ t1.nummer som t1_nummer, t1.text som t1_text, t2.nummer som t2_nummer, t2.text som t2_text, t3.nummer som t3_nummer, t3.text som t3_text, t4.nummer som t4_nummer, t4.text som t4_text FRÅN t1 LEFT JOIN test_table_2 t2 on t1.number = t2.number RIGHT JOIN test_table_2 t3 on t1.number = t3.number INNER JOIN test_table_2 t4 on t1.number = t4.number
Som ni kan se sammanfogar jag här både vänster och höger och inre bara för tydlighetens skull.
Med associationer tror jag nog, särskilt eftersom det inte finns något komplicerat i dem. Men detta är inte slutet på studien av SQL i nästa artiklar, vi kommer att fortsätta, men för närvarande, öva och skriva dina frågor. Lycka till!
Operatör ANSLUTA SIG används för att utföra operationen för att sammanfoga data från två uppsättningar till en resultatuppsättning. Flera olika typer av kopplingsoperationer kan användas när du utför en SELECT. Vilka rader som ska ingå i resultatuppsättningen beror på typen av kopplingsoperation och på ett uttryckligen definierat sammanfogningsvillkor. Kopplingsvillkoret, dvs. villkoret för att matcha tabellrader med varandra är ett logiskt uttryck.
Om du behöver ansluta flera tabeller måste kopplingsoperationen tillämpas flera gånger i följd.
JOIN -sats syntax
Syntax för att ansluta till ytterligare tabellfråga med hjälp av operatör ANSLUTA SIG kan representeras enligt följande:
VÄLJ fält1, fält1, [, ... n] FRÅN Tabell1 t1 (INN |
I de flesta DBMS, när du använder operatören ANSLUTA SIG i kombination med sökorden VÄNSTER, HÖGER, FULL, kan YTTRE operanden utelämnas. INNER -operanden kan också utelämnas i de flesta DBMS.
Om namnen på kolumnerna som tabellerna är sammanfogade är desamma kan du istället för PÅ använda ANVÄNDER SIG AV... För operatör Korsa med villkoret är inte specificerat.
Testtabeller för JOIN -validering
För ytterligare exempel, låt oss skapa två enkla testtabeller. Referenstabeller för användare och deras fordon. Den första tabellen lagrar användar -ID och hans namn (nick). Den andra tabellen innehåller en lista över bilar (identifierare, namn) och identifierare för deras ägare / användare. Det bör noteras att användaren kan ha flera bilar eller inte alls.
Användare tabell
SKAPA TABELL -användare (id int inte null, namn varchar (32) inte null, primär nyckel (id)); - Låt oss infoga flera poster i tabellen infoga i användare (id, namn) värden (1, "alex"); sätt in värdena för användare (id, namn) (2, "piter"); sätt in värdena för användare (id, namn) (3, "serg"); infoga i användarnas (id, namn) värden (4, "olga"); infoga i användarnas (id, namn) värden (5, "ostap");
Bilbord
SKAPA tabellbilar (id int inte null, - auto identifierarnamn varchar (32) inte null, - auto name oid int inte null, - ägaridentifierare primärnyckel (id)); - Låt oss infoga flera poster i tabellen infoga i autos (id, oid, namn) värden (1, 1, "toyota camry"); infoga värden i bilar (id, oid, namn) (2, 1, "toyota prado"); infoga värden i bilar (id, oid, namn) (3, 2, "renault megane"); infoga värden i bilar (id, oid, namn) (4, 3, "nissan x-trail"); infoga värden i bilar (id, oid, namn) (5, 4, "suzuki swift"); infoga värden i bilar (id, oid, namn) (6, 4, "suzuki vitara");
Inre anslutning, INNER GÅ MED
INRE KOPPLINGär en operatör för den inre skarven av två bord. Det är symmetriskt, så tabellernas ordning är inte viktig för operatören.
Tillämplig INRE KOPPLING för att bara få de rader för vilka det finns en överensstämmelse mellan poster i mastern och den bifogade tabellen. Algoritm för att generera resultatet: varje rad i huvudbordet matchas med varje rad i den bifogade tabellen. Därefter kontrolleras anslutningsförhållandet. Om villkoret är sant läggs motsvarande "sammanfogade" sträng till resultatuppsättningen.
Fråga med val av kolumner i resultatuppsättningen välj u.namn som ägare, a.namn som auto från användare u inner join autos a ON a.oid = u.id - Frågeresultatägare auto alex toyota camry alex toyota prado piter renault megane serg nissan x -trail olga suzuki swift olga suzuki vitara - Fråga utan att välja kolumner i resultatuppsättningen välj * från användare u inner join autos a ON a.oid = u.id - Frågeresultat id -namn id1 namn1 oid 1 alex 1 toyota camry 1 1 alex 2 toyota prado 1 2 piter 3 renault megane 2 3 serg 4 nissan x-trail 3 4 olga 5 suzuki swift 4 4 olga 6 suzuki vitara 4
För att få data som inte matchar villkoret är det nödvändigt att använda en yttre koppling - YTTRE GÅ MED.
Yttre anslutning, YTTRE GÅ MED
När du ansluter två bord med operatören YTTRE GÅ MED resultatuppsättningen kommer nödvändigtvis att innehålla rader från antingen en av tabellerna eller från båda tabellerna. Nyckelordet YTTRE kan utelämnas. Posten LEFT JOIN är identisk med LEFT YUTER JOIN.
Det finns två typer av yttre fogar. den VÄNSTER YTTRE GÅ MED och HÖGER YTTRE GÅ MED... Dessa operatörer fungerar på samma sätt. Skillnaden är att när du använder LEFT JOIN är huvudtabellen tabellen efter FROM -satsen. Uppgifterna i den bifogade tabellen läggs till i raderna i denna tabell under de angivna villkoren. För operatören RIGHT YUTER JOIN är allt precis tvärtom.
Yttre anslutningsoperatör YTTRE GÅ MEDär inte symmetrisk, så ordningen i vilken förhållandet mellan tabeller upprättas är viktigt för operatören.
Ett exempel på hur man använder LEFT YTTER JOIN -operatören
Välj u.name som ägare, a.name som auto från användare u left join autos a ON a.oid = u.id - Frågeresultat för ägare auto alex toyota camry alex toyota prado piter renault megane serg nissan x -trail olga suzuki swift olga suzuki vitara ostap
Grafiskt kan resultatet av arbetet representeras enligt följande:
LEFT YTTER JOIN -uttalande med filtrering
Genom att lägga till villkoret "där a.name är null" till koden i föregående exempel. Endast en "ostap" -post kommer att finnas kvar i urvalet, eftersom bara den inte har en bil definierad.
Grafiskt kan resultatet av arbetet representeras enligt följande:
Cross join -operatör, CROSS JOIN
CROSS JOIN är cross -join -operatören (kartesisk produkt). Operatören är symmetrisk och tabellernas ordning är inte viktig för operatören.
Resultatuppsättningen bildas på följande sätt: varje rad i en tabell är ansluten till varje rad i den andra tabellen, och ger därigenom alla möjliga kombinationer av rader i de två tabellerna. För vårt exempel resulterar detta i 30 rader av resultatuppsättningen.
Välj * bland användare cross join autos - Frågeresultat id namn id1 namn1 oid 1 alex 1 toyota camry 1 2 piter 1 toyota camry 1 3 serg 1 toyota camry 1 4 olga 1 toyota camry 1 5 ostap 1 toyota camry 1 1 alex 2 toyota prado 1 2 piter 2 toyota prado 1 3 serg 2 toyota prado 1 4 olga 2 toyota prado 1 5 ostap 2 toyota prado 1. ... ...
Endast de första 12 raderna visas i uppsättningen.
Kopplingsoperationen, liksom andra binära operationer, är utformad för att hämta data från två tabeller och inkludera dessa data i en resultatuppsättning. Ett särdrag hos kopplingsoperationen är följande:
- schemat i resultattabellen inkluderar kolumnerna i båda källtabellerna (operandtabeller), det vill säga resultatschemat är en "sammanfogning" av operandscheman;
- varje rad i resultattabellen är en "sammanfogning" av en rad från en operandtabell med en rad i den andra operandtabellen.
Bestämning av exakt vilka källsträngar som kommer att ingå i resultatet och i vilka kombinationer beror på typen av kopplingsoperation och på den uttryckligen angivna anslutningsvillkor... Ett kopplingsvillkor, det vill säga ett villkor för att matcha rader med källtabeller med varandra, är ett logiskt uttryck (predikat).
Om det är nödvändigt att koppla ihop inte två, utan flera tabeller, används kopplingsoperationen flera gånger (sekventiellt).
Operatörsbeskrivning
VÄLJ Fält [, ... n] FRÅN Tabell1 (INRE | (VÄNSTER | HÖGER | HELT) YTTRE | KORS) GÅ MED Tabell2 PÅ< condition>
I de flesta DBMS, när du anger orden VÄNSTER, HÖGER, FULL, kan ordet YTTRE utelämnas. Ordet INNER kan också utelämnas i de flesta DBMS.
I allmänhet kontrollerar DBMS villkoret (predikat) när en koppling utförs skick... För CROSS JOIN anges ingen klausul.
Vissa SQL -implementeringar använder kommatorn ( , ):
VÄLJ Fält [, ... n] FRÅN Tabell1, Tabell2
GÅ MED operatortyper
För ytterligare förtydligande kommer följande tabeller att användas:
Människor som bor i städer (personbord)
INRE KOPPLING
Operatör intern anslutning INNER JOIN sammanfogar två bord. Tabellernas ordning är inte viktig för operatören eftersom operatören är symmetrisk.
Rubrik sammanfogning
Kropp resultatet formas logiskt enligt följande. Varje rad i en tabell jämförs med varje rad i den andra tabellen, varefter kopplingsvillkoret kontrolleras för den resulterande "anslutna" raden (sammanfogningspredikatet beräknas). Om villkoret är sant läggs motsvarande "sammanfogade" rad till i resultattabellen.
Den beskrivna algoritmen för åtgärder är strikt logisk, det vill säga den förklarar bara resultatet som ska uppnås när operationen utförs, men föreskriver inte att ett visst DBMS ska utföra anslutningen på exakt det angivna sättet. Det finns många sätt att implementera en kopplingsoperation, till exempel en kapslad loop -koppling. inre öglor går med), haschanslutning (eng. hash -anslutning), slå ihop (eng. slå samman). Det enda kravet är att en implementering logiskt ger samma resultat som vid tillämpning av den beskrivna algoritmen.
VÄLJ * FRÅN Person INNER GÅ MED i stad PÅ person. CityId = City. Id
Resultat:
| Person.Name | Person.CityId | City.Id | Stadens namn |
|---|---|---|---|
| Andrey | 1 | 1 | Moskva |
| Leonid | 2 | 2 | St. Petersburg |
| Sergey | 1 | 1 | Moskva |
YTTRE GÅ MED
En sammanfogning av två tabeller, vars resultat nödvändigtvis innehåller rader från antingen en eller båda tabellerna.
VÄNSTER YTTRE GÅ MED
Operatör vänster yttre skarv VÄNSTER YTTRE JOIN sammanfogar två tabeller. Ordningen på tabellerna för operatören är viktig eftersom operatören inte är symmetrisk.
Rubrik result-table är sammanslutningen (sammanfogning) av rubrikerna på tabellerna som sammanfogas.
Kropp sid.
- sid.
- Därefter läggs de poster i den vänstra tabellen som inte ingick i den inre kopplingen i steg 1. till resultatet. För sådana poster fylls fälten som motsvarar den högra tabellen med värden NULL.
VÄLJ * FRÅN VÄNSTER YTTRE GÅ MED i stad PÅ person. CityId = City. Id
Resultat:
| Person.Name | Person.CityId | City.Id | Stadens namn |
|---|---|---|---|
| Andrey | 1 | 1 | Moskva |
| Leonid | 2 | 2 | St. Petersburg |
| Sergey | 1 | 1 | Moskva |
| Gregory | 4 | NULL | NULL |
HÖGER YTTRE GÅ MED
Operatör höger yttre skarv RIGHT YUTER JOIN sammanfogar två bord. Ordningen på tabellerna för operatören är viktig eftersom operatören inte är symmetrisk.
Rubrik result-table är sammanslutningen (sammanfogning) av rubrikerna på tabellerna som sammanfogas.
Kropp resultatet bildas logiskt enligt följande. Låt sammanfogningen av vänster och höger tabell utföras av ett predikat (villkor) sid.
- Resultatet inkluderar den inre kopplingen (INNER JOIN) i vänster och höger tabell av predikatet sid.
- Därefter läggs de poster i den högra tabellen som inte ingick i den inre kopplingen i steg 1. till resultatet. För sådana poster fylls fälten som motsvarar den vänstra tabellen med värden NULL.
VÄLJ * FRÅN HÖGER YTTRE GÅ MED i stad PÅ person. CityId = City. Id
Resultat:
| Person.Name | Person.CityId | City.Id | Stadens namn |
|---|---|---|---|
| Andrey | 1 | 1 | Moskva |
| Sergey | 1 | 1 | Moskva |
| Leonid | 2 | 2 | St. Petersburg |
| NULL | NULL | 3 | Kazan |
HELA YTTRE GÅ MED
Operatör full yttre skarv FULL YUTER JOIN sammanfogar två bord. Tabellernas ordning är inte viktig för operatören eftersom operatören är symmetrisk.
Rubrik result-table är sammanslutningen (sammanfogning) av rubrikerna på tabellerna som sammanfogas.
Kropp resultatet bildas logiskt enligt följande. Låt sammanfogningen av de första och andra tabellerna utföras av ett predikat (villkor) sid... Orden "första" och "andra" här anger inte ordningen i posten (vilket inte är viktigt), utan används endast för att skilja tabeller.
- Resultatet inkluderar den inre kopplingen (INNER JOIN) i den första och andra tabellen av predikatet sid.
- Resultatet lägger till de poster i den första tabellen som inte ingick i den inre kopplingen i steg 1. För sådana poster fylls fälten som motsvarar den andra tabellen med värden NULL.
- Resultatet lägger till de poster i den andra tabellen som inte ingick i den inre kopplingen i steg 1. För sådana poster fylls fälten som motsvarar den första tabellen med värden NULL.
VÄLJ * FRÅN Person HELA YTTRE GÅ MED i stad PÅ person. CityId = City. Id
Resultat:
| Person.Name | Person.CityId | City.Id | Stadens namn |
|---|---|---|---|
| Andrey | 1 | 1 | Moskva |
| Sergey | 1 | 1 | Moskva |
| Leonid | 2 | 2 | St. Petersburg |
| NULL | NULL | 3 | Kazan |
| Gregory | 4 | NULL | NULL |
Korsa med
Operatör tvärförbindelse, eller kartesisk produkt CROSS JOIN sammanfogar två tabeller. Tabellernas ordning är inte viktig för operatören eftersom operatören är symmetrisk.
Rubrik result-table är sammanslutningen (sammanfogning) av rubrikerna på tabellerna som sammanfogas.
Kropp resultatet bildas logiskt enligt följande. Varje rad i en tabell är sammanfogad med varje rad i den andra tabellen, vilket resulterar i alla möjliga kombinationer av rader i de två tabellerna.
VÄLJ * FRÅN Person CROSS JOIN City
VÄLJ * FRÅN Person, stad
Resultat:
| Person.Name | Person.CityId | City.Id | Stadens namn |
|---|---|---|---|
| Andrey | 1 | 1 | Moskva |
| Andrey | 1 | 2 | St. Petersburg |
| Andrey | 1 | 3 | Kazan |
| Leonid | 2 | 1 | Moskva |
| Leonid | 2 | 2 | St. Petersburg |
| Leonid | 2 | 3 | Kazan |
| Sergey | 1 | 1 | Moskva |
| Sergey | 1 | 2 | St. Petersburg |
| Sergey | 1 | 3 | Kazan |
| Gregory | 4 | 1 | Moskva |
| Gregory | 4 | 2 | St. Petersburg |
| Gregory | 4 | 3 | Kazan |
Om du lägger till ett sammankopplingsvillkor i WHERE -klausulen, det vill säga begränsningar för kombinationer av tupler, motsvarar resultatet INNER JOIN -operationen med samma villkor:
Så CROSS GÅ MED + VAR ( predikat) och INNER JOIN ( predikat) är syntaktiskt alternativa former för att skriva samma logiska inre kopplingsoperation. CROSS JOIN + WHERE -syntaxen för anslutning utfas eftersom den utfasas av ANSI SQL -standarden.
Anteckningar
Länkar
- Beskrivning av Join -typer i Oracle (ryska)
- Jeff Atwood, en visuell förklaring av SQL Joins
| SQL | |
|---|---|
| Versioner | SQL-86 SQL-89 SQL-92 SQL: 1999 SQL: 2003 SQL: 2008 |
| Nyckelord | Skapa radera från att ha infogat Ansluta sig Slå ihop Null -order genom att förbereda Välj topp Stympa Union Update |
| Relaterad | Edgar Codd relationsdatabas |
Oracle stöder flera typer av kopplingar, som skiljer sig åt på vilket sätt rader från två eller flera tabeller eller vyer sammanfogas. Det här inlägget på min blogg kommer att beskriva de vanligaste Oracle -anslutningstyperna.
Equijoint
En equi-join förenar två eller flera tabeller baserat på jämlikhetsvillkoret mellan kolumnerna. Med andra ord har samma kolumn samma värde i alla tabeller som sammanfogas. Nedan är ett exempel på användningen av en ekvivalent:
SQL> VÄLJ e.last_name, d.dept FRÅN emp e, avd d VAR e.emp_id = d.emp_id;
Du kan också använda följande nya syntax för ovanstående anslutningsoperator:
SQL> SELECT e.last_name, d.dept FROM emp e JOIN dept d USING (emp_id);
Om du vill gå med i flera kolumner kan du ange deras namn som en kommaseparerad lista, till exempel: USING (dept_id, emp_name).
Naturlig koppling
En naturlig koppling är en equi-join där kolumnerna som måste matchas för att slutföra kopplingen inte är specifikt specificerade. Oracle avgör automatiskt vilka kolumner som ska anslutas baserat på matchande kolumner i två tabeller. Nedan är ett exempel på att använda naturlig förening:
SQL> VÄLJ e.last_name, d.dept FRÅN emp e NATURAL JOIN avd d;
I det här exemplet är villkoret för kopplingen närvaron av identiska värden i kolumnen last_name i tabellerna emp och dept.
Reflekterande anslutning
En självförening refererar till sammanfogning av ett bord till sig själv genom användning av alias. Följande exempel förenar medarbetartabellen med sig själv med ett alias, vilket tar bort alla dubblerade rader.
SQL> RADERA FRÅN anställda X VAR ROWID> 2 (välj MIN (rowid) FRÅN anställda Y 3 där X.key_values = Y.key_values);
Intern anslutning
En inre koppling, även kallad en enkel koppling, returnerar alla rader som matchar ett angivet sammanfogningsvillkor. Tidigare, i den inre sammanfogningssyntaxen, var du tvungen att använda en WHERE -sats för att ange hur tabeller ska sammanfogas, så här:
SQL> VÄLJ e.flast_name, d.dept FRÅN emp e, avd d VAR e.emp_id = d.emp_id;
Oracle låter dig nu ange anslutningskriterier i inre (eller enkel) sammanfogningssyntax genom att använda den nya konstruktionen ON eller USING, till exempel:
SQL> VÄLJ DISTINCT NVL (dname, "No Dept"), COUNT (empno) nbr_emps FRÅN emp GÅ MED DEPT PÅ emp.deptno = dept.deptno VAR emp.job IN ("MANAGER", "SALESMAN", "ANALYST") GROUP BY dname;
Extern anslutning
En yttre koppling används för att returnera alla rader som matchar ett angivet sammanfogningsvillkor, plus några eller alla rader från en tabell som inte har matchande rader som matchar ett angivet kopplingsvillkor. Det finns tre typer av yttre skarv: vänster yttre skarv, höger yttre skarv och full yttre skarv. I en fullständig yttre sammanfogning uttalas vanligtvis ordet YTTRE.
