PostgreSQL
Operaciones de conjuntos UNION, INTERSECT y EXCEPT, diferencias entre ALL y eliminación de duplicados y requisitos de compatibilidad entre columnas.
Las operaciones de conjuntos combinan resultados completos. No relacionan una fila con otra como un JOIN: alinean columnas compatibles y deciden qué filas conservar, eliminar o comparar.
UNION, INTERSECT y EXCEPT trabajan con dos o más resultados que comparten la misma forma lógica:
resultado A: columna 1, columna 2, ...
resultado B: columna 1, columna 2, ...
↓
operación de conjuntos
↓
resultado combinadoLa compatibilidad se determina por posición, no por nombre. La primera columna de una rama se compara o combina con la primera de la otra, aunque sus aliases sean distintos.
La diferencia fundamental frente a un join es:
JOIN
→ combina columnas horizontalmente a partir de coincidencias entre filas
SET OPERATION
→ combina filas verticalmente a partir de resultados compatiblesSin operaciones de conjuntos, tareas como unificar historiales, comparar snapshots o encontrar registros faltantes suelen terminar en lógica de aplicación, tablas temporales innecesarias o joins difíciles de leer.
Ejemplos reales:
Las ramas deben devolver:
Ejemplo correcto:
SELECT
customer_id,
created_at,
'order'::text AS source
FROM orders
UNION ALL
SELECT
customer_id,
created_at,
'payment'::text AS source
FROM payments;Los nombres finales suelen provenir de la primera rama. El alias de la segunda no cambia el contrato de salida.
Ejemplo engañoso:
SELECT id, created_at FROM orders
UNION ALL
SELECT created_at, id FROM payments;Aunque existan casts posibles, la semántica está invertida. PostgreSQL solo observa posiciones y tipos, no la intención del dominio.
UNION ALL concatena filas y conserva duplicados:
SELECT email FROM customers_2025
UNION ALL
SELECT email FROM customers_2026;Flujo conceptual:
Es la opción correcta cuando:
UNION aplica semántica distinct al resultado combinado:
SELECT customer_id FROM orders
UNION
SELECT customer_id FROM payments;Esto no significa que PostgreSQL “ignore duplicados gratis”. Necesita comparar filas completas, normalmente mediante sort o hash.
La igualdad considera todas las columnas del resultado. Si añades source, las filas dejan de ser duplicadas:
SELECT customer_id, 'order' AS source FROM orders
UNION
SELECT customer_id, 'payment' AS source FROM payments;Aunque el customer_id sea el mismo, source es distinto.
INTERSECT conserva filas presentes en ambos resultados:
SELECT customer_id
FROM orders
WHERE status = 'paid'
INTERSECT
SELECT customer_id
FROM support_tickets
WHERE priority = 'high';El resultado contiene customers que cumplen ambas condiciones.
INTERSECT ALL conserva multiplicidades. Si una fila aparece tres veces a la izquierda y dos a la derecha, aparece dos veces en el resultado. Su uso es menos común, pero la diferencia importa cuando los duplicados representan ocurrencias reales.
EXCEPT devuelve filas del primer resultado que no aparecen en el segundo:
SELECT id
FROM products
WHERE active
EXCEPT
SELECT product_id
FROM inventory
WHERE branch_id = $1;Esto encuentra productos activos sin fila de inventario en una branch.
El orden de las ramas importa:
A EXCEPT B ≠ B EXCEPT AEXCEPT ALL resta multiplicidades, no solo presencia.
En operaciones de conjuntos con deduplicación, PostgreSQL trata filas con NULL en las mismas posiciones como duplicadas para este propósito.
Ejemplo:
SELECT NULL::integer
UNION
SELECT NULL::integer;produce una sola fila.
Esto difiere de una comparación ordinaria NULL = NULL, que produce UNKNOWN. Las reglas de distinctness del conjunto son específicas.
El ORDER BY escrito al final ordena el resultado completo:
SELECT id, created_at FROM orders
UNION ALL
SELECT id, created_at FROM archived_orders
ORDER BY created_at DESC, id DESC
LIMIT 100;Para limitar una rama antes de combinar, usa paréntesis:
(
SELECT id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 50
)
UNION ALL
(
SELECT id, created_at
FROM archived_orders
ORDER BY created_at DESC
LIMIT 50
)
ORDER BY created_at DESC
LIMIT 50;Cada rama obtiene candidatos; el orden final decide los 50 globales. Sin el último ORDER BY, no existe contrato de orden.
Úsalo cuando quieres apilar resultados con la misma estructura.
Úsalo cuando necesitas columnas de filas relacionadas.
Úsalo cuando solo preguntas si existe una coincidencia.
Úsalo cuando expresas diferencia entre dos resultados completos.
Ejemplo equivalente de ausencia:
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
);NOT EXISTS puede ser más natural cuando la condición depende de varias columnas correlacionadas. EXCEPT es expresivo cuando ya tienes dos conjuntos completos comparables.
PostgreSQL necesita resolver un tipo común:
SELECT 1::integer
UNION ALL
SELECT 2::bigint;El resultado puede promoverse a bigint.
Con tipos ambiguos, añade casts explícitos:
SELECT NULL::uuid AS public_id
UNION ALL
SELECT public_id FROM archived_orders;Esto evita que una rama NULL o literal desconocido produzca resolución inesperada.
Costes principales:
UNION, INTERSECT y EXCEPT sin ALL.UNION ALL suele ser más barato, pero no debes sustituir UNION si el contrato exige unicidad.
Usa EXPLAIN (ANALYZE, BUFFERS) con datos representativos para observar:
Requisito: mostrar movimientos de inventario y cambios de precio en una sola secuencia.
SELECT
occurred_at,
product_id,
'inventory'::text AS event_type,
jsonb_build_object(
'delta', quantity_delta,
'reason', reason
) AS payload
FROM inventory_movements
WHERE branch_id = $1
UNION ALL
SELECT
changed_at AS occurred_at,
product_id,
'price'::text AS event_type,
jsonb_build_object(
'old_price', old_price,
'new_price', new_price
) AS payload
FROM product_price_changes
WHERE branch_id = $1
ORDER BY occurred_at DESC, product_id DESC
LIMIT 100;El contrato común es:
occurred_at + product_id + event_type + payloadCada fuente adapta sus columnas a ese contrato. UNION ALL conserva eventos iguales si realmente ocurrieron dos veces.
La operación continúa con las demás. No es un error.
La sentencia falla durante análisis. Usa casts o corrige el contrato.
UNION puede borrar información. Si dos eventos idénticos ocurrieron realmente, usa UNION ALL y conserva un ID de evento.
La deduplicación puede usar mucha memoria o disco. Revisa si la unicidad puede garantizarse antes o si ALL es correcto.
Comparar texto para deduplicación depende de reglas del tipo y collation. Cambios de collation pueden afectar orden y comparaciones.
Limitar demasiado cada rama puede excluir filas que deberían aparecer en el top global. Define un margen o consulta diferente.
Elimina duplicados y añade coste aunque no exista necesidad.
Entrega repetidos que el consumidor no espera.
Se intentan alinear columnas de entidades que realmente necesitan relacionarse.
El nombre final proviene de la primera rama.
No garantiza el resultado global.
Los tipos coinciden, pero la primera columna significa una cosa diferente en cada rama.
ALL cambia multiplicidad.ALL conserva multiplicidad y evita deduplicación.UNION compara filas completas.EXCEPT depende del orden.ORDER BY final controla el resultado completo.source puede cambiar el resultado de UNION?EXCEPT es más expresivo que un LEFT JOIN?INTERSECT e INTERSECT ALL?ORDER BY dentro de cada rama no garantiza el orden global?UNION para eventos?source cambia.ALL conserva la multiplicidad mínima de ambas ramas.ORDER BY final establece contrato.Window functions calcula rankings, acumulados y comparaciones sin perder el detalle de cada fila.