begrijpen hoe join werken wanneer 3 of meer tabellen betrokken zijn. [SQL]

Ik vraag me af of iemand kan helpen bij het verbeteren van mijn begrip van joins in SQL. [Als het belangrijk is voor het probleem, denk ik specifiek aan MS SQL Server.]

Neem 3 tabellen A, B [A gerelateerd aan B door een A.AID], en C [B gerelateerd aan C door sommige B.BID]

Als ik een query compose b.g

SELECT *
FROM A JOIN B 
ON A.AId = B.AId

allemaal goed – ik ben lief met hoe dit werkt.

Wat gebeurt er wanneer tabel C (of een andere D, E, …. wordt toegevoegd)

In de situatie

SELECT *
FROM A JOIN B 
  ON A.AId = B.AId
JOIN C ON C.BId = B.BId

Waar komt er mee aan? – Is het dat B-tabel (en de waarden daarin)?
Of is het een andere tijdelijke resultaatset die het resultaat is van de A + B die de C-tabel is verbonden met?

[De implicatie is niet alle waarden die in de B-tabel staan, is noodzakelijkerwijs in het tijdelijke resultaat instellen A + B op basis van de conditie voor A, B]

Een specifiek (en vrij gecontacteerd) voorbeeld van waarom ik vraag, is omdat ik het gedrag probeer te begrijpen, ik zie in het volgende:

Tables 
Account (AccountId, AccountBalanceDate, OpeningBalanceId, ClosingBalanceId)
Balance (BalanceId)
BalanceToken (BalanceId, TokenAmount)
Where:
Account->Opening, and Closing Balances are NULLABLE 
(may have opening balance, closing balance, or none)
Balance->BalanceToken is 1:m - a balance could consist of many tokens

Conceptueel, sluitingsbalans van een datum, zou de openingssaldo van morgen staan ​​

Als ik probeerde een lijst met alle openings- en slotbalansen voor een account te vinden

Ik kan iets doen als

SELECT AccountId
, AccountBalanceDate
, Sum (openingBalanceAmounts.TokenAmount) AS OpeningBalance
, Sum (closingBalanceAmounts.TokenAmount) AS ClosingBalance
FROM Account A 
   LEFT JOIN BALANCE OpeningBal 
      ON A.OpeningBalanceId = OpeningBal.BalanceId
   LEFT JOIN BALANCE ClosingBal 
      ON A.ClosingBalanceId = ClosingBal.BalanceId
   LEFT JOIN BalanceToken openingBalanceAmounts 
      ON openingBalanceAmounts.BalanceId = OpeningBal.BalanceId
   LEFT JOIN BalanceToken closingBalanceAmounts 
      ON closingBalanceAmounts.BalanceId = ClosingBal.BalanceId
   GROUP BY AccountId, AccountBalanceDate  

Dingen werken zoals ik zou verwachten totdat de laatste join brengt in de closing-balans-tokens – waar ik eindig met duplicaten in het resultaat.

[Ik kan oplossen met een onderscheidende – maar ik probeer te begrijpen waarom wat er gebeurt gebeurt]

Er is mij verteld dat het probleem is omdat de relatie tussen evenwicht en balancetoken 1: m – en dat wanneer ik in de laatste join breng ik duplicaten krijg, omdat de 3e joers al meerdere keren in balans worden ingebracht meerdere keren in de ( Ik neem aan) tijdelijke resultaat instellen.

Ik weet dat de voorbeeldtabellen niet voldoen aan het goede DB-ontwerp

Excuses voor het essay, bedankt voor elke elightentment 🙂

Bewerken in reactie op vraag door Marc

Conceptueel voor een account zou er geen duplicaten moeten zijn in balancetoken voor een account (per accountingdatum) – ik denk dat het probleem tot stand komt omdat 1 Account / AccountingDates Sluitingssaldo is dat het saldo van de volgende dag is, dus wanneer u zich aansluit bij Saldo, Balancetoken meerdere keren om openen en sluiten van saldi Ik denk dat evenwichtigheden (balans) meerdere keren in de ‘resultaatmix’ worden gebracht. Als het helpt bij het verduidelijken van het tweede voorbeeld, denkt eraan aan als een dagelijkse afstemming – Vandaar dat de bijgelaten is – een openings (en / of) sluitingssaldo mogelijk niet berekend voor een bepaalde account / AccountingDate-combinatie.

Antwoord 1, Autoriteit 100%

Conceptueel Hier is wat er gebeurt wanneer u zich samenvoegt aan drie tafels.

1. De optimizer komt met een plan, dat een bijbehorende volgorde bevat. Het kan A, B, C of C, B, A of een van de combinaties

zijn

2. De query-uitvoeringsengine past alle predikaten (WHERE-clausule) toe op de eerste tabel die geen van de andere tabellen omvat. Het selecteert de kolommen vermeld in de JOINvoorwaarden of de SELECTlijst of de ORDER BYlijst. Noem dit resultaat A
3. Het voegt deze resultaatset toe aan de tweede tabel. Voor elke rij wordt deze samengevoegd met de tweede tabel, waarbij eventuele predikaten worden toegepast die van toepassing kunnen zijn op de tweede tabel. Dit resulteert in een andere tijdelijke resultatenset.
4. Dan doet hij mee aan de finaletafel en past de ORDER BY

. toe

Dit is conceptueel wat er gebeurt. In feite zijn er onderweg veel mogelijke optimalisaties. Het voordeel van het relationele model is dat de degelijke wiskundige basis verschillende transformaties van het plan mogelijk maakt zonder de correctheid te veranderen.

Het is bijvoorbeeld niet nodig om onderweg de volledige resultatensets te genereren. De ORDER BYkan in plaats daarvan worden gedaan door in de eerste plaats toegang te krijgen tot de gegevens met behulp van een index. Er zijn ook veel soorten joins die kunnen worden gedaan.

Antwoord 2, autoriteit 11%

We weten dat de gegevens van Bworden gefilterd door de (innerlijke) join naar A(de gegevens in Azijn ook gefilterd). Dus als we (innerlijk) samenkomen van Bnaar C, dus de set Cwordt ookgefilterd door de relatie naar A. En merk ook op dat eventuele duplicaten van de join worden opgenomen.

Echter; in welke volgorde dit gebeurt, is aan de optimizer; het zou kunnen besluiten om eerst de B/Cjoin te doen en vervolgens Aof een andere reeks te introduceren (waarschijnlijk gebaseerd op het geschatte aantal rijen van elke join en de juiste indexen).

ECHTER; in je latere voorbeeld gebruik je een LEFT OUTERjoin; dus Accountwordt helemaal niet gefilterd, en wordt mogelijk gedupliceerd als een van de andere tabellen meerdere overeenkomsten heeft.

Zijn er duplicaten (per account) in BalanceToken?

Antwoord 3, autoriteit 2%

Ik merk vaak dat het helpt om het daadwerkelijke uitvoeringsplan te bekijken. In Query Analyser/Management Studio kunt u dit inschakelen voor query’s vanuit het Query-menu of Ctrl+M gebruiken. Nadat de query is uitgevoerd, wordt het uitgevoerde plan weergegeven op een ander resultaattabblad. Hieruit ziet u dat C en B eerst worden samengevoegd en dat het resultaat vervolgens wordt samengevoegd met A. Het plan kan variëren, afhankelijk van de informatie die het DBMS heeft, omdat beide joins innerlijk zijn, waardoor het A-en-B-en-C is . Wat ik bedoel is dat het resultaat hetzelfde zal zijn, ongeacht welke als eerste wordt samengevoegd, maar de tijd die het kost kan sterk verschillen, en dit is waar de optimizer en hints in het spel komen.

Antwoord 4, autoriteit 2%

Joins kunnen lastig zijn, en veel van het gedrag wordt natuurlijk bepaald door hoe de gegevens in de daadwerkelijke tabellen zijn opgeslagen.

Zonder de tabellen te zien, is het moeilijk om een ​​duidelijk antwoord te geven in uw specifieke geval, maar ik denk dat het basisprobleem is dat u meerdere resultatensets optelt die tot één resultaat worden gecombineerd.

Misschien moet u in plaats van meerdere joins twee aparte tijdelijke tabellen maken in uw zoekopdracht, één met de accountID, datum en som van de openingsbalansen, een tweede met de accountID, datum en som van de eindsaldi, en deze twee samen te voegen op AccountID en datum.

Om erachter te komen wat er precies gebeurt met joins, ook in jouw specifieke geval, zou ik het volgende doen:

Verander het begingedeelte

SELECT accountID Accountbalancedate, sum(…) als beginsaldo,
sum(…) als eindsaldo VAN

om gewoon

“SELECTEER * VAN”

Bestudeer de resulterende tabel en u zult precies zien welke gegevens worden gedupliceerd. Verwijder de joins één voor één en kijk wat er gebeurt. Dit zou u een idee moeten geven van wat het is met uw specifieke gegevens die de dupes veroorzaken.

Als u de query opent in SQL Server Management Studio (er bestaat een gratis versie), kunt u de query bewerken in de ontwerper. De visuele weergave van hoe de tafels worden samengevoegd, kan je ook helpen te beseffen wat er aan de hand is.