Anti-joins to find what changed

Description

This module's earlier lessons solved two questions with normal JOINs: "which dimension version corresponds to this sale?" and, within a batch, "which copy of this resent order do I keep?" This lesson solves a third question, with a tool you haven't used yet: "which rows have no match at all?" — either because a sale doesn't fall within any known validity range, or because a row from a new catalog doesn't match anything already loaded. DuckDB solves this with two native JOIN types, ANTI JOIN and SEMI JOIN, which avoid the subquery or the LEFT JOIN ... WHERE ... IS NULL other engines would need.

Connection to the module. This lesson picks back up, directly, lesson 4's problem — an order outside dim_product_scd's coverage — and gives it an explicit detection tool, instead of discovering it by accident. It also solves a new problem: before running the MERGE INTO module 4 taught, how do you know, with a simple query, exactly which rows of a new catalog represent a real change? ANTI JOIN answers both questions with the same query shape.

An analogy: the guest list that never showed up

Imagine a party with a list of confirmed guests and a list of people who actually showed up. If you want to know who confirmed but never came, you don't need to check, one by one, whether each name on the confirmed list appears on the actual-attendee list and note the ones that don't — that's exactly what a LEFT JOIN followed by WHERE ... IS NULL does, mechanically. An experienced host simply asks for "the list of people who confirmed and didn't show up" — a direct question, no detours. ANTI JOIN is, literally, that direct question in SQL: "give me the rows on the left that have no match on the right," without building the complete pairing first only to discard it afterward.

Worked example: two scenarios, two uses of ANTI JOIN

Scenario 1 — an order with no dimension version covering it

Pick back up dim_product_scd, complete, as module 4 left it. Add a demo order, dated before any version of dim_product_scd exists — Kiosko's catalog, in this guide, starts existing on 2026-08-01; a sale dated before that date has no dimension row covering it, no matter how correctly the JOIN is written.

# antijoin_demo.py
# (dim_product_scd already loaded, as in earlier lessons)
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: finds the order with no dimension version covering it ===")
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: finds the order with no dimension version covering it ===
┌──────────┬─────────────────────┐
│ order_id │      order_ts       │
│ varchar  │      timestamp      │
├──────────┼─────────────────────┤
│ ORD-9001 │ 2026-07-30 09:00:00 │
└──────────┴─────────────────────┘

ANTI JOIN dim_product_scd d ON ... returns, directly, the orphan_order rows that found no dim_product_scd row satisfying the condition — in this case, one: ORD-9001, dated July 30, before any version of any product exists yet. Confirm it:

print("\n=== Why: dim_product_scd only covers from this date onward ===")
print(con.sql("SELECT MIN(valid_from) AS earliest_coverage FROM dim_product_scd"))

=== Why: dim_product_scd only covers from this date onward ===
┌────────────────────┐
│ earliest_coverage  │
│        date        │
├─────────────────────┤
│ 2026-08-01          │
└─────────────────────┘

And, to make clear this isn't a problem with Kiosko's forty real orders — all within the week of August 3-9, all later than August 1 — run the same ANTI JOIN against fact_orders:

print("\n=== Confirming fact_orders (the real 40) has NO orphans ===")
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')
"""))

=== Confirming fact_orders (the real 40) has NO orphans ===
┌─────────┐
│ orphans │
│  int64  │
├─────────┤
│       0 │
└─────────┘

Zero — every one of the forty real orders finds coverage in dim_product_scd. ORD-9001 was, deliberately, a demonstration case, not a real problem with Kiosko's data.

Scenario 2 — what changed in a new catalog, before running the MERGE

Pick back up the scenario from module 4, lesson 5, exercise 2: P002's cost goes up again, on 2026-08-25, to 0.72, with no category change. Before running the MERGE INTO module 4 taught, how would you know, with a single query, which rows of that new catalog represent a real change against what's already current in 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: what changed in staging_product_v3, vs the current version ===")
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: what changed in staging_product_v3, vs the current version ===
┌────────────┬───────────────┬───────────┐
│ product_id │   category    │ unit_cost │
│  varchar   │    varchar    │  double   │
├────────────┼───────────────┼───────────┤
│ P002       │ health-snacks │      0.72 │
└────────────┴───────────────┴───────────┘

Only P002 — because P001, P003, and P004 in staging_product_v3 match their current version in dim_product_scd (is_current = true) exactly, but P002 brings unit_cost = 0.72, different from the current 0.68. ANTI JOIN returns, precisely, exactly the subset of staging_product_v3 rows that have no identical row on the dim_product_scd side. Notice something important: this ANTI JOIN applied no change at all. It's a read-only query, useful as an audit or alert step — "this is what the next MERGE is going to modify" — before running the real MERGE INTO, not a replacement for it.

Its complement, SEMI JOIN, answers the opposite question — what did not change:

print("\n=== SEMI JOIN: the complement -- which products did NOT change ===")
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: the complement -- which products did NOT change ===
┌────────────┐
│ product_id │
│  varchar   │
├────────────┤
│ P001       │
│ P003       │
│ P004       │
└────────────┘

ANTI JOIN and SEMI JOIN, over exactly the same condition, split staging_product_v3 into two non-overlapping halves: the three products SEMI JOIN confirms unchanged, and the single one ANTI JOIN flags as different. Between the two, they cover 100% of staging_product_v3's rows — no row is left unclassified.

Diagram: what each of the two JOINs does

flowchart TD
    A["staging_product_v3: 4 rows"] --> B{"Condition: same product_id,\nsame category, same unit_cost\nas the current version"}
    B -->|"identical match FOUND"| C["SEMI JOIN\nreturns the staging row\n(P001, P003, P004)"]
    B -->|"identical match NOT found"| D["ANTI JOIN\nreturns the staging row\n(P002 -- the only one that changed)"]

Equivalence with LEFT JOIN + WHERE (the form other engines would need)
──────────────────────────────────────────────────────────────────────────────
ANTI JOIN  b ON cond   ≡   LEFT JOIN b ON cond   WHERE b.<any_join_column> IS NULL
SEMI JOIN  b ON cond   ≡   WHERE EXISTS (SELECT 1 FROM b WHERE cond)

Going deeper: why ANTI JOIN and not LEFT JOIN + WHERE IS NULL

The LEFT JOIN ... WHERE <right_column> IS NULL form works, and in fact it's the form lesson 4 used implicitly when counting matched_rows with LEFT JOIN — but it has two costs ANTI JOIN avoids. The first is readability: LEFT JOIN followed by a WHERE that checks IS NULL on an arbitrary column of the right table doesn't communicate, through the operator's name, what the query is looking for — someone reading it has to reconstruct the intent from two separate clauses. ANTI JOIN, on the other hand, names the exact operation in the keyword itself.

The second cost is subtler, and has to do with NULL: if the column you choose for a LEFT JOIN's WHERE ... IS NULL could, for some reason, genuinely be NULL on the right side even though there was a MATCH — for example, if that specific column allows NULL values in the real data — the IS NULL condition would give a false positive, marking as "no match" a row that actually had one. ANTI JOIN doesn't carry that risk, because it doesn't rely on examining a specific column on the right side to infer the absence of a match — it evaluates the JOIN condition directly, without that intermediate step. DuckDB's official documentation confirms this exact relationship: ANTI JOIN "provides the same logic as the NOT IN operator," and SEMI JOIN "provides the same logic as the IN operator" — neither one building the full JOIN product first only to filter it afterward.

It's worth noting that ANTI JOIN and SEMI JOIN are relatively recent syntax in DuckDB — introduced in version 0.8 — designed specifically to make explicit two operations that, before, only existed disguised as LEFT JOIN/IS NULL or as subqueries with EXISTS/NOT EXISTS. The result is identical in all three cases; what changes is how much the reader has to reconstruct to understand the intent.

Common mistakes

Using ANTI JOIN with a single-column equality condition, when the real comparison needs several. What happens: someone writes staging_product_v3 s ANTI JOIN dim_product_scd d ON s.product_id = d.product_id — without is_current, category, or unit_cost — expecting to find "what changed," and the result comes back empty, because every product_id in staging_product_v3 does exist in dim_product_scd, regardless of whether their values match. Why it happens: it's easy to think of ANTI JOIN as "find what isn't there," without noticing that "isn't there" depends entirely on which columns the ON condition includes. How to spot it: if your ANTI JOIN returns zero rows when you expected to find changes, check whether the ON condition only compares the key, without the value columns that actually define "changed." How to fix it: to detect value changes (not just existence), an ANTI JOIN's ON condition must include the key and every column whose value you care about comparing — exactly like this lesson's second scenario, which compares product_id, category, and unit_cost at once.

Forgetting is_current = true when comparing against a historized dimension with ANTI JOIN. What happens: someone runs this lesson's second scenario without the d.is_current = true filter, and the ANTI JOIN gets compared against every version of dim_product_scd, including P002's historical, closed one (snacks/0.60). Why it happens: with a dimension that has no history, that filter wouldn't be needed — the problem only shows up when the dimension, like dim_product_scd, has more than one row per product_id. How to spot it: if your ANTI JOIN against a historized dimension returns inconsistent or unexpected results for a product you know has more than one version, suspect a missing is_current first. How to fix it: any comparison against a SCD-2 dimension's "current state" — with ANTI JOIN, SEMI JOIN, or a normal JOIN — needs to restrict the dimension side to is_current = true, exactly the same discipline module 4's MERGE INTO already required in its ON condition.

Confusing ANTI JOIN with a way to "fix" the data. What happens: someone, after seeing ANTI JOIN find ORD-9001 as an orphan in the first scenario, expects the query to also correct it or somehow remove it from orphan_order. Why it happens: ANTI JOIN, like any SELECT, feels like part of a "detect and fix" flow, and it's easy to forget it's purely read-only. How to spot it: if you expect orphan_order to have fewer rows after running the ANTI JOIN, or dim_product_scd to change after the second scenario's ANTI JOIN, you have this confusion. How to fix it: ANTI JOIN and SEMI JOIN are read-only queries, exactly like any other SELECT — they exist to detect and report, never to modify. The real fix — inserting a dimension row that covers the gap, or applying the MERGE INTO over the detected change — is a separate, explicit step, which these two lessons name but don't execute against Kiosko's real data.

Exercises

Exercise 1 — Confirm with COUNT(*) that SEMI JOIN and ANTI JOIN together cover exactly staging_product_v3's four rows. Write a query that sums both results and confirms it comes to 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 unchanged,
        (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 changed
"""))

┌───────────┬─────────┐
│ unchanged │ changed │
│   int64   │  int64  │
├───────────┼─────────┤
│         3 │       1 │
└───────────┴─────────┘

Exercise 2 — Add an additional orphan order, for a product that doesn't exist in Kiosko's catalog (P099), and confirm the ANTI JOIN finds it for a different reason. Insert an order with product_id = 'P099' into orphan_order, and run the first scenario's ANTI JOIN again.

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 │
└──────────┴────────────┴─────────────────────┘

Exercise 3 — Explain why ANTI JOIN can never return more rows than the table on the left. In 2-3 sentences, using what you know about how ANTI JOIN evaluates its condition, explain why — unlike lesson 2's unfiltered JOIN — there's never fan-out with ANTI JOIN or SEMI JOIN.

Summary and next step

This lesson introduced ANTI JOIN and SEMI JOIN, two native DuckDB JOIN types that solve "which rows have no match at all?" and its opposite, "which rows do?", with no need for a subquery or a LEFT JOIN/WHERE IS NULL. You applied them to two real scenarios: finding an order outside dim_product_scd's temporal coverage (picking back up lesson 4), and detecting, before running any MERGE INTO, exactly which row of a new catalog represents a real change against what's already current.

Before moving on you should be able to: write from memory ANTI JOIN/SEMI JOIN's syntax and its equivalence with NOT IN/IN; explain why they're read-only operations, useful for detecting and reporting, not for fixing; and apply ANTI JOIN to find both temporal orphans (a date with no coverage) and structural orphans (a natural key that doesn't exist).

Lesson 8, the module's closing project, integrates the six previous lessons over the same familiar fact_orders and dim_product_scd: the correct JOIN against the broken one, deduplication of a resent batch, and this lesson's ANTI JOINs, all in a single, fully verified end-to-end flow.

Resources

• DuckDB — FROM and JOIN clause documentation, which includes the exact syntax and semantics of SEMI JOIN and ANTI JOIN used in this lesson. duckdb.org/docs/current/sql/query_syntax/from. In English.
• Kimball Group — "Late Arriving Dimension" — the "natural key with no dimension row at all" case this lesson's exercise 2 detects with ANTI JOIN. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/late-arriving-dimension. In English.
• DuckDB — official MERGE INTO statement documentation — the process an audit ANTI JOIN, like this lesson's second scenario, typically precedes without replacing it. duckdb.org/docs/lts/sql/statements/merge_into. In English.