Curso SQL · Northwind
Tema 25 de 64
Bloque 5 · Combinar tablas PostgreSQL 17 northwind

UNION, INTERSECT y EXCEPT

Requiereanti-join-y-semi-join

1. Qué es

Un JOIN pega tablas de lado: añade columnas. Los operadores de conjuntos las pegan de arriba abajo: añaden filas.

JOIN:   [A][B]  →  más columnas
UNION:   [A]
         [B]    →  más filas
Operador Devuelve
UNION Filas de A y de B, sin repetidos
UNION ALL Filas de A y de B, tal cual
INTERSECT Solo las que están en las dos
EXCEPT Las de A que no están en B

INTERSECT y EXCEPT también eliminan duplicados salvo que añadas ALL.

2. Las reglas

Las tres son innegociables y las tres se olvidan:

  1. Mismo número de columnas en todas las ramas.
  2. Tipos compatibles, columna por columna, en el mismo orden.
  3. Los nombres los pone la primera rama. Los alias de las demás se ignoran.

Y dos de sintaxis:

  1. El ORDER BY va al final, una sola vez, y afecta al resultado completo.
  2. Se evalúan de arriba abajo, con INTERSECT antes que UNION y EXCEPT. Con tres ramas o más, usa paréntesis.
SELECT a, b FROM t1
UNION ALL
SELECT a, b FROM t2
ORDER BY a;

3. Ejemplos sobre Northwind

UNION contra UNION ALL — la diferencia importa:

SELECT count(*) FROM (SELECT country FROM customers UNION     SELECT country FROM suppliers) t;  --  25
SELECT count(*) FROM (SELECT country FROM customers UNION ALL SELECT country FROM suppliers) t;  -- 120

25 contra 120. UNION ALL apila las 91 filas de clientes y las 29 de proveedores. UNION deduplica el resultado entero y deja los 25 países distintos.

Y hay un detalle que se pasa por alto: UNION no solo quita los duplicados entre ramas, también los de dentro de cada rama:

SELECT count(*) FROM (SELECT customer_id FROM orders UNION     SELECT customer_id FROM orders) t;  --   89
SELECT count(*) FROM (SELECT customer_id FROM orders UNION ALL SELECT customer_id FROM orders) t;  -- 1660

La misma consulta unida consigo misma da 89, no 1.660. UNION es UNION ALL + DISTINCT sobre todo.

Eso se ve en el plan:

-- UNION                          -- UNION ALL
 HashAggregate                     Append
   Group Key: country                ->  Seq Scan on customers
   ->  Append                        ->  Seq Scan on suppliers
         ->  Seq Scan on customers
         ->  Seq Scan on suppliers

UNION ALL es solo Append: pegar y ya. UNION añade un HashAggregate encima, que cuesta memoria y tiempo.

Usa UNION ALL por defecto. UNION solo cuando de verdad puede haber duplicados y te molestan. Es al revés de lo que hace todo el mundo: se escribe UNION por costumbre y se paga una deduplicación que nadie pidió.

3.1 El uso más común: etiquetar el origen

SELECT company_name, city, 'cliente'   AS tipo FROM customers WHERE city = 'London'
UNION ALL
SELECT company_name, city, 'proveedor'      FROM suppliers WHERE city = 'London'
ORDER BY tipo, company_name;

     company_name      |  city  |   tipo
-----------------------+--------+-----------
 Around the Horn       | London | cliente
 B's Beverages         | London | cliente
 Consolidated Holdings | London | cliente
 Eastern Connection    | London | cliente
 North/South           | London | cliente
 Seven Seas Imports    | London | cliente
 Exotic Liquids        | London | proveedor

La columna tipo es literal, inventada en el SELECT, y es lo que hace útil el resultado: sin ella, siete empresas de Londres sin saber cuál es cuál. Cuando unas dos tablas distintas, etiqueta siempre el origen.

Fíjate también en que la segunda rama no lleva alias: sería inútil. Los nombres de columna los fija la primera.

3.2 INTERSECT — lo que está en los dos sitios

SELECT count(*) FROM (SELECT country FROM customers INTERSECT SELECT country FROM suppliers) t;  -- 12

12 países donde hay clientes y proveedores. Es la misma pregunta que el FULL JOIN de left-right-y-full-join, respondida en dos líneas — porque aquí solo quiero la columna que comparo, no columnas de los dos lados.

3.3 EXCEPT — lo que está en uno y no en el otro

SELECT country FROM customers EXCEPT SELECT country FROM suppliers ORDER BY 1;

   country
-------------
 Argentina
 Austria
 Belgium
 Ireland
 Mexico
 Poland
 Portugal
 Switzerland
 Venezuela

9 países donde vendemos y no compramos. Y al revés:

SELECT country FROM suppliers EXCEPT SELECT country FROM customers ORDER BY 1;

 Australia
 Japan
 Netherlands
 Singapore

4 países donde compramos y no vendemos. 12 + 9 + 4 = 25. ✓

EXCEPT no es simétrico. A EXCEPT B y B EXCEPT A son preguntas distintas y hay que hacer las dos. Es el error de lectura más común del operador.

3.4 Los nulos aquí sí son iguales

SELECT region FROM customers INTERSECT SELECT region FROM suppliers ORDER BY 1 NULLS LAST;

 region
--------
 OR
 Québec

Tres filas: OR, Québec y una vacía. Esa última es NULL, y está ahí porque las dos tablas tienen regiones nulas.

Los operadores de conjuntos tratan NULL = NULL como cierto, igual que GROUP BY y DISTINCT, y al revés que = en un WHERE o en un ON (null-y-la-logica-de-tres-valores). Es coherente —para comparar filas, dos ausencias son la misma ausencia— pero hay que saberlo: un EXCEPT puede eliminar una fila por coincidir en un NULL.

3.5 Añadir una fila de total

SELECT extract(year FROM order_date)::int AS anio, count(*) FROM orders GROUP BY 1
UNION ALL
SELECT NULL, count(*) FROM orders
ORDER BY 1 NULLS LAST;

 anio | count
------+-------
 1996 |   152
 1997 |   408
 1998 |   270
      |   830

Funciona, y es exactamente lo que ROLLUP hace mejor (agregacion-avanzada): aquí la tabla orders se lee dos veces; con ROLLUP, una. Cuando veas un UNION ALL con un total pegado abajo, casi siempre es un ROLLUP sin escribir.

4. Errores comunes

  1. Distinto número de columnas. sql SELECT country, city FROM customers UNION SELECT country FROM suppliers; ERROR: each UNION query must have the same number of columns Este avisa.

  2. Columnas en distinto orden — este NO avisa. sql SELECT country FROM customers UNION SELECT company_name FROM suppliers; Devuelve 50 filas mezclando países y nombres de empresa bajo el título country. Los dos son varchar, así que PostgreSQL no tiene nada que objetar. El motor comprueba los tipos, no el significado.

Cuando los tipos sí chocan, sí avisa: sql SELECT 1 UNION SELECT 'a'; ERROR: invalid input syntax for type integer: "a"

  1. ORDER BY en una rama intermedia. sql SELECT country FROM customers ORDER BY country UNION SELECT country FROM suppliers; ERROR: syntax error at or near "UNION" Va al final, una sola vez.

  2. UNION por costumbre cuando quieres UNION ALL. Deduplicación silenciosa: si dos filas legítimas eran idénticas, pierdes una. En un UNION de importes de dos sucursales, dos ventas del mismo importe se convierten en una.

  3. Esperar que EXCEPT sea simétrico.

  4. Ordenar por una columna que no está en el SELECT. En un UNION no se puede: el ORDER BY solo ve las columnas del resultado.

  5. Usar UNION para lo que es un JOIN. Si necesitas columnas de las dos tablas en la misma fila, es un JOIN. UNION apila; no cruza.

5. Diferencias con SQL Server

PostgreSQL SQL Server
UNION / UNION ALL ✅ ✅ Idéntico
INTERSECT / EXCEPT ✅ ✅ Idéntico
INTERSECT ALL / EXCEPT ALL ✅ ❌ No existen
MINUS (nombre alternativo de EXCEPT) ❌ ❌ (es de Oracle)

INTERSECT ALL y EXCEPT ALL son la única diferencia, y conservan las repeticiones contando ocurrencias: si A tiene un valor 3 veces y B 1, EXCEPT ALL lo devuelve 2 veces. Se usan poco; si el código tiene que valer para los dos motores, evítalos.

Si te encuentras MINUS, es Oracle: se traduce a EXCEPT tal cual.

6. Ejercicios

  1. Una lista única de todas las ciudades donde hay clientes o proveedores.
  2. Un directorio de contactos: nombre de contacto, empresa, teléfono y tipo (cliente / proveedor), ordenado por empresa.
  3. Las ciudades que tienen clientes pero ningún proveedor.
  4. Los ids de producto que aparecen en products pero nunca en order_details, resuelto con un operador de conjuntos, y luego con NOT EXISTS. ¿Cuál prefieres?
  5. Explica por qué esta consulta devuelve menos filas de las esperadas y cómo lo arreglarías: sql SELECT freight FROM orders WHERE ship_country = 'Brazil' UNION SELECT freight FROM orders WHERE ship_country = 'France';

7. Pregunta de negocio

Administración quiere un único listado de "todas las empresas con las que trabajamos" —clientes y proveedores— con nombre, contacto, teléfono, ciudad, país y de qué tipo es cada una. Y avisan de que hay empresas que podrían ser las dos cosas.

? RespuestaRespóndela antes de abrir
WITH todas AS (
    SELECT company_name, contact_name, phone, city, country, 'cliente'   AS tipo
    FROM customers
    UNION ALL
    SELECT company_name, contact_name, phone, city, country, 'proveedor'
    FROM suppliers
)
SELECT company_name,
       string_agg(DISTINCT tipo, ' + ' ORDER BY tipo) AS tipos,
       min(contact_name) AS contacto,
       min(phone)        AS telefono,
       min(city)         AS ciudad,
       min(country)      AS pais
FROM todas
GROUP BY company_name
ORDER BY company_name;

Devuelve 120 filas, todas con tipos = un solo valor: ninguna empresa es cliente y proveedor a la vez.

Y ahí está lo que había que entregar, que no es la tabla:

  1. UNION ALL, no UNION. Con UNION, una empresa que fuera las dos cosas produciría dos filas distintas (tipo es distinto), así que ni siquiera deduplicaría — pero sí borraría dos contactos homónimos legítimos. UNION ALL no toca nada y el agrupado posterior decide.
  2. El GROUP BY company_name con string_agg es lo que responde al aviso. Si una empresa apareciera en las dos tablas, saldría una fila con cliente + proveedor. Es la única forma de que el listado conteste a la pregunta que hicieron en lugar de dejársela al lector.
  3. Comparar por company_name es frágil, y hay que decirlo. Exotic Liquids y Exotic Liquids Ltd. son la misma empresa y este listado las da por distintas. La igualdad de texto no es identidad de empresa (cross-join-y-self-join sección 8). Si Administración va a usar esto para consolidar proveedores, hace falta revisión manual o comparación por similitud.
  4. Los min() son un parche consciente. Si una empresa saliera en las dos tablas con teléfonos distintos, min() elige uno arbitrariamente. Como aquí ninguna se repite, no afecta a ninguna fila — pero es una bomba dormida si mañana entra una empresa que sea las dos cosas. Lo honesto es decirlo al entregar, no descubrirlo entonces.
✓ SolucionesResuélvelos antes de abrir

1.

SELECT city FROM customers
UNION
SELECT city FROM suppliers
ORDER BY city;

93 ciudades distintas. Aquí UNION (sin ALL) es lo correcto: la pregunta pide una lista única.

2.

SELECT contact_name, company_name, phone, 'cliente'   AS tipo FROM customers
UNION ALL
SELECT contact_name, company_name, phone, 'proveedor'      FROM suppliers
ORDER BY company_name;

120 filas = 91 + 29. UNION ALL a propósito: si dos personas distintas se llaman igual, quiero las dos. Un UNION habría borrado una en silencio.

3.

SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers
ORDER BY city;

64 ciudades con clientes y sin proveedores.

4.

-- Con EXCEPT
SELECT product_id FROM products
EXCEPT
SELECT product_id FROM order_details;

-- Con NOT EXISTS
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_details od WHERE od.product_id = p.product_id);

Las dos devuelven 0 filas: todos los productos se han vendido.

Prefiero NOT EXISTS, por una razón concreta: EXCEPT solo puede devolver la columna que compara. En cuanto quieras también el nombre del producto, EXCEPT deja de servir y hay que envolverlo en otra consulta. NOT EXISTS filtra sin limitar lo que seleccionas.

EXCEPT gana cuando comparas filas completas — por ejemplo, comprobar que dos tablas tras una migración son idénticas: SELECT * FROM origen EXCEPT SELECT * FROM destino. Eso con NOT EXISTS sería columna por columna, con los nulos dando guerra.

5. Porque UNION deduplica, y freight es un número que se repite muchísimo entre pedidos. Dos pedidos —uno de Brasil y otro de Francia— con el mismo flete se convierten en una sola fila.

SELECT count(*) FROM orders WHERE ship_country IN ('Brazil','France');   -- 160
SELECT count(*) FROM (SELECT freight FROM orders WHERE ship_country='Brazil'
                      UNION
                      SELECT freight FROM orders WHERE ship_country='France') t;   -- 158

160 contra 158: dos filas desaparecidas. Y ese es justo el tamaño de error que hace daño — no faltan 50 filas que alguien notaría, faltan dos. Si esos fletes se van a sumar, el total sale corto y nadie lo revisa.

El arreglo es UNION ALL. Aunque lo correcto de verdad es no usar operadores de conjuntos para esto:

SELECT freight FROM orders WHERE ship_country IN ('Brazil', 'France');

Una sola pasada por la tabla en vez de dos. Cuando las dos ramas de un UNION leen la misma tabla y solo cambian en el WHERE, casi siempre es un OR o un IN escrito de la forma larga.