Bloque 5 · Combinar tablas PostgreSQL 17 northwind

Joins múltiples y granularidad

Este tema no está en las guías al uso. Y es, con diferencia, el error más caro que se puede cometer con SQL: no da error, no da aviso, y produce números que parecen correctos. Todo lo que se ha ido dejando pendiente en los temas anteriores —el sum(DISTINCT) del tema 12, el count(DISTINCT o.order_id) que he repetido cinco veces— aterriza aquí.

1. Qué es

Granularidad = qué representa una fila.

Tabla	Una fila es…
`customers`	Un cliente
`orders`	Un pedido
`order_details`	Una línea de un pedido
`employee_territories`	Un territorio de un empleado

Un JOIN puede cambiar la granularidad del resultado. Y cuando lo hace, cualquier sum() o count() sobre columnas de la tabla original queda multiplicado.

830 pedidos  ──JOIN order_details──▶  2.155 filas

Después de ese JOIN, una fila ya no es un pedido: es una línea. El freight del pedido 10248 aparece 3 veces, una por cada línea. Sumarlo cuenta ese flete tres veces.

Eso se llama fan-out, y es un multiplicador silencioso.

2. La regla

Un JOIN multiplica filas cuando el lado al que unes puede tener más de una fila por pareja.

Relación	Fan-out
`orders` → `customers` (muchos a uno)	❌ No. Un pedido, un cliente
`orders` → `order_details` (uno a muchos)	✅ Sí. Un pedido, N líneas
`products` → `categories` (muchos a uno)	❌ No
`employees` → `employee_territories` (uno a muchos)	✅ Sí

Unir "hacia arriba" (hacia la tabla del uno) es seguro. Unir "hacia abajo" (hacia la tabla del muchos) multiplica.

Y de ahí sale el hábito que hay que coger:

Cuenta las filas antes y después de cada JOIN. Si el número cambia y no esperabas que cambiara, para.

3. El fan-out, medido

SELECT count(*) FROM orders;                                              --   830
SELECT count(*) FROM orders o JOIN order_details od ON od.order_id = o.order_id;  -- 2155

Y qué le pasa a una suma:

SELECT round(sum(o.freight), 2) FROM orders o;                            --  64942.69   ✅ real
SELECT round(sum(o.freight), 2) FROM orders o
JOIN order_details od ON od.order_id = o.order_id;                        -- 207306.10   ❌ ×3,2

Visto pedido a pedido, no hay misterio:

SELECT o.order_id, count(*) AS lineas, o.freight, sum(o.freight) AS flete_sumado
FROM orders o JOIN order_details od ON od.order_id = o.order_id
WHERE o.order_id IN (10248, 10250)
GROUP BY o.order_id, o.freight;

 order_id | lineas | freight | flete_sumado
----------+--------+---------+--------------
    10248 |      3 |   32.38 |        97.14
    10250 |      3 |   65.83 |       197.49

32,38 × 3 = 97,14. El motor hace exactamente lo que le pediste. El problema es que le pediste otra cosa.

3.1 Doble fan-out: el efecto multiplicativo

Ahora dos JOIN que multiplican, encadenados:

SELECT count(*) FROM orders o
JOIN order_details       od ON od.order_id    = o.order_id
JOIN employee_territories et ON et.employee_id = o.employee_id;

 count
-------
 10129

830 → 10.129 filas. Cada pedido se multiplica por sus líneas y por los territorios de su empleado (entre 2 y 10 según el empleado). Los fan-outs no se suman: se multiplican entre sí.

Y el flete:

SELECT round(sum(o.freight), 2) FROM orders o
JOIN order_details       od ON od.order_id    = o.order_id
JOIN employee_territories et ON et.employee_id = o.employee_id;

 1026997.96

Un millón contra los 64.942 reales: ×15,8. Y sigue siendo un número con dos decimales que cabe en una celda.

Ese es el peligro completo del tema en una frase: el resultado equivocado tiene exactamente el mismo aspecto que el correcto.

4. Las tres formas de arreglarlo

4.1 ❌ La que no funciona: `DISTINCT`

SELECT round(sum(DISTINCT o.freight), 2) FROM orders o
JOIN order_details od ON od.order_id = o.order_id;

 64526.94

64.526,94 contra 64.942,69 reales. Se parece muchísimo. Está mal.

sum(DISTINCT ...) no suma "un flete por pedido": suma valores de flete distintos. Dos pedidos que casualmente costaron 32,38 cuentan una sola vez. 415,75 perdidos entre 65.000, imposibles de detectar mirando.

count(DISTINCT ...) sí es válido —contar identificadores únicos es exactamente lo que hace— pero sum(DISTINCT) y avg(DISTINCT) casi nunca lo son.

4.2 ✅ La correcta: agregar antes de unir

Cada métrica se calcula a su granularidad, y solo después se juntan los resultados:

WITH ventas AS (
    SELECT employee_id, count(*) AS pedidos, sum(freight) AS flete
    FROM orders
    GROUP BY employee_id
),
lineas AS (
    SELECT o.employee_id,
           sum(od.unit_price * od.quantity * (1 - od.discount)) AS facturacion
    FROM orders o
    JOIN order_details od ON od.order_id = o.order_id
    GROUP BY o.employee_id
),
terr AS (
    SELECT employee_id, count(*) AS territorios
    FROM employee_territories
    GROUP BY employee_id
)
SELECT v.employee_id,
       v.pedidos,
       round(v.flete, 2)       AS flete,
       round(l.facturacion, 2) AS facturacion,
       t.territorios
FROM ventas v
JOIN lineas l USING (employee_id)
JOIN terr   t USING (employee_id)
ORDER BY facturacion DESC;

 employee_id | pedidos |  flete   | facturacion | territorios
-------------+---------+----------+-------------+-------------
           4 |     156 | 11346.14 |   232890.85 |           3
           3 |     127 | 10884.74 |   202812.84 |           4
           1 |     123 |  8836.64 |   192107.60 |           2
           2 |      96 |  8696.41 |   166537.76 |           7
           8 |     104 |  7487.88 |   126862.28 |           4

Las tres métricas son correctas a la vez, cosa que ninguna consulta de un solo FROM puede conseguir:

pedidos y flete a granularidad de pedido
facturacion a granularidad de línea
territorios a granularidad de territorio

Y se comprueba:

SELECT round(sum(flete), 2) FROM (SELECT employee_id, sum(freight) AS flete FROM orders GROUP BY 1) t;

 64942.69     ✅

Los CTE (WITH) se ven en cte-with; aquí basta con leerlos como "consultas con nombre".

Este es el patrón que hay que memorizar del tema: cuando un informe mezcla métricas de distinta granularidad, se agrega cada una por su lado y se unen los agregados. No hay atajo.

4.3 ⚠️ La tercera: `count(DISTINCT)`, válida pero limitada

SELECT o.employee_id,
       count(DISTINCT o.order_id) AS pedidos,
       sum(od.unit_price * od.quantity * (1 - od.discount)) AS facturacion
FROM orders o
JOIN order_details od ON od.order_id = o.order_id
GROUP BY o.employee_id;

Correcta, y más corta. Funciona porque solo hay una métrica de la tabla inflada (pedidos) y se resuelve contando ids únicos. En cuanto necesites sumar algo del pedido —el flete— deja de servir y hay que volver al patrón de arriba.

5. Un fan-out que no lo parece

SELECT c.category_name,
       count(DISTINCT p.product_id) AS productos,
       count(*)                     AS filas
FROM categories c
JOIN products      p  ON p.category_id = c.category_id
JOIN order_details od ON od.product_id = p.product_id
GROUP BY 1 ORDER BY 1 LIMIT 4;

 category_name  | productos | filas
----------------+-----------+-------
 Beverages      |        12 |   404
 Condiments     |        12 |   216
 Confections    |        13 |   334
 Dairy Products |        10 |   366

12 productos, 404 filas. El primer JOIN no multiplica (cada producto tiene una categoría); el segundo sí, y mucho.

Si el informe pide "productos por categoría" y alguien escribe count(*), el número sale 33 veces mayor. Y count(*) es lo que se escribe por reflejo.

6. Cómo detectarlo antes de que salga publicado

Cuenta filas después de cada JOIN. Es la comprobación más barata que existe.
Comprueba los totales contra la tabla original. sum(freight) sobre orders a secas debe cuadrar con el total de tu informe.
Desconfía de cualquier count(*) en una consulta con más de dos tablas.
Si has escrito DISTINCT para arreglar filas de más, párate. Estás tapando, no arreglando.
Suma las filas del desglose y compáralas con el total. Si no cuadran, hay fan-out o hay filas perdidas.
Un número sospechosamente redondo o sospechosamente grande suele ser un múltiplo exacto del bueno. Divide entre 2, 3 o 2,6 y mira si te suena.

7. Errores comunes

sum() de una columna del "uno" tras unir con el "muchos". El error del tema.
count(*) en un join múltiple. Cuenta la granularidad final, no lo que crees.
sum(DISTINCT) como parche. Da un número plausible y falso.
avg() tras un fan-out. Peor que sum: la media se pondera sola por el número de líneas, y ni siquiera es un múltiplo exacto que puedas detectar.
Encadenar tres o más tablas del "muchos" sin pensar. El multiplicador es el producto de todos.
Suponer que porque el JOIN es por la clave primaria no multiplica. Depende de qué lado es la clave primaria.
Validar el informe solo mirando si "tiene sentido". 207.306 tiene sentido. Es tres veces el número real.

8. Diferencias con SQL Server

Ninguna. El fan-out es una propiedad del álgebra relacional, no del motor: ocurre igual en PostgreSQL, SQL Server, Oracle, MySQL, BigQuery y Spark.

Lo único que cambia son las herramientas para evitarlo:

	PostgreSQL	SQL Server
Pre-agregar en CTE	✅ `WITH`	✅ `WITH`
`count(DISTINCT)`	✅	✅
Agregar en el `FROM` correlacionado	`LATERAL`	`CROSS APPLY`
`FILTER`	✅	❌ → `CASE`

Y una nota que vale para cualquier herramienta de BI: Power BI, Tableau y Looker tienen exactamente este problema cuando se les da un modelo con relaciones uno-a-muchos mal declaradas. La diferencia es que allí el fan-out lo produce la herramienta y ni siquiera ves el SQL. Entenderlo aquí es lo que te permite depurarlo allí.

9. Ejercicios

Cuenta las filas de orders, de orders JOIN customers, y de orders JOIN order_details. Explica cada número antes de ejecutarlo.
Calcula el flete total por país de dos formas —directa y tras unir con order_details— y compáralas.
Escribe un informe por categoría con: número de productos, unidades vendidas y facturación. Los tres correctos.
¿Cuántos territorios tiene cada empleado y cuántos pedidos gestionó? Sin que ninguna de las dos cifras se infle.
Explica por qué esta consulta da un número mayor que el real y cómo lo arreglarías: sql SELECT c.company_name, count(*) AS pedidos FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_details od ON od.order_id = o.order_id GROUP BY c.company_name;

10. Pregunta de negocio

Dirección pide un cuadro de mando por país: número de clientes, número de pedidos, facturación y flete total. Una fila por país. El año pasado el mismo informe daba un flete "que no cuadraba con contabilidad" y nadie encontró el motivo.

Ya sabes exactamente cuál era el motivo.

? RespuestaRespóndela antes de abrir

El motivo del flete que no cuadraba es exactamente el de la sección 3: el informe unía orders con order_details para sacar la facturación, y sumaba el flete en la misma consulta. El flete salía multiplicado por las líneas de cada pedido, ~3,2 veces.

WITH por_pais AS (
    SELECT ship_country          AS pais,
           count(*)              AS pedidos,
           sum(freight)          AS flete
    FROM orders
    GROUP BY ship_country
),
facturacion AS (
    SELECT o.ship_country AS pais,
           sum(od.unit_price * od.quantity * (1 - od.discount)) AS facturacion
    FROM orders o
    JOIN order_details od ON od.order_id = o.order_id
    GROUP BY o.ship_country
),
clientes AS (
    SELECT country AS pais, count(*) AS clientes
    FROM customers
    GROUP BY country
)
SELECT p.pais,
       coalesce(cl.clientes, 0)   AS clientes,
       p.pedidos,
       round(f.facturacion, 2)    AS facturacion,
       round(p.flete, 2)          AS flete
FROM por_pais p
JOIN      facturacion f  USING (pais)
LEFT JOIN clientes    cl USING (pais)
ORDER BY facturacion DESC;

Las cuatro decisiones:

Tres granularidades, tres CTE. Clientes se cuentan en customers, pedidos y flete en orders, facturación en order_details. Mezclarlas en un solo FROM es la causa del bug que arrastraban.
La comprobación que hay que hacer antes de entregar: SELECT sum(freight) FROM orders da 64.942,69, y la suma de la columna flete del informe tiene que dar lo mismo. Si no cuadra con la tabla origen, no cuadra con contabilidad.
LEFT JOIN con clientes. El país del pedido (ship_country) y el del cliente (country) son columnas distintas y no tienen por qué coincidir. Un INNER JOIN borraría del cuadro de mando cualquier país al que se envía sin tener clientes registrados allí.
Lo que hay que decir al entregar: la columna clientes cuenta clientes domiciliados en ese país y las demás cuentan actividad enviada a ese país. En Northwind coinciden en las 830 filas, pero son dos conceptos distintos con el mismo nombre de columna — y esa ambigüedad es la siguiente que hará que "los números no cuadren".

✓ SolucionesResuélvelos antes de abrir

SELECT count(*) FROM orders;                                                       --  830
SELECT count(*) FROM orders o JOIN customers     c  ON c.customer_id = o.customer_id;  --  830
SELECT count(*) FROM orders o JOIN order_details od ON od.order_id   = o.order_id;     -- 2155

830: la tabla.
830: customers está del lado del "uno" — cada pedido tiene exactamente un cliente. No multiplica.
2155: order_details está del lado del "muchos". Multiplica por 2,6 de media.

SELECT round(sum(freight),2) FROM orders WHERE ship_country = 'Germany';                   -- 11283.28
SELECT round(sum(o.freight),2) FROM orders o
JOIN order_details od ON od.order_id = o.order_id WHERE o.ship_country = 'Germany';         -- 38170.75

11.283 contra 38.170. El segundo es 3,4 veces el primero: exactamente las líneas por pedido de Alemania. Ningún error, ninguna advertencia.

WITH cat_productos AS (
    SELECT c.category_id, c.category_name, count(*) AS productos
    FROM categories c
    JOIN products p ON p.category_id = c.category_id
    GROUP BY c.category_id, c.category_name
),
cat_ventas AS (
    SELECT p.category_id,
           sum(od.quantity)                                     AS unidades,
           sum(od.unit_price * od.quantity * (1 - od.discount))  AS facturacion
    FROM order_details od
    JOIN products p ON p.product_id = od.product_id
    GROUP BY p.category_id
)
SELECT cp.category_name, cp.productos, cv.unidades, round(cv.facturacion, 2) AS facturacion
FROM cat_productos cp
JOIN cat_ventas    cv USING (category_id)
ORDER BY facturacion DESC;

 category_name  | productos | unidades | facturacion
----------------+-----------+----------+-------------
 Beverages      |        12 |     9532 |   267868.18
 Dairy Products |        10 |     9149 |   234507.29
 Confections    |        13 |     7906 |   167357.23

Dos granularidades, dos agregaciones, un JOIN entre resultados ya agregados. Intentarlo en una sola consulta obliga a count(DISTINCT p.product_id) — que aquí funcionaría, pero deja de funcionar en cuanto una métrica haya que sumarla en vez de contarla.

WITH terr AS (
    SELECT employee_id, count(*) AS territorios FROM employee_territories GROUP BY 1
),
ped AS (
    SELECT employee_id, count(*) AS pedidos FROM orders GROUP BY 1
)
SELECT e.employee_id, e.last_name, t.territorios, p.pedidos
FROM employees e
JOIN terr t USING (employee_id)
JOIN ped  p USING (employee_id)
ORDER BY p.pedidos DESC;

 employee_id | last_name | territorios | pedidos
-------------+-----------+-------------+---------
           4 | Peacock   |           3 |     156
           3 | Leverling |           4 |     127
           1 | Davolio   |           2 |     123

Sin pre-agregar, orders JOIN employee_territories da 3.960 filas en vez de 830, y las dos columnas salen infladas. Margaret Peacock aparecería con 468 pedidos (156 × 3) y 468 territorios.

5. Porque count(*) después de unir con order_details cuenta líneas de pedido, no pedidos. Cada cliente sale con unas 2,6 veces sus pedidos reales.

Dos arreglos:

-- A: contar ids únicos
SELECT c.company_name, count(DISTINCT o.order_id) AS pedidos ...

-- B: no unir con order_details, que no hacía falta para nada
SELECT c.company_name, count(*) AS pedidos
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.company_name;

B es la mejor. El JOIN con order_details no aportaba ninguna columna al resultado: estaba de más. Cuando encuentres un fan-out, la primera pregunta no es cómo compensarlo — es si esa tabla tenía que estar ahí.