Las operaciones de conjuntos apilan filas y las comparan enteras

La idea

Un JOIN agrega columnas: toma una fila de una tabla y le adjunta la fila coincidente de otra. Una operación de conjuntos agrega filas. Ejecuta dos consultas y combina sus resultados como conjuntos de filas, un resultado apilado debajo del otro.

Hay cuatro. UNION ALL apila y conserva todo. UNION apila y quita las filas repetidas. INTERSECT conserva solo las filas que están en ambos resultados. EXCEPT conserva las filas del primer resultado que no están en el segundo.

Por qué existe

Los datos reales sobre un mismo tema viven en varios lugares con la misma forma. Los pedidos están en orders, las devoluciones en returns, las sesiones en web_sessions: tres tablas distintas, pero cada una se puede reducir a "una fecha, un tipo de evento, un monto". Apilarlas da una sola tabla de actividad, algo que ningún join puede construir, porque no hay una clave que conecte una devolución con una sesión.

Las operaciones de conjuntos también responden preguntas de solapamiento, que con joins quedan torpes. "¿Quién compró en ambos años?" "¿Quién compró el año pasado y no este?" Son afirmaciones sobre listas de clientes, y una operación de conjuntos las dice tan directamente como la pregunta.

Cómo funciona por dentro

Las filas se comparan enteras y las columnas coinciden por posición

La base de datos toma las columnas del primer resultado y alinea debajo las columnas del segundo, por posición, no por nombre. La columna 1 va con la columna 1. Las reglas salen directamente de ahí:

• ambas consultas deben devolver el mismo número de columnas;
• los tipos en cada posición deben ser compatibles (un número debajo de una fecha es un error, y un número debajo de un texto es una conversión silenciosa, así que revísalo);
• los nombres de las columnas vienen de la primera consulta;
• ORDER BY va una sola vez al final y se aplica a todo el resultado combinado.

Si la segunda consulta selecciona (order_id, customer_id) y la primera selecciona (customer_id, order_id), nada se queja y el resultado es un sinsentido: los ids quedan en las columnas equivocadas. Pon una columna de etiqueta en cada rama ('order' AS kind, 'return' AS kind) para que una tabla apilada diga de dónde viene cada fila.

UNION quita duplicados, y eso tiene un costo

UNION es UNION ALL seguido de un paso distinto. De ahí salen dos cosas. Es más lento en resultados grandes, porque la base de datos tiene que ordenar o aplicar hash a cada fila. Y puede borrar filas que querías conservar.

Dos reembolsos legítimos con la misma fecha y monto son idénticos como filas seleccionadas, así que UNION los fusiona y el total baja. Si estás sumando un monto, UNION ALL es la opción segura, y recurres a UNION solo cuando de verdad quieres una lista sin repeticiones.

INTERSECT y EXCEPT

INTERSECT y EXCEPT también comparan filas enteras, y ambos quitan duplicados. Úsalos para preguntas sobre personas o productos.

Son los bloques de un informe de estado de clientes. Los clientes retenidos están entre los compradores de este año y los del año pasado. Los clientes nuevos están entre los de este año y en ningún periodo anterior. Los clientes inactivos están entre los del año pasado y no entre los de este. Los clientes reactivados compraron este año, no el pasado, pero sí compraron antes. Cada grupo es una operación de conjuntos, y un UNION ALL final con una etiqueta los pone en una sola tabla con el monto que gastó cada grupo.

NULL se compara como igual aquí

En un WHERE, NULL = NULL es desconocido. En una operación de conjuntos, dos NULL en la misma posición cuentan como el mismo valor, así que los duplicados que contienen NULL se eliminan como se espera. Es el único lugar de SQL donde los NULL se comportan como valores normales.

Tipos y etiquetas en la práctica

Cuando las dos ramas vienen de tablas distintas, dales la misma forma a mano. Convierte una fecha a DATE en ambas, escribe 0 o CAST(NULL AS DECIMAL(10,2)) donde una rama no tenga monto, y mantén el orden de las columnas idéntico. Un poco de tedio aquí ahorra una tarde después.

Alternativas

INTERSECT se puede escribir como un INNER JOIN o WHERE … IN, y EXCEPT como WHERE NOT EXISTS o un LEFT JOIN … IS NULL. Dan la misma respuesta cuando la clave es única. La operación de conjuntos suele ser más corta y dice lo que quieres decir, pero un join te permite traer columnas extra como el gasto.

Revisar un resultado apilado

Las operaciones de conjuntos tienen una aritmética ordenada que puedes usar como prueba. UNION ALL devuelve exactamente el número de filas del primer resultado más el del segundo: 3 + 1 = 4 en el diagrama. UNION devuelve como mucho esa cantidad, y la diferencia te dice cuántos duplicados quitó. Para los grupos de clientes, retenidos más inactivos deben sumar los compradores del año pasado, porque cada uno de ellos volvió o no volvió. Si los números no cuadran, una de las ramas usa otra definición de "compró", por ejemplo una excluye los pedidos cancelados y la otra no. Arregla la definición en un CTE compartido para que ambas ramas lean de la misma lista.

Errores comunes

"UNION es el normal, UNION ALL es el especial"

Al revés. UNION ALL hace exactamente lo que sugiere apilar y es más rápido. UNION agrega un paso de eliminación de duplicados para el que debes tener una razón. Cuando sumes un número, usa UNION ALL por defecto.

"Las columnas se emparejan por nombre"

Por posición. Nada comprueba que las columnas de tu segunda consulta signifiquen lo mismo, solo que haya el mismo número con tipos compatibles. Lee las dos listas del SELECT una junto a otra antes de confiar en un resultado apilado.

"EXCEPT es simétrico, como INTERSECT"

A EXCEPT B y B EXCEPT A son preguntas distintas: clientes inactivos frente a clientes nuevos. Decide siempre cuál es el primer resultado y dilo en un comentario.