Une dos tablas sin romper el número

La lección que más números rompe

Una unión combina filas de dos tablas que comparten una clave. Un INNER JOIN conserva solo las filas que coinciden en ambos lados. Un LEFT JOIN conserva todas las filas de la tabla izquierda y rellena con nulls donde no hay coincidencia a la derecha. Elegir la unión equivocada o sumar después de una unión incorrecta produce un número rotundamente erróneo. Esta lección rompe más totales que ninguna otra.

La expansión infla un total

orders tiene una fila por pedido. order_items tiene una fila por línea, 2,40 por pedido de media. Únelas y suma una columna del nivel del pedido: el total de cada pedido se contará una vez por cada línea.

Primero el total correcto, con granularidad de pedido:

bruin query --connection duckdb-default \
  --description "correct total order value at order grain" \
  --query "SELECT SUM(order_total) AS revenue FROM orders;"

Devuelve 604.065,00 (el terminal imprime 604065). Ahora la versión rota, que suma la misma columna después de unirla a las líneas:

bruin query --connection duckdb-default \
  --description "inflated total order value after the lines join" \
  --query "SELECT SUM(o.order_total) AS revenue FROM orders o JOIN order_items oi ON o.order_id = oi.order_id;"

Se ejecuta sin error y devuelve 1.44977182e+06: notación científica para 1.449.771,82, que es 2,4 veces la cifra correcta. No hay ninguna advertencia. order_total pertenece al pedido, así que debe sumarse con granularidad de pedido, no después de una unión de uno a muchos. La solución es agregar antes de unir o sumar solo columnas del nivel de línea, como quantity * net_price.

Puedes hacer que la base de datos realice la división por ti:

bruin query --connection duckdb-default \
  --description "how inflated is the joined total" \
  --query "SELECT ROUND(SUM(o.order_total) / 604065.0, 2) AS inflation FROM orders o JOIN order_items oi ON o.order_id = oi.order_id;"

inflation devuelve exactamente 2,4: el número medio de líneas por pedido, y no es una coincidencia. Cada pedido se contó una vez por línea.

Un LEFT JOIN que se convierte silenciosamente en INNER JOIN

order_items tiene 15 líneas cuyo product_id no está en products. Un INNER JOIN con products descarta esas 15 líneas y, con ellas, 3.637,89 de ingresos de línea sin decir nada. Un LEFT JOIN las conserva. Comprueba por ti mismo las líneas descartadas: este patrón, un LEFT JOIN filtrado a las filas sin coincidencia, muestra lo que un INNER JOIN descartaría silenciosamente:

bruin query --connection duckdb-default \
  --description "order lines whose product does not exist" \
  --query "SELECT COUNT(*) AS orphan_lines, ROUND(SUM(oi.quantity * oi.net_price), 2) AS orphan_revenue FROM order_items oi LEFT JOIN products p ON oi.product_id = p.product_id WHERE p.product_id IS NULL;"

Hay una segunda trampa. Un LEFT JOIN protege las filas izquierdas solo si el filtro de la tabla derecha está en la cláusula ON. Mueve ese filtro a WHERE y las filas sin coincidencia no cumplirán la condición y desaparecerán, convirtiendo de nuevo el LEFT JOIN en un INNER JOIN. Pon las condiciones de la tabla derecha en ON y reserva WHERE para la tabla izquierda.

Añadir DISTINCT no arregla la expansión. Hace que el recuento parezca razonable mientras la granularidad sigue siendo incorrecta. Considera cualquier DISTINCT en una consulta algo que debes justificar, no una reparación.

Tu tarea

Reproduce las tres cifras en queries/03-joins.sql. Escribe el total correcto con granularidad de pedido y confirma 604.065,00. Después suma order_total tras unirlo con order_items y observa cómo se infla hasta 1.449.771,82. Ejecuta después la consulta de inflación para confirmar que el factor es exactamente 2,4:

bruin query --connection duckdb-default \
  --description "how inflated is the joined total" \
  --query "SELECT ROUND(SUM(o.order_total) / 604065.0, 2) AS inflation FROM orders o JOIN order_items oi ON o.order_id = oi.order_id;"

Por último, encuentra las líneas huérfanas que descartaría un INNER JOIN - 15 líneas y 3.637,89 de ingresos - con el patrón de LEFT JOIN anterior. No uses DISTINCT para arreglar nada de esto.

Comprueba tu comprensión

• ¿Cuál es el valor total correcto de los pedidos y hasta qué cifra lo infla la unión?
• ¿Por qué exactamente 2,4 veces?
• ¿Cuántas líneas huérfanas y cuántos ingresos descartaría un INNER JOIN?

Hazlo con tu agente

Di next lesson y tu agente te enseñará esto, te hará estas preguntas y después asignará la tarea anterior. Hazla a mano y luego di review my work - ejecutará tu consulta, la comparará con una rúbrica y te dirá qué corregir o marcará la lección como terminada.