sql - tablas - tipos de join oracle

Comprender cómo funciona JOIN cuando están involucradas 3 o más tablas.

Question

Me pregunto si alguien puede ayudar a mejorar mi comprensión de JOINs en SQL. [Si es significativo para el problema, estoy pensando específicamente en MS SQL Server.]

Tome 3 tablas A, B [A relacionadas por algunos A.AId], y C [B relacionadas con C por algunas B.BId]

Si redacto una consulta, por ejemplo

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

Todo bien, soy dulce con la forma en que esto funciona.

¿Qué sucede cuando se agrega la Tabla C (o alguna otra D, E, ...)?

En la situación

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

¿A qué se está uniendo C? - ¿Es esa la tabla B (y los valores en la tabla B?) ¿O es algún otro conjunto de resultados temporales que es el resultado de la unión A + B a la que se une la tabla C?

[La implicación de que no todos los valores que están en la tabla B necesariamente estarán en el conjunto de resultados temporales A + B en base a la condición de unión para A, B]

Un ejemplo específico (y bastante artificial) de por qué estoy preguntando es porque estoy tratando de comprender el comportamiento que estoy viendo en lo siguiente:

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

Conceptualmente, el saldo de cierre de una fecha sería el saldo inicial de mañana

Si estaba tratando de encontrar una lista de todos los saldos de apertura y cierre de una cuenta

Podría hacer algo como

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  

Las cosas funcionan como esperaba hasta que el último JOIN presente las fichas de saldo de cierre, donde termino con duplicados en el resultado.

[Puedo arreglar con DISTINCT - pero estoy tratando de entender por qué está sucediendo lo que está sucediendo]

Me han dicho que el problema se debe a que la relación entre Balance y BalanceToken es 1: M, y que cuando traigo el último JOIN obtengo duplicados porque el 3rd JOIN ya ha traído BalanceIds varias veces al (supongo) conjunto de resultados temporales.

Sé que las tablas de ejemplo no se ajustan al buen diseño de DB

Disculpas por el ensayo, gracias por cualquier aclaración :)

Editar en respuesta a la pregunta de Marc

Conceptualmente para una cuenta no debería haber duplicados en BalanceToken para una cuenta (por Fecha de contabilidad): creo que el problema se produce porque el saldo de cierre de Cuentas / Contabilidad es el saldo de apertura de cuentas para el día siguiente, por lo que cuando se une al saldo, BalanceToken varias veces para obtener saldos de apertura y cierre Creo que los saldos (BalanceId''s) se están incorporando a la "combinación de resultados" varias veces. Si ayuda a aclarar el segundo ejemplo, considérelo una conciliación diaria, por lo tanto, se une a la izquierda, es posible que no se haya calculado un saldo de apertura (y / o de cierre) para una combinación dada de cuenta / fecha contable.

Answer 1

A menudo me resulta útil ver el plan de ejecución real. En el analizador de consultas / estudio de gestión, puede activar las consultas desde el menú Consulta o usar Ctrl + M. Después de ejecutar la consulta, el plan que se ejecutó se muestra en otra pestaña de resultados. A partir de esto, verás que C y B se unen primero, y luego el resultado se combina con A. El plan puede variar según la información que tenga el DBMS porque ambas uniones son internas, por lo que es A-y-B y C . Lo que quiero decir es que el resultado será el mismo independientemente de cuál se una primero, pero el tiempo que toma puede diferir enormemente, y aquí es donde entran en juego el optimizador y las sugerencias.

Answer 2

Las uniones pueden ser complicadas, y gran parte del comportamiento depende, por supuesto, de cómo se almacenan los datos en las tablas reales.

Sin ver las tablas, es difícil dar una respuesta clara en su caso particular, pero creo que el problema básico es que está sumando varios conjuntos de resultados que se combinan en uno solo.

Quizás en lugar de varias combinaciones, debe hacer dos tablas temporales separadas en su consulta, una con el ID de cuenta, fecha y suma de los desequilibrios de apertura, una segunda con el ID de cuenta, fecha y suma de saldos de cierre, y luego unir esas dos en ID de cuenta y fecha.

Para saber exactamente qué está sucediendo con las uniones, también en su caso específico, haría lo siguiente:

Cambiar la parte inicial

SELECCIONAR accountID Accountbalancedate, sum (...) como openingbalance, sum (...) como closingbalance FROM

simplemente

"SELECCIONAR DE"

Estudie la tabla resultante y verá exactamente qué datos se duplican. Elimine las uniones una a una y vea qué sucede. Esto debería darte una pista de qué se trata acerca de tus datos en particular lo que está causando los engaños.

Si abre la consulta en SQL Server Management Studio (existe la versión gratuita) puede editar la consulta en el diseñador. La vista visual de cómo se unen las tablas también podría ayudarlo a darse cuenta de lo que está sucediendo.

Answer 3

Sabemos que los datos de B van a ser filtrados por la unión (interna) a A (los datos en A también se filtran). Entonces, si unimos (interno) de B a C , el conjunto C también se filtra por la relación con A Y tenga en cuenta también que se incluirá cualquier duplicado de la unión.

Sin embargo; en qué orden ocurre esto depende del optimizador; podría decidir hacer la combinación B / C primero y luego introducir A , o cualquier otra secuencia (probablemente en función del número estimado de filas de cada combinación y los índices apropiados).

SIN EMBARGO; en su ejemplo posterior utiliza una combinación LEFT OUTER ; por lo que Account no se filtra en absoluto , y puede que se duplique si cualquiera de las otras tablas tiene coincidencias múltiples.

¿Hay duplicados (por cuenta) en BalanceToken ?

Answer 4

Conceptualmente, aquí está lo que sucede cuando unes tres tablas juntas.

1. El optimizador presenta un plan, que incluye una orden de unión. Podría ser A, B, C o C, B, A o cualquiera de las combinaciones
2. El motor de ejecución de consultas aplica cualquier predicado (cláusula WHERE ) a la primera tabla que no involucra ninguna de las otras tablas. Selecciona las columnas mencionadas en las condiciones JOIN o la lista SELECT o la lista ORDER BY . Llamar a este resultado A
3. Une este conjunto de resultados a la segunda tabla. Para cada fila, se une a la segunda tabla, aplicando cualquier predicado que pueda aplicarse a la segunda tabla. Esto da como resultado otro conjunto de resultados temporales.
4. Luego se une en la mesa final y aplica la ORDER BY

Esto es conceptualmente lo que sucede. De hecho, hay muchas optimizaciones posibles en el camino. La ventaja del modelo relacional es que la sólida base matemática permite varias transformaciones del plan sin modificar la corrección.

Por ejemplo, realmente no hay necesidad de generar los conjuntos de resultados completos en el camino. En su lugar, ORDER BY se puede hacer accediendo a los datos usando un índice en primer lugar. También hay muchos tipos de uniones que se pueden hacer.