PostgreSQL
Joins y cardinalidad del resultado
Uso de INNER, LEFT, RIGHT, FULL, CROSS y self joins, con atención a cardinalidad, duplicados, condiciones y preservación de filas.
- Última actualización
- Actualizada
- Nivel
- Fundamentos
PostgreSQL
Uso de INNER, LEFT, RIGHT, FULL, CROSS y self joins, con atención a cardinalidad, duplicados, condiciones y preservación de filas.
Un join no “pega tablas”. Construye pares de filas que satisfacen una condición. La habilidad principal es predecir la cardinalidad: cuántas filas del resultado puede producir cada fila de entrada.
Antes de escribir un join, responde:
¿qué representa cada tabla?
¿cuál es la clave de cada lado?
¿cuántas coincidencias puede tener una fila?
¿qué debe ocurrir cuando no existe coincidencia?La condición del join define la relación lógica. Una FK ayuda a garantizar existencia, pero no obliga al planner a usar un algoritmo concreto ni evita multiplicación cuando se combinan varias relaciones uno-a-muchos.
Los datos normalizados viven en relaciones separadas. Para construir una vista útil necesitamos combinarlos:
orders.customer_id
→ customers.idSin joins, la aplicación haría múltiples consultas y combinaría datos manualmente, lo que puede producir N+1, snapshots inconsistentes y más viajes de red.
El riesgo opuesto es unir demasiado y generar más filas de las esperadas. Los totales inflados suelen ser un problema de cardinalidad, no de SUM.
Para cada fila del lado izquierdo, la condición encuentra:
0 coincidencias
→ INNER la elimina
→ LEFT conserva una fila con NULL a la derecha
1 coincidencia
→ produce 1 fila
N coincidencias
→ produce N filasSi después unes otra relación con M coincidencias por la misma entidad, puedes obtener N × M filas.
SELECT
o.id,
o.status,
c.name AS customer_name
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id;Solo conserva orders cuya condición encuentra customer.
Si la FK es NOT NULL y está validada, todas las orders deberían coincidir. Si faltan filas, puede haber:
NOT VALID sin completar.En multi-tenancy:
JOIN customers AS c
ON c.tenant_id = o.tenant_id
AND c.id = o.customer_idUnir solo por id puede mezclar tenants si el ID no es globalmente único o si la query debe reforzar scope.
La condición debe reflejar la clave real, no solo una columna que “parece coincidir” en datos pequeños.
SELECT
p.id,
p.name,
i.quantity
FROM products AS p
LEFT JOIN inventory AS i
ON i.product_id = p.id
AND i.branch_id = $1;Conserva todos los products. Si no existe inventory para esa branch, i.quantity es NULL.
La condición branch_id pertenece al ON porque define qué fila de inventory cuenta como coincidencia.
Consulta distinta:
FROM products p
LEFT JOIN inventory i ON i.product_id = p.id
WHERE i.branch_id = $1;Para products sin inventory, i.branch_id es NULL; NULL = $1 produce UNKNOWN y WHERE elimina la fila. El resultado se comporta como INNER JOIN respecto a ese filtro.
Regla práctica:
condición que define la coincidencia opcional
→ ON
filtro final sobre filas ya construidas
→ WHERENo es una regla sintáctica universal; debes decidir la semántica deseada.
SELECT p.id
FROM products p
LEFT JOIN inventory i
ON i.product_id = p.id
AND i.branch_id = $1
WHERE i.product_id IS NULL;Esto busca products sin inventory.
NOT EXISTS suele expresar la intención más directamente:
SELECT p.id
FROM products p
WHERE NOT EXISTS (
SELECT 1
FROM inventory i
WHERE i.product_id = p.id
AND i.branch_id = $1
);El planner puede transformar ambas formas; elige la más clara y verifica el plan.
customers RIGHT JOIN ordersConserva el lado derecho. Normalmente puede reescribirse como LEFT JOIN intercambiando tablas, lo que suele mejorar legibilidad.
No es incorrecto, pero un equipo puede estandarizar LEFT JOIN para leer siempre desde la entidad principal hacia dependencias opcionales.
Conserva no coincidencias de ambos lados:
SELECT
COALESCE(a.external_id, b.external_id) AS external_id,
a.amount AS system_a_amount,
b.amount AS system_b_amount
FROM imported_payments a
FULL JOIN provider_payments b
ON b.external_id = a.external_id;Útil para reconciliación:
Costes:
SELECT b.id AS branch_id, p.id AS product_id
FROM branches b
CROSS JOIN products p;Produce todas las combinaciones. Puede ser intencional para generar matriz branch-product.
Cardinalidad:
100 branches × 10 000 products = 1 000 000 filasUn join sin condición puede convertirse accidentalmente en producto cartesiano. Estima cardinalidad antes de ejecutarlo.
SELECT child.id, parent.name AS parent_name
FROM categories child
LEFT JOIN categories parent
ON parent.id = child.parent_id;Aliases son obligatorios conceptualmente para distinguir roles de la misma tabla.
Un self join no detecta jerarquías profundas; para eso puede usarse CTE recursiva.
Supón:
order 1
→ 3 items
→ 2 paymentsConsulta:
SELECT o.id, oi.quantity, p.amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN payments p ON p.order_id = o.id;Resultado: 6 filas. Cada item se combina con cada payment.
Si haces:
sum(oi.quantity)
sum(p.amount)ambos totales quedan multiplicados.
WITH item_totals AS (
SELECT order_id, sum(quantity * unit_price) AS item_total
FROM order_items
GROUP BY order_id
),
payment_totals AS (
SELECT order_id, sum(amount) AS paid_total
FROM payments
GROUP BY order_id
)
SELECT
o.id,
it.item_total,
pt.paid_total
FROM orders o
LEFT JOIN item_totals it ON it.order_id = o.id
LEFT JOIN payment_totals pt ON pt.order_id = o.id;Cada CTE produce máximo una fila por order. El join final conserva esa cardinalidad.
Alternativas:
Pregunta:
¿Qué orders tienen al menos un item agotado?
SELECT o.id
FROM orders o
WHERE EXISTS (
SELECT 1
FROM order_items oi
JOIN inventory i
ON i.product_id = oi.product_id
AND i.branch_id = o.branch_id
WHERE oi.order_id = o.id
AND i.quantity < oi.quantity
);EXISTS devuelve true al encontrar una coincidencia; no multiplica la fila externa.
Esto es semánticamente distinto de JOIN + DISTINCT:
SELECT p.id
FROM products p
WHERE NOT EXISTS (
SELECT 1
FROM order_items oi
WHERE oi.product_id = p.id
);Busca products nunca usados.
Suele ser más seguro que NOT IN cuando la subquery puede devolver NULL.
Permite que una subquery en FROM use columnas anteriores:
SELECT
p.id,
latest.price,
latest.valid_from
FROM products p
LEFT JOIN LATERAL (
SELECT pp.price, pp.valid_from
FROM product_prices pp
WHERE pp.product_id = p.id
ORDER BY pp.valid_from DESC, pp.id DESC
LIMIT 1
) latest ON true;Flujo:
Con índice (product_id, valid_from DESC, id DESC), puede ser eficiente para pocos/muchos products según plan y volumen.
Una window function puede resolver lo mismo globalmente; compara planes.
SELECT *
FROM orders
JOIN customers USING (customer_id);USING fusiona la columna de join en la salida. Es cómodo cuando el nombre y significado son iguales.
Riesgos:
SELECT * cambia shape.Une automáticamente columnas con el mismo nombre. Es frágil: añadir una columna homónima cambia la consulta sin editar SQL.
Evítalo en código de producción. Es mejor declarar la condición.
La sintaxis JOIN no determina el algoritmo.
Para cada fila externa busca coincidencias internas.
Bueno cuando:
Malo cuando estimaciones erróneas hacen millones de búsquedas.
Construye hash de una entrada y prueba la otra.
Bueno para igualdad y conjuntos medianos/grandes.
Costes:
Consume entradas ordenadas por la key.
Bueno cuando:
El planner elige según estadísticas y costes.
Una FK hija no crea índice automáticamente.
Consulta:
orders JOIN customers ON customers.id = orders.customer_idorders.customer_id puede necesitar índice según dirección, filtros y volumen.Para tenant:
CREATE INDEX orders_tenant_customer_idx
ON orders(tenant_id, customer_id);El índice debe alinearse con filtros y join, no solo existir.
Si una FK validada garantiza coincidencia y no se usan columnas del parent, el planner puede eliminar ciertos joins en casos compatibles.
No dependas de esto sin revisar plan. Constraints también informan optimización, además de integridad.
NULL = NULL no es TRUE. Si el dominio quiere que dos NULL coincidan:
ON a.code IS NOT DISTINCT FROM b.codePero puede cambiar uso de índices y semántica. Normalmente keys de relación deberían ser NOT NULL.
Objetivo:
WITH totals AS (
SELECT
order_id,
sum(quantity * unit_price) AS total_amount
FROM order_items
GROUP BY order_id
)
SELECT
o.id,
c.name AS customer_name,
d.name AS delivery_name,
t.total_amount
FROM orders o
JOIN customers c
ON c.tenant_id = o.tenant_id
AND c.id = o.customer_id
LEFT JOIN delivery_staff d
ON d.tenant_id = o.tenant_id
AND d.id = o.delivery_staff_id
LEFT JOIN totals t
ON t.order_id = o.id;Cardinalidad esperada:
INNER elimina filas sin referencia; LEFT las conserva.
Sin UNIQUE, una relación esperada 1:1 se multiplica.
Un join al customer actual puede cambiar reportes históricos. Usa snapshots cuando corresponda.
Filtrar deleted_at en WHERE u ON cambia si parent eliminado debe conservarse como NULL o eliminar la fila.
Policies se aplican por tabla y pueden hacer que una coincidencia “exista” físicamente pero sea invisible al rol.
El planner puede hacer partition pruning antes del join si predicados permiten.
Los diagramas de Venn no explican multiplicación ni keys.
Elimina filas no coincidentes.
Añade trabajo y puede ocultar cardinalidad.
Infla métricas.
Une por id y olvida tenant o tipo.
Cambia con el schema.
EXPLAIN (ANALYZE, BUFFERS).(product_id, valid_from DESC, id DESC).Agregaciones y GROUP BY reduce conjuntos a métricas sin perder de vista NULL, grupos vacíos, joins y precisión.