Comprendre comment fonctionne le JOIN lorsque 3 tables ou plus sont concernées. [SQL]

Question

Je me demande si quelqu'un peut m'aider à améliorer ma compréhension des JOIN en SQL. (Si c'est important pour le problème, je pense spécifiquement à MS SQL Server).

Prenez 3 tables A, B [A lié à B par un certain A.AId], et C [B lié à C par un certain B.BId].

Si je compose une requête, par exemple

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

Tout va bien - je suis content de la façon dont cela fonctionne.

Que se passe-t-il lorsque la table C (ou une autre D, E, ....) est ajoutée ?

Dans la situation

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

À quoi C se joint-il ? - Est-ce la table B (et les valeurs qu'elle contient) ? Ou est-ce un autre ensemble de résultats temporaire qui est le résultat de la jointure A+B à laquelle la table C est jointe ?

(L'implication étant que toutes les valeurs qui sont dans la table B ne seront pas nécessairement dans l'ensemble de résultats temporaires A+B basé sur la condition de jointure pour A,B).

Un exemple spécifique (et assez artificiel) de la raison pour laquelle je pose la question est que j'essaie de comprendre le comportement que je vois dans ce qui suit :

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

Conceptuellement, le solde de clôture d'une date est le solde d'ouverture du lendemain.

Si j'essayais de trouver une liste de tous les soldes d'ouverture et de fermeture d'un compte

Je pourrais faire quelque chose comme

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  

Les choses fonctionnent comme prévu jusqu'à ce que le dernier JOIN apporte les jetons de solde de clôture - où je me retrouve avec des doublons dans le résultat.

[Je peux régler le problème avec un DISTINCT, mais j'essaie de comprendre pourquoi ce qui se passe se produit.]

On m'a dit que le problème est dû au fait que la relation entre Balance et BalanceToken est 1:M - et que lorsque j'introduis la dernière JOIN, j'obtiens des doublons parce que la 3ème JOIN a déjà introduit les BalanceIds plusieurs fois dans le jeu de résultats (je suppose) temporaire.

Je sais que les tables d'exemple ne sont pas conformes à la bonne conception des bases de données.

Toutes mes excuses pour la rédaction, merci pour tout éclaircissement :)

Modifier en réponse à la question de Marc

Conceptuellement, pour un compte, il ne devrait pas y avoir de doublons dans BalanceToken pour un compte (par AccountingDate) - je pense que le problème vient du fait qu'un compte / AccountingDates solde de clôture est ce compte solde d'ouverture pour le jour suivant - donc lorsque l'auto-jointure à Balance, BalanceToken plusieurs fois pour obtenir les soldes d'ouverture et de clôture, je pense que les soldes (BalanceId) sont introduits dans le "mélange de résultats" plusieurs fois. Si cela peut aider à clarifier le second exemple, considérez-le comme un rapprochement quotidien - d'où les jointures à gauche - un solde d'ouverture (et/ou) de clôture peut ne pas avoir été calculé pour une combinaison compte/date comptable donnée.

Answer 1

Conceptuellement voici ce qui se passe quand on joint trois tables ensemble.

1. L'optimiseur propose un plan, qui inclut un ordre de jointure. Il peut s'agir de A, B, C, ou de C, B, A ou de n'importe laquelle des combinaisons suivantes
2. Le moteur d'exécution des requêtes applique tout prédicat ( WHERE ) à la première table qui n'implique aucune des autres tables. Elle sélectionne les colonnes mentionnées dans la clause JOIN conditions ou le SELECT ou la liste ORDER BY liste. On appelle ce résultat A
3. Il joint cet ensemble de résultats à la deuxième table. Pour chaque ligne, il joint la deuxième table, en appliquant tous les prédicats qui peuvent s'appliquer à la deuxième table. Il en résulte un autre ensemble de résultats temporaire.
4. Ensuite, il joint la table finale et applique la méthode ORDER BY

C'est conceptuellement ce qui se passe. En fait, il existe de nombreuses optimisations possibles en cours de route. L'avantage du modèle relationnel est que la base mathématique solide rend possible diverses transformations du plan sans en modifier l'exactitude.

Par exemple, il n'est pas vraiment nécessaire de générer les ensembles de résultats complets en cours de route. Le site ORDER BY peut se faire en accédant aux données à l'aide d'un index en premier lieu. Il existe de nombreux types de jointures qui peuvent également être réalisées.

Answer 2

Nous savons que les données de B va être filtrée par la jointure (interne) vers A (les données dans A est également filtré). Ainsi, si nous effectuons une jointure (interne) à partir de B a C donc l'ensemble C est également filtré par la relation avec A . Et notez aussi que tous les doublons de la jointure seront inclus .

Cependant, l'ordre dans lequel cela se produit dépend de l'optimiseur ; il pourrait décider de faire le B / C rejoindre d'abord puis introduire A ou toute autre séquence (probablement basée sur le nombre estimé de lignes de chaque jointure et les index appropriés).

---

CEPENDANT ; dans votre dernier exemple, vous utilisez un LEFT OUTER rejoindre ; donc Account n'est pas filtré du tout et peut être dupliquée si l'une des autres tables a des correspondances multiples.

Existe-t-il des doublons (par compte) dans BalanceToken ?

Answer 3

Je trouve souvent utile de visualiser le plan d'exécution réel. Dans l'analyseur de requêtes/management studio, vous pouvez activer cette fonction pour les requêtes à partir du menu Requête, ou en utilisant Ctrl+M. Après avoir exécuté la requête, le plan qui a été exécuté est affiché dans un autre onglet de résultats. Vous verrez que C et B sont d'abord joints, puis que le résultat est joint à A. Le plan peut varier en fonction des informations dont dispose le SGBD, car les deux jointures sont internes, ce qui fait que A-et-B-et-C. Ce que je veux dire, c'est que le résultat sera le même, quelle que soit la jointure effectuée en premier, mais le temps nécessaire peut être très différent, et c'est là que l'optimiseur et les conseils entrent en jeu.

Answer 4

Les jointures peuvent être délicates, et une grande partie du comportement est bien sûr dictée par la façon dont les données sont stockées dans les tables réelles.

Sans voir les tableaux, il est difficile de donner une réponse claire dans votre cas particulier, mais je pense que le problème de base est que vous additionnez plusieurs ensembles de résultats qui sont combinés en un seul.

Peut-être qu'au lieu de faire des jointures multiples, vous devriez créer deux tables temporaires séparées dans votre requête, l'une avec l'ID du compte, la date et la somme des soldes d'ouverture, une seconde avec l'ID du compte, la date et la somme des soldes de fermeture, puis joindre ces deux tables sur l'ID du compte et la date.

Pour savoir exactement ce qui se passe avec les jointures, également dans votre cas spécifique, je ferais ce qui suit :

Modifier la partie initiale

SELECT accountID Accountbalancedate, sum(...) as openingbalance, somme(...) comme solde de clôture FROM

pour simplement

"SELECT * FROM"

Étudiez le tableau qui en résulte, et vous verrez exactement quelles données sont dupliquées. Supprimez les jointures une par une et voyez ce qui se passe. Cela devrait vous donner une idée de ce qui, dans vos données particulières, est à l'origine des doublons.

Si vous ouvrez la requête dans SQL server management studio (la version gratuite existe), vous pouvez modifier la requête dans le concepteur. La vue visuelle de la façon dont les tables sont jointes peut également vous aider à comprendre ce qui se passe.