EXCEPT en SQL: diferencia de conjuntos y el equivalente MINUS de Oracle

EXCEPT devuelve las filas de la primera consulta que no estan en la segunda: es la diferencia de conjuntos en su forma mas pura. Oracle llama a este mismo operador MINUS; la sintaxis y el significado son identicos.

Que hace EXCEPT

Tomemos todos los usuarios y restemos los que estan baneados. Quedan exactamente los user_id presentes en el primer conjunto pero ausentes en el segundo.

SELECT user_id FROM users
EXCEPT
SELECT user_id FROM banned;

Igual que UNION, el operador exige el mismo numero de columnas y tipos compatibles en ambas ramas. La comparacion se hace sobre la fila completa, no solo sobre la primera columna. Un detalle clave: EXCEPT trata NULL como un unico valor, asi que una fila con NULL a la izquierda se cancela con una fila con NULL a la derecha, algo que un = normal nunca hace.

• Los nombres de columna provienen del primer SELECT.
• El resultado no contiene duplicados: EXCEPT aplica un DISTINCT implicito.
• El orden de las filas no esta garantizado, asi que anade ORDER BY al final si lo necesitas.

EXCEPT frente a EXCEPT ALL

El EXCEPT normal colapsa las repeticiones: aunque un valor aparezca diez veces a la izquierda, en el resultado figura como mucho una vez. EXCEPT ALL conserva la multiplicidad: de N apariciones a la izquierda resta M a la derecha y deja max(N - M, 0) copias.

-- DISTINCT difference: each surviving id appears once
SELECT user_id FROM orders
EXCEPT
SELECT user_id FROM orders WHERE status = 'refunded';

-- Multiset difference: per-row counts are subtracted
SELECT user_id FROM orders
EXCEPT ALL
SELECT user_id FROM orders WHERE status = 'refunded';

Trampa: EXCEPT ALL existe en PostgreSQL pero no en MySQL (que no tuvo EXCEPT hasta la 8.0.31), y ClickHouse tiene su propia semantica de duplicados. No supongas que el comportamiento de multiconjunto es el predeterminado; confirma que variante admite realmente tu motor.

EXCEPT frente a NOT EXISTS y LEFT JOIN

La misma diferencia suele escribirse con NOT EXISTS o LEFT JOIN ... IS NULL. No son sinonimos exactos, y la forma en que cada uno maneja NULL y los duplicados decide cual conviene usar.

-- Set difference via NOT EXISTS
SELECT u.user_id
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM banned b WHERE b.user_id = u.user_id
);

-- Same idea via anti-join
SELECT u.user_id
FROM users u
LEFT JOIN banned b ON b.user_id = u.user_id
WHERE b.user_id IS NULL;

Las diferencias clave:

• EXCEPT elimina por si mismo los duplicados de la izquierda; NOT EXISTS y LEFT JOIN los conservan salvo que anadas un DISTINCT explicito.
• EXCEPT compara la fila entera en todas las columnas, mientras que NOT EXISTS solo comprueba las condiciones que escribes en su WHERE.
• NOT EXISTS maneja bien los NULL de la tabla derecha; el clasico NOT IN se rompe con NULL y devuelve vacio en silencio. Para una sola columna esta es la trampa mas comun.
• Cuando hay que reconciliar varias columnas a la vez, EXCEPT se lee mejor: SELECT a, b, c ... EXCEPT SELECT a, b, c ... supera a un NOT EXISTS verboso con tres condiciones.

En rendimiento, NOT EXISTS suele ganar en tablas grandes: el planificador lo convierte en un hash anti join y no materializa por completo ambas ramas solo para deduplicar. EXCEPT, en cambio, primero construye ambos conjuntos y les aplica hash, lo que consume mas memoria.

Reconciliacion mediante la diferencia

El papel practico principal de EXCEPT es la reconciliacion bidireccional. Para detectar desviaciones entre un origen y un destino, calcula la diferencia en ambos sentidos.

-- Rows in source but missing in target
SELECT id, user_id, amount FROM orders_source
EXCEPT
SELECT id, user_id, amount FROM orders_target;

Si la consulta no devuelve nada, el origen esta totalmente contenido en el destino. La comprobacion simetrica intercambia las ramas, o combinas ambas diferencias con UNION ALL mas una etiqueta de direccion. Esto es mucho mas fiable que comparar solo COUNT(*): el mismo numero de filas no garantiza que las filas coincidan.

En resumen: EXCEPT es una forma concisa de expresar una diferencia sobre todo el conjunto de columnas con deduplicacion automatica, ideal para reconciliaciones. Cuando importa la velocidad con grandes volumenes o necesitas conservar duplicados, recurre a NOT EXISTS.