Bloque 5 · Combinar tablas PostgreSQL 17 northwind

Anti-join y semi-join

Este tema no está en las guías al uso con este nombre. Aparece disperso entre EXISTS, IN y NOT IN, sin explicar que son la misma idea ni advertir de la trampa de la sección 4 — que es de las que borran informes enteros en silencio.

1. Qué es

Un JOIN normal trae columnas de la otra tabla. Muchas veces no quieres columnas: quieres saber si existe o no existe.

Nombre	Pregunta	Cómo se escribe
Semi-join	¿Tiene al menos uno?	`EXISTS` / `IN`
Anti-join	¿No tiene ninguno?	`NOT EXISTS`

La propiedad que los define, y por la que merecen un nombre propio:

Un semi-join no multiplica filas. Un cliente con 31 pedidos aparece una vez, no 31. Un JOIN normal daría 31 y habría que arreglarlo con DISTINCT — que es la tirita de distinct-y-duplicados.

2. Sintaxis

-- Semi-join
WHERE EXISTS     (SELECT 1 FROM b WHERE b.clave = a.clave)
WHERE a.clave IN (SELECT clave FROM b)

-- Anti-join
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.clave = a.clave)

El SELECT 1 de dentro no es un truco de rendimiento: EXISTS solo mira si hay filas, nunca sus valores, así que da igual escribir SELECT 1, SELECT * o SELECT 1/0. Se escribe 1 por convención, para que quede claro que no se usa.

3. Semi-join: "los que tienen al menos uno"

SELECT count(*) FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);   -- 89

SELECT count(*) FROM customers c
WHERE c.customer_id IN (SELECT customer_id FROM orders);                     -- 89

SELECT count(*) FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;                              -- 830  ⚠

89, 89 y 830. Las dos primeras responden "¿cuántos clientes han comprado?". La tercera responde "¿cuántos pedidos hay?", aunque parezca que cuenta clientes.

El caso realista — clientes que han comprado el producto 38:

-- Semi-join: 19
SELECT count(*) FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              JOIN order_details od ON od.order_id = o.order_id
              WHERE o.customer_id = c.customer_id AND od.product_id = 38);

-- JOIN normal: 24  ⚠
SELECT count(*) FROM customers c
JOIN orders o        ON o.customer_id = c.customer_id
JOIN order_details od ON od.order_id  = o.order_id
WHERE od.product_id = 38;

19 clientes contra 24 filas. Cinco clientes compraron Côte de Blaye más de una vez, y el JOIN los cuenta una vez por compra. Los dos números son ciertos; solo uno responde a "¿a cuántos clientes les mando la promoción?".

3.1 `EXISTS` contra `IN`

Con datos sin nulos, son equivalentes. Y no solo en el resultado — el plan también:

EXPLAIN (COSTS OFF) SELECT c.customer_id FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

 Hash Join
   Hash Cond: (c.customer_id = o.customer_id)
   ->  Seq Scan on customers c
   ->  Hash
         ->  HashAggregate
               Group Key: o.customer_id
               ->  Seq Scan on orders o

La versión con IN produce el mismo plan, línea por línea. PostgreSQL reconoce las dos formas como el mismo semi-join.

Cuándo usar cada uno:

	`EXISTS`	`IN`
Varias condiciones de correlación	✅ Natural	❌ Incómodo
Lista de valores literales	❌	✅ `IN ('A','B','C')`
Con posibles nulos	✅ Seguro	⚠️ En la forma negada, peligroso

Regla: IN para listas literales, EXISTS para subconsultas.

4. Anti-join: "los que no tienen ninguno" — y la trampa

Las tres formas de escribirlo:

-- A: NOT EXISTS
SELECT c.company_name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- B: LEFT JOIN ... IS NULL
SELECT c.company_name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- C: NOT IN     ⚠️ ver abajo
SELECT c.company_name FROM customers c
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders);

             company_name
--------------------------------------
 FISSA Fabrica Inter. Salchichas S.A.
 Paris spécialités

Aquí las tres dan lo mismo. Y aun así solo una es correcta.

4.1 La trampa de `NOT IN` con `NULL`

Empleados que no dirigen a nadie. La misma pregunta, dos formas:

SELECT count(*) FROM employees e
WHERE e.employee_id NOT IN (SELECT reports_to FROM employees);          -- 0   ❌

SELECT count(*) FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees s WHERE s.reports_to = e.employee_id);  -- 7  ✅

Cero contra siete. NOT IN no da error, no da aviso, y devuelve el resultado más creíble de todos: una lista vacía.

Por qué. reports_to contiene un NULL — Andrew Fuller no reporta a nadie. Así que NOT IN (2, 5, NULL) se expande a:

employee_id <> 2  AND  employee_id <> 5  AND  employee_id <> NULL

Esa última comparación es UNKNOWN para todas las filas, y TRUE AND UNKNOWN = UNKNOWN (null-y-la-logica-de-tres-valores). El WHERE solo deja pasar TRUE. Ninguna fila puede sobrevivir, hagan lo que hagan los datos.

Y esto se ve también en el plan:

-- NOT EXISTS
 Nested Loop Anti Join
   ->  Seq Scan on customers c
   ->  Index Only Scan using idx_orders_customer_id on orders o

-- NOT IN
 Seq Scan on customers c
   Filter: (NOT (ANY (customer_id = ((hashed SubPlan 1).col1))))
   SubPlan 1
     ->  Seq Scan on orders

NOT EXISTS se convierte en un Anti Join de verdad y usa el índice. NOT IN no puede: por la semántica del NULL, el motor está obligado a comprobar la lista entera fila por fila. Es peor resultado y peor plan.

Regla sin excepción: para un anti-join, NOT EXISTS. NOT IN solo es seguro sobre una lista literal (NOT IN ('A','B')) o sobre una columna declarada NOT NULL — y esa declaración puede cambiar mañana sin que nadie revise tus consultas.

4.2 `NOT EXISTS` contra `LEFT JOIN ... IS NULL`

Las dos son correctas. Diferencias:

	`NOT EXISTS`	`LEFT JOIN ... IS NULL`
Intención	Explícita: "no existe"	Hay que deducirla
Riesgo	Ninguno	Elegir mal la columna del `IS NULL`
Plan	`Anti Join`	`Hash Right Join` + `Filter`

El riesgo del LEFT JOIN es real: hay que comprobar IS NULL sobre una columna que nunca sea nula en la tabla derecha. Con o.order_id (la clave primaria) está bien; con o.shipped_date saldrían además los 21 pedidos sin enviar.

NOT EXISTS no tiene esa decisión que tomar. Por eso es el que se recomienda.

4.3 Combinar semi y anti: la pregunta que solo ellos responden

Clientes que compraron en el primer trimestre de 1998 y no han vuelto:

SELECT count(*) FROM customers c
WHERE EXISTS     (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
                                           AND o.order_date >= '1998-01-01')
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
                                           AND o.order_date >= '1998-04-01');

 count
-------
    25

25 clientes en riesgo de fuga. Con JOIN esto no se escribe de forma natural: harían falta dos agregaciones y un HAVING. Con un semi y un anti se lee como la frase en español.

4.4 `EXCEPT`, la cuarta forma

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

Devuelve los mismos 2 clientes. EXCEPT es un anti-join sobre columnas, no sobre tablas: solo sirve cuando lo que quieres devolver es exactamente la columna que comparas. Está en union-intersect-except.

5. Errores comunes

NOT IN con una subconsulta. Cero filas silenciosas. El error del tema.
JOIN normal donde querías un semi-join. Filas multiplicadas: 24 en vez de 19.
Arreglar lo anterior con DISTINCT. Funciona y esconde el problema; con una agregación al lado, la esconde mal.
LEFT JOIN ... WHERE columna IS NULL sobre una columna que puede ser nula de verdad.
Poner columnas de la subconsulta en el SELECT de fuera. No se puede: un semi-join no trae columnas. Si las necesitas, es un JOIN.
NOT EXISTS sin correlacionar. NOT EXISTS (SELECT 1 FROM orders) sin WHERE es cierto o falso para toda la tabla: devuelve todo o nada.
Creer que EXISTS es más lento que IN. Producen el mismo plan.

6. Diferencias con SQL Server

	PostgreSQL	SQL Server
`EXISTS` / `NOT EXISTS`	✅	✅ Idéntico
`IN` / `NOT IN`	✅	✅ Con la misma trampa de `NULL`
`EXCEPT`	✅	✅
`LEFT JOIN ... IS NULL`	✅	✅

Este tema es 100% portable, trampa incluida. NOT IN con nulos se comporta igual de mal en SQL Server, en Oracle y en MySQL: no es una peculiaridad de PostgreSQL, es el estándar SQL.

Detalle de entrevista: "¿Cuál es la diferencia entre NOT IN y NOT EXISTS?" es una de las preguntas más frecuentes que existen. La respuesta completa son tres frases: con nulos en la subconsulta, NOT IN devuelve cero filas; NOT EXISTS funciona bien; y además NOT EXISTS permite al optimizador usar un anti-join.

7. Ejercicios

Los productos que nunca se han vendido. ¿Hay alguno?
Los clientes que han comprado algún producto de la categoría Seafood, sin duplicados.
Los empleados que no han gestionado ningún pedido en 1998.
Los proveedores que no tienen ningún producto descatalogado.
Explica por qué estas dos dan resultados distintos y cuál es la correcta: ```sql SELECT count(*) FROM orders o WHERE o.ship_region NOT IN (SELECT region FROM customers);

SELECT count(*) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.region = o.ship_region); ```

8. Pregunta de negocio

Marketing prepara una campaña de recuperación. Quieren "los clientes que compraban y han dejado de comprar", con su última compra y lo que gastaban. Y avisan: la última vez que pidieron algo así, les llegó una lista vacía y nadie supo por qué.

La última frase te está diciendo exactamente qué salió mal.

? RespuestaRespóndela antes de abrir

"Les llegó una lista vacía y nadie supo por qué" es la descripción exacta de un NOT IN con nulos. Casi con seguridad la consulta anterior era:

-- Lo que probablemente escribieron
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE order_date >= '1998-04-01');

En este Northwind orders.customer_id es NOT NULL —lo confirma \d orders— así que ahí no falla. Pero en la mayoría de bases reales esa columna sí admite nulos: pedidos anónimos, importaciones a medio migrar, ventas de mostrador. Basta un solo pedido sin cliente para que la lista salga vacía. Y sale vacía sin error, así que nadie la cuestiona.

La versión correcta:

SELECT c.customer_id,
       c.company_name,
       c.country,
       max(o.order_date)                                              AS ultima_compra,
       count(DISTINCT o.order_id)                                     AS pedidos_historicos,
       round(sum(od.unit_price * od.quantity * (1 - od.discount)), 2)  AS gasto_historico
FROM customers c
JOIN orders        o  ON o.customer_id = c.customer_id
JOIN order_details od ON od.order_id   = o.order_id
WHERE NOT EXISTS (SELECT 1 FROM orders o2
                  WHERE o2.customer_id = c.customer_id
                    AND o2.order_date >= '1998-04-01')
GROUP BY c.customer_id, c.company_name, c.country
HAVING count(DISTINCT o.order_id) >= 3
ORDER BY gasto_historico DESC;

 customer_id |       company_name       | country |   ultima   | pedidos |  gasto
-------------+--------------------------+---------+------------+---------+----------
 MEREP       | Mère Paillarde           | Canada  | 1997-10-30 |      13 | 28872.19
 BERGS       | Berglunds snabbköp       | Sweden  | 1998-03-04 |      18 | 24927.58
 BLONP       | Blondesddsl père et fils | France  | 1998-01-12 |      11 | 18534.08
 SEVES       | Seven Seas Imports       | UK      | 1998-02-04 |       9 | 16215.33
 ...

El primero de la lista es el caso de libro: Mère Paillarde, 13 pedidos y 28.872 gastados, sin comprar desde octubre de 1997. Seis meses de silencio en un cliente que compraba cada mes.

Las cuatro decisiones que hay detrás:

NOT EXISTS, no NOT IN. Es la corrección del bug que les dejó la lista vacía, y hay que decírselo así: "la consulta anterior no estaba vacía por falta de clientes, estaba vacía por un NULL".
JOIN normal con orders y order_details, y el anti-join solo en el WHERE. Necesito las columnas del histórico —última fecha, gasto— así que ahí sí quiero traer datos. El anti-join es el filtro; el JOIN es la fuente. Mezclarlos es lo que hace que la gente acabe con LEFT JOIN imposibles de leer.
HAVING count(...) >= 3. "Compraban" implica un hábito. Un cliente con un solo pedido en 1996 no ha dejado de comprar: nunca empezó. Sin ese filtro, la campaña de recuperación se dirige a gente que no hay que recuperar.
count(DISTINCT o.order_id) y max(o.order_date). Lo de siempre al unir con order_details: sin el DISTINCT, "pedidos históricos" cuenta líneas (joins-multiples-y-granularidad).

Y lo que hay que decir al entregar: el corte del 1 de abril de 1998 es arbitrario y la base termina el 6 de mayo de 1998. Un cliente que compra cada seis meses y compró en marzo no está perdido: está en su ciclo normal. Sin conocer la frecuencia habitual de cada cliente, "ha dejado de comprar" no se puede afirmar — y eso sí se puede calcular, comparando su intervalo medio entre pedidos con el tiempo transcurrido desde el último (lag-lead-first-last-value).

✓ SolucionesResuélvelos antes de abrir

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);

0 filas: los 77 productos se han vendido alguna vez. Aquí NOT IN también habría funcionado —order_details.product_id es NOT NULL, por la clave primaria compuesta— pero eso hay que verificarlo antes, y NOT EXISTS te ahorra tener que hacerlo.

SELECT c.company_name
FROM customers c
WHERE EXISTS (SELECT 1
              FROM orders o
              JOIN order_details od ON od.order_id  = o.order_id
              JOIN products      p  ON p.product_id = od.product_id
              JOIN categories    cat ON cat.category_id = p.category_id
              WHERE o.customer_id = c.customer_id
                AND cat.category_name = 'Seafood')
ORDER BY c.company_name;

85 clientes de 89. Sin el semi-join —con JOIN y DISTINCT— saldría lo mismo, pero el EXISTS dice lo que quiere decir: no me traigas nada de ahí dentro, solo dime si hay.

SELECT e.employee_id, e.first_name || ' ' || e.last_name AS empleado
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.employee_id = e.employee_id
                    AND o.order_date >= '1998-01-01');

0 filas: los 9 vendieron algo en 1998. Fíjate en que la condición de fecha va dentro del NOT EXISTS. Fuera cambiaría la pregunta por completo.

SELECT s.company_name
FROM suppliers s
WHERE NOT EXISTS (SELECT 1 FROM products p
                  WHERE p.supplier_id = s.supplier_id AND p.discontinued);

20 proveedores de 29 no tienen ningún producto de baja. Los otros 9 tienen al menos uno.

5. Los tres números:

-- A: NOT IN
SELECT count(*) FROM orders o WHERE o.ship_region NOT IN (SELECT region FROM customers);           -- 0

-- B: NOT EXISTS
SELECT count(*) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.region = o.ship_region);  -- 520

-- C: NOT EXISTS + nulos tratados
SELECT count(*) FROM orders o WHERE o.ship_region IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.region = o.ship_region);                        -- 13

0, 520 y 13.

A usa NOT IN sobre customers.region, que tiene 60 nulos: devuelve 0 siempre, pase lo que pase con los datos.

B arregla eso, pero introduce otro problema: orders.ship_region también admite nulos, y una fila con ship_region = NULL nunca encuentra pareja (NULL = cualquier cosa es UNKNOWN). Los 520 son casi todos pedidos sin región de envío, colados como si fueran regiones desconocidas.

C es la correcta: 13 pedidos con una región de envío que ningún cliente tiene como propia.

El ejercicio enseña que NOT EXISTS te salva de un problema, no de los dos: arregla la trampa del NOT IN en la subconsulta, pero los nulos de la tabla externa siguen siendo decisión tuya. Y fíjate en la gravedad relativa: A da un falso vacío, B da un falso lleno de 520. Los dos son creíbles y los dos están mal.