Anti-joins para detectar qué cambió

Descripción

Las lecciones anteriores de este módulo resolvieron dos preguntas con JOINs normales: "¿cuál versión de la dimensión le corresponde a esta venta?" y, dentro de un lote, "¿cuál copia de esta orden reenviada conservo?". Esta lección resuelve una tercera pregunta, con una herramienta que todavía no usaste: "¿qué filas no tienen ninguna correspondencia?" — ya sea porque una venta no cae dentro de ningún rango de vigencia conocido, o porque una fila de un catálogo nuevo no coincide con nada de lo que ya está cargado. DuckDB resuelve esto con dos tipos de JOIN nativos, ANTI JOIN y SEMI JOIN, que evitan la subconsulta o el LEFT JOIN ... WHERE ... IS NULL que otros motores necesitarían.

Conexión con el módulo. Esta lección retoma, directamente, el problema de la lección 4 —una orden fuera de la cobertura de dim_product_scd— y le da una herramienta de detección explícita, en vez de descubrirlo por accidente. También resuelve un problema nuevo: antes de correr el MERGE INTO que el módulo 4 enseñó, ¿cómo sabes, con una consulta simple, exactamente qué filas de un catálogo nuevo representan un cambio real? ANTI JOIN responde ambas preguntas con la misma forma de consulta.

Una analogía: la lista de invitados que nunca llegaron

Imagina una fiesta con una lista de invitados confirmados y una lista de personas que efectivamente llegaron. Si quieres saber quién confirmó pero nunca llegó, no necesitas revisar, uno por uno, si cada nombre de la lista de confirmados aparece en la lista de asistentes reales y anotar los que no —eso es exactamente lo que un LEFT JOIN seguido de WHERE ... IS NULL hace, mecánicamente—. Un anfitrión con experiencia simplemente pide "la lista de los que confirmaron y no llegaron" — una pregunta directa, sin rodeos. ANTI JOIN es, literalmente, esa pregunta directa en SQL: "dame las filas de la izquierda que no tienen ninguna coincidencia en la derecha", sin construir el emparejamiento completo primero para después descartarlo.

Ejemplo trabajado: dos escenarios, dos usos de ANTI JOIN

Escenario 1 — una orden sin ninguna versión de dimensión que la cubra

Retoma dim_product_scd, completa, tal como la dejó el módulo 4. Agrega una orden de demostración, fechada antes de que exista cualquier versión de dim_product_scd — el catálogo de Kiosko, en esta guía, empieza a existir el 2026-08-01; una venta fechada antes de esa fecha no tiene ninguna fila de dimensión que la cubra, sin importar qué tan correcto esté escrito el JOIN.

# antijoin_demo.py
# (dim_product_scd ya cargada, como en las lecciones anteriores)
import duckdb

con.execute("""
    CREATE TABLE orphan_order (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP
    )
""")
con.execute("INSERT INTO orphan_order VALUES ('ORD-9001', 'S02', 'P002', 1, 1.20, 1.20, '2026-07-30T09:00:00')")

print("=== ANTI JOIN: encuentra la orden sin ninguna version de dimension que la cubra ===")
print(con.sql("""
    SELECT o.order_id, o.order_ts
    FROM orphan_order o
    ANTI JOIN dim_product_scd d
        ON o.product_id = d.product_id
       AND o.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
"""))

=== ANTI JOIN: encuentra la orden sin ninguna version de dimension que la cubra ===
┌──────────┬─────────────────────┐
│ order_id │      order_ts       │
│ varchar  │      timestamp      │
├──────────┼─────────────────────┤
│ ORD-9001 │ 2026-07-30 09:00:00 │
└──────────┴─────────────────────┘

ANTI JOIN dim_product_scd d ON ... devuelve, directamente, las filas de orphan_order que no encontraron ninguna fila de dim_product_scd cumpliendo la condición — en este caso, una sola: ORD-9001, fechada el 30 de julio, antes de que cualquier versión de cualquier producto exista todavía. Confírmalo:

print("\n=== Por que: dim_product_scd solo cubre desde esta fecha en adelante ===")
print(con.sql("SELECT MIN(valid_from) AS earliest_coverage FROM dim_product_scd"))

=== Por que: dim_product_scd solo cubre desde esta fecha en adelante ===
┌────────────────────┐
│ earliest_coverage  │
│        date        │
├─────────────────────┤
│ 2026-08-01          │
└─────────────────────┘

Y, para que quede claro que este no es un problema de las cuarenta órdenes reales de Kiosko —todas dentro de la semana del 3 al 9 de agosto, todas posteriores al 1 de agosto—, corre el mismo ANTI JOIN contra fact_orders:

print("\n=== Confirmando que fact_orders (las 40 reales) NO tiene huerfanas ===")
print(con.sql("""
    SELECT COUNT(*) AS orphans
    FROM fact_orders f
    ANTI JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
"""))

=== Confirmando que fact_orders (las 40 reales) NO tiene huerfanas ===
┌─────────┐
│ orphans │
│  int64  │
├─────────┤
│       0 │
└─────────┘

Cero — cada una de las cuarenta órdenes reales encuentra cobertura en dim_product_scd. ORD-9001 fue, deliberadamente, un caso de demostración, no un problema real de los datos de Kiosko.

Escenario 2 — qué cambió en un catálogo nuevo, antes de correr el MERGE

Retoma el escenario del ejercicio 2 de la lección 5 del módulo 4: P002 sube de costo otra vez, el 2026-08-25, a 0.72, sin cambiar de categoría. Antes de correr el MERGE INTO que el módulo 4 enseñó, ¿cómo sabrías, con una sola consulta, cuáles filas de ese catálogo nuevo representan un cambio real frente a lo que ya está vigente en dim_product_scd?

con.execute("""
    CREATE TABLE staging_product_v3 (product_id VARCHAR, product_name VARCHAR, category VARCHAR, unit_cost DOUBLE)
""")
con.executemany(
    "INSERT INTO staging_product_v3 VALUES (?, ?, ?, ?)",
    [
        ("P001", "Bottled Water 600ml", "beverages", 0.40),
        ("P002", "Energy Bar", "health-snacks", 0.72),
        ("P003", "Instant Coffee Sachet", "beverages", 0.35),
        ("P004", "Phone Charger Cable", "electronics", 2.10),
    ],
)

print("\n=== ANTI JOIN: que cambio en staging_product_v3, respecto a la version vigente ===")
print(con.sql("""
    SELECT s.product_id, s.category, s.unit_cost
    FROM staging_product_v3 s
    ANTI JOIN dim_product_scd d
        ON s.product_id = d.product_id
       AND d.is_current = true
       AND s.category = d.category
       AND s.unit_cost = d.unit_cost
"""))

=== ANTI JOIN: que cambio en staging_product_v3, respecto a la version vigente ===
┌────────────┬───────────────┬───────────┐
│ product_id │   category    │ unit_cost │
│  varchar   │    varchar    │  double   │
├────────────┼───────────────┼───────────┤
│ P002       │ health-snacks │      0.72 │
└────────────┴───────────────┴───────────┘

Solo P002 — porque P001, P003 y P004 en staging_product_v3 coinciden, exacto, con su versión vigente en dim_product_scd (is_current = true), pero P002 trae unit_cost = 0.72, distinto del 0.68 vigente. ANTI JOIN devuelve, con precisión, exactamente el subconjunto de filas de staging_product_v3 que no tiene una fila idéntica del lado de dim_product_scd. Fíjate en algo importante: este ANTI JOIN no aplicó ningún cambio. Es una consulta de solo lectura, útil como paso de auditoría o alerta —"esto es lo que el próximo MERGE va a modificar"— antes de correr el MERGE INTO de verdad, no un reemplazo de él.

Su complemento, SEMI JOIN, responde la pregunta opuesta —qué no cambió—:

print("\n=== SEMI JOIN: el complemento -- que productos NO cambiaron ===")
print(con.sql("""
    SELECT s.product_id
    FROM staging_product_v3 s
    SEMI JOIN dim_product_scd d
        ON s.product_id = d.product_id
       AND d.is_current = true
       AND s.category = d.category
       AND s.unit_cost = d.unit_cost
    ORDER BY s.product_id
"""))

=== SEMI JOIN: el complemento -- que productos NO cambiaron ===
┌────────────┐
│ product_id │
│  varchar   │
├────────────┤
│ P001       │
│ P003       │
│ P004       │
└────────────┘

ANTI JOIN y SEMI JOIN, sobre exactamente la misma condición, dividen staging_product_v3 en dos mitades que no se superponen: los tres productos que SEMI JOIN confirma sin cambios, y el único que ANTI JOIN marca como distinto. Entre los dos, cubren el 100% de las filas de staging_product_v3 — ninguna fila queda sin clasificar.

Diagrama: qué hace cada uno de los dos JOIN

flowchart TD
    A["staging_product_v3: 4 filas"] --> B{"Condicion: mismo product_id,\nmisma category, mismo unit_cost\nque la version vigente"}
    B -->|"SI encuentra match identico"| C["SEMI JOIN\ndevuelve la fila de staging\n(P001, P003, P004)"]
    B -->|"NO encuentra match identico"| D["ANTI JOIN\ndevuelve la fila de staging\n(P002 -- el unico que cambio)"]

Equivalencia con LEFT JOIN + WHERE (la forma que otros motores necesitarian)
──────────────────────────────────────────────────────────────────────────────
ANTI JOIN  b ON cond   ≡   LEFT JOIN b ON cond   WHERE b.<cualquier_columna_del_join> IS NULL
SEMI JOIN  b ON cond   ≡   WHERE EXISTS (SELECT 1 FROM b WHERE cond)

Profundización: por qué ANTI JOIN y no LEFT JOIN + WHERE IS NULL

La forma LEFT JOIN ... WHERE <columna_derecha> IS NULL funciona, y de hecho es la forma que la lección 4 usó implícitamente al contar matched_rows con LEFT JOIN — pero tiene dos costos que ANTI JOIN evita. El primero es de legibilidad: LEFT JOIN seguido de un WHERE que verifica IS NULL en una columna arbitraria de la tabla derecha no comunica, con el nombre del operador, qué es lo que la consulta está buscando — alguien que lee la consulta tiene que reconstruir la intención a partir de dos cláusulas separadas. ANTI JOIN, en cambio, nombra exactamente la operación en la palabra clave misma.

El segundo costo es más sutil, y tiene que ver con NULL: si la columna que eliges para el WHERE ... IS NULL de un LEFT JOIN pudiera, por alguna razón, ser genuinamente NULL del lado derecho aunque sí hubo MATCH —por ejemplo, si esa columna específica admite valores NULL en los datos reales—, la condición IS NULL daría un falso positivo, marcando como "sin match" una fila que en realidad sí lo tuvo. ANTI JOIN no tiene ese riesgo, porque no depende de examinar una columna específica del lado derecho para inferir la ausencia de coincidencia — evalúa la condición del JOIN directamente, sin ese paso intermedio. La documentación oficial de DuckDB confirma esta relación exacta: ANTI JOIN "provee la misma lógica que el operador NOT IN", y SEMI JOIN "provee la misma lógica que el operador IN" — ambos, sin construir primero el producto completo del JOIN para después filtrarlo.

Vale la pena notar que ANTI JOIN y SEMI JOIN son sintaxis relativamente reciente en DuckDB —introducida en la versión 0.8—, pensada específicamente para hacer explícitas dos operaciones que, antes, solo existían disfrazadas de LEFT JOIN/IS NULL o de subconsultas con EXISTS/NOT EXISTS. El resultado es idéntico en los tres casos; lo que cambia es cuánto tiene que reconstruir quien lee la consulta para entender la intención.

Errores comunes

Usar ANTI JOIN con una condición de igualdad de una sola columna, cuando la comparación real necesita varias. Qué pasa: alguien escribe staging_product_v3 s ANTI JOIN dim_product_scd d ON s.product_id = d.product_id —sin is_current, category ni unit_cost— esperando encontrar "lo que cambió", y el resultado sale vacío, porque todos los product_id de staging_product_v3 sí existen en dim_product_scd, sin importar si sus valores coinciden. Por qué pasa: es fácil pensar en ANTI JOIN como "encuentra lo que no está", sin notar que "no está" depende por completo de qué columnas incluye la condición ON. Cómo detectarlo: si tu ANTI JOIN devuelve cero filas cuando esperabas encontrar cambios, revisa si la condición ON solo compara la llave, sin las columnas de valor que realmente definen "cambió". Cómo corregirlo: para detectar cambios de valor (no solo de existencia), la condición ON de un ANTI JOIN debe incluir la llave y cada columna cuyo valor te importa comparar — exactamente como el segundo escenario de esta lección, que compara product_id, category y unit_cost a la vez.

Olvidar is_current = true al comparar contra una dimensión historizada con ANTI JOIN. Qué pasa: alguien corre el segundo escenario de esta lección sin el filtro d.is_current = true, y el ANTI JOIN se compara contra todas las versiones de dim_product_scd, incluyendo la histórica y cerrada de P002 (snacks/0.60). Por qué pasa: con una dimensión sin historia, no haría falta ese filtro — el problema solo aparece cuando la dimensión, como dim_product_scd, tiene más de una fila por product_id. Cómo detectarlo: si tu ANTI JOIN contra una dimensión historizada devuelve resultados inconsistentes o inesperados para un producto que sabes que tiene más de una versión, sospecha primero de un is_current faltante. Cómo corregirlo: cualquier comparación contra "el estado vigente" de una dimensión SCD-2 —con ANTI JOIN, SEMI JOIN, o un JOIN normal— necesita restringir el lado de la dimensión a is_current = true, exactamente la misma disciplina que el MERGE INTO del módulo 4 ya exigió en su condición ON.

Confundir ANTI JOIN con una forma de "arreglar" los datos. Qué pasa: alguien, después de ver que ANTI JOIN encuentra ORD-9001 como huérfana en el primer escenario, espera que la consulta también la corrija o la descarte de orphan_order de alguna forma. Por qué pasa: ANTI JOIN, igual que cualquier SELECT, se siente como parte de un flujo de "detectar y arreglar", y es fácil olvidar que es puramente de lectura. Cómo detectarlo: si esperas que orphan_order tenga menos filas después de correr el ANTI JOIN, o que dim_product_scd cambie después del ANTI JOIN del segundo escenario, tienes esta confusión. Cómo corregirlo: ANTI JOIN y SEMI JOIN son consultas de solo lectura, exactamente como cualquier otro SELECT — sirven para detectar y reportar, nunca para modificar. La corrección real —insertar una fila de dimensión que cubra el hueco, o aplicar el MERGE INTO sobre el cambio detectado— es un paso separado y explícito, que estas dos lecciones nombran pero no ejecutan sobre los datos reales de Kiosko.

Ejercicios

Ejercicio 1 — Confirma con COUNT(*) que SEMI JOIN y ANTI JOIN juntos cubren exactamente las cuatro filas de staging_product_v3. Escribe una consulta que sume el resultado de ambos y confirme que da 4.

print(con.sql("""
    SELECT
        (SELECT COUNT(*) FROM staging_product_v3 s
         SEMI JOIN dim_product_scd d ON s.product_id = d.product_id AND d.is_current = true
            AND s.category = d.category AND s.unit_cost = d.unit_cost) AS sin_cambios,
        (SELECT COUNT(*) FROM staging_product_v3 s
         ANTI JOIN dim_product_scd d ON s.product_id = d.product_id AND d.is_current = true
            AND s.category = d.category AND s.unit_cost = d.unit_cost) AS con_cambios
"""))

┌─────────────┬─────────────┐
│ sin_cambios │ con_cambios │
│    int64    │    int64    │
├─────────────┼─────────────┤
│           3 │           1 │
└─────────────┴─────────────┘

Ejercicio 2 — Agrega una orden huérfana adicional, para un producto que no existe en el catálogo de Kiosko (P099), y confirma que el ANTI JOIN la encuentra por una razón distinta. Inserta una orden con product_id = 'P099' en orphan_order, y corre de nuevo el ANTI JOIN del primer escenario.

con.execute("INSERT INTO orphan_order VALUES ('ORD-9002', 'S01', 'P099', 1, 1.00, 1.00, '2026-08-05T09:00:00')")
print(con.sql("""
    SELECT o.order_id, o.product_id, o.order_ts
    FROM orphan_order o
    ANTI JOIN dim_product_scd d
        ON o.product_id = d.product_id
       AND o.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
    ORDER BY o.order_id
"""))

┌──────────┬────────────┬─────────────────────┐
│ order_id │ product_id │      order_ts       │
│ varchar  │  varchar   │      timestamp      │
├──────────┼────────────┼─────────────────────┤
│ ORD-9001 │ P002       │ 2026-07-30 09:00:00 │
│ ORD-9002 │ P099       │ 2026-08-05 09:00:00 │
└──────────┴────────────┴─────────────────────┘

Ejercicio 3 — Explica por qué ANTI JOIN nunca puede devolver más filas que la tabla de la izquierda. En 2-3 frases, usando lo que sabes sobre cómo ANTI JOIN evalúa su condición, explica por qué —a diferencia del JOIN sin filtro de la lección 2— nunca hay fan-out con ANTI JOIN ni SEMI JOIN.

Resumen y siguiente paso

Esta lección introdujo ANTI JOIN y SEMI JOIN, dos tipos de JOIN nativos de DuckDB que resuelven "¿qué filas no tienen ninguna coincidencia?" y su opuesto, "¿qué filas sí la tienen?", sin necesitar una subconsulta o un LEFT JOIN/WHERE IS NULL. Los aplicaste a dos escenarios reales: encontrar una orden fuera de la cobertura temporal de dim_product_scd (retomando la lección 4), y detectar, antes de correr cualquier MERGE INTO, exactamente qué fila de un catálogo nuevo representa un cambio real frente a lo que ya está vigente.

Antes de avanzar deberías poder: escribir de memoria la sintaxis de ANTI JOIN/SEMI JOIN y su equivalencia con NOT IN/IN; explicar por qué son operaciones de solo lectura, útiles para detectar y reportar, no para corregir; y aplicar ANTI JOIN para encontrar tanto huérfanos temporales (fecha sin cobertura) como huérfanos estructurales (llave natural inexistente).

La lección 8, el proyecto de cierre del módulo, integra las seis lecciones anteriores sobre el mismo fact_orders y dim_product_scd de siempre: el JOIN correcto contra el roto, la deduplicación de un lote reenviado, y los ANTI JOIN de esta lección, todo en un solo flujo verificado de punta a punta.

Recursos

• DuckDB — documentación de las cláusulas FROM y JOIN, que incluye la sintaxis y semántica exacta de SEMI JOIN y ANTI JOIN usadas en esta lección. duckdb.org/docs/current/sql/query_syntax/from. En inglés.
• Kimball Group — "Late Arriving Dimension" — el caso de "llave natural sin ninguna fila de dimensión" que el segundo caso del ejercicio 2 de esta lección detecta con ANTI JOIN. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/late-arriving-dimension. En inglés.
• DuckDB — documentación oficial del statement MERGE INTO — el proceso que un ANTI JOIN de auditoría, como el del segundo escenario de esta lección, típicamente precede sin reemplazarlo. duckdb.org/docs/lts/sql/statements/merge_into. En inglés.