Left Anti Join & Left Semi Join in Snowflake: Complete Guide with Optimizer Internals and Banking Patterns

1. Introduction: The Two Sides of Existence Filtering

In relational data engineering, two of the most frequent business questions are:

1. Who HAS done something? (e.g., customers with at least one transaction)
2. Who HAS NOT done something? (e.g., customers with zero transactions)

In relational algebra, these are known as Existence Joins:

• Left Semi Join ( \ltimes R$): Returns rows from the left table that have at least one match in the right table.
• Left Anti Join ( riangleright R$): Returns rows from the left table that have zero matches in the right table.

Table A (Customers)             Table B (Orders)
+------+----------+             +----------+--------+
| ID   | Name     |             | Cust_ID  | Amount |
+------+----------+             +----------+--------+
| 101  | Alice    | <--- Match -| 101      | 50   |
| 102  | Bob      |     ┌-------| 101      | 20   |
| 103  | Charlie  | <---┼ Match -| 103      | 90   |
| 104  | Diana    |     |       +----------+--------+
+------+----------+     └---------------------------+

LEFT SEMI JOIN (Rows in A with >= 1 match in B):
+------+----------+
| ID   | Name     |
+------+----------+
| 101  | Alice    |  (Returned ONCE, despite 2 orders)
| 103  | Charlie  |
+------+----------+

LEFT ANTI JOIN (Rows in A with 0 matches in B):
+------+----------+
| ID   | Name     |
+------+----------+
| 102  | Bob      |
| 104  | Diana    |
+------+----------+

2. Core Differences Across All Join Types

Understanding how Semi and Anti joins compare to standard Inner and Outer joins is fundamental for data engineers:

3. Why Snowflake Doesn't Have LEFT SEMI or LEFT ANTI Keywords

Engines like Apache Spark SQL, Trino, Hive, and Databricks provide explicit join keywords:

-- Spark SQL / Trino
SELECT c.* FROM customers c LEFT SEMI JOIN orders o ON c.id = o.cust_id;
SELECT c.* FROM customers c LEFT ANTI JOIN orders o ON c.id = o.cust_id;

# PySpark
df_customers.join(df_orders, on="customer_id", how="left_semi")
df_customers.join(df_orders, on="customer_id", how="left_anti")

However, Snowflake adheres strictly to ANSI SQL:1999 / SQL:2016 standards. Standard ANSI SQL does not define LEFT SEMI JOIN or LEFT ANTI JOIN as standalone join clauses.

Instead, ANSI SQL uses declarative subquery expressions:

• Left Semi Join $ ightarrow$ WHERE EXISTS (...) or WHERE ... IN (...)
• Left Anti Join $ ightarrow$ WHERE NOT EXISTS (...) or LEFT JOIN ... WHERE right.key IS NULL

Snowflake does not need proprietary syntax because its query compiler recognizes these ANSI patterns and automatically lowers them into physical Semi-Join and Anti-Join execution operators.

4. Syntax & Implementation Patterns

Pattern A: Left Semi Join (Finding Matches)

1. WHERE EXISTS (Recommended Best Practice)

SELECT
    c.customer_id,
    c.customer_name,
    c.tier
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

2. The Inefficient Anti-Pattern: INNER JOIN + DISTINCT

A common mistake among beginner data engineers is writing:

-- AVOID THIS IN PRODUCTION:
SELECT DISTINCT
    c.customer_id,
    c.customer_name,
    c.tier
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id;

Why this is bad: If 1 million customers have 500 million orders, the INNER JOIN materializes hundreds of millions of duplicate rows into warehouse memory, only for a downstream sort/hash operator to discard them with DISTINCT. WHERE EXISTS short-circuits on the very first match and never duplicates a row.

Pattern B: Left Anti Join (Finding Non-Matches)

1. WHERE NOT EXISTS (Recommended Best Practice)

SELECT
    c.customer_id,
    c.customer_name,
    c.tier
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

2. LEFT JOIN ... WHERE right.key IS NULL (The Outer Join Idiom)

SELECT
    c.customer_id,
    c.customer_name,
    c.tier
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

3. The Dangerous Pattern: NOT IN (Fails with NULLs)

-- DANGER: If orders.customer_id contains a single NULL, returns 0 rows!
SELECT c.*
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
);

5. Sample DDL & Sandbox Data Setup

Run this script in Snowflake to create a hands-on testing environment:

-- Step 1: Create sample tables
CREATE OR REPLACE TRANSIENT TABLE customers (
    customer_id   INT,
    customer_name VARCHAR(50),
    tier          VARCHAR(20),
    created_date  DATE
);

CREATE OR REPLACE TRANSIENT TABLE orders (
    order_id      INT,
    customer_id   INT,
    order_amount  NUMBER(10, 2),
    order_date    DATE,
    order_status  VARCHAR(20)
);

-- Step 2: Insert customers
INSERT INTO customers (customer_id, customer_name, tier, created_date) VALUES
    (101, 'Alice Johnson', 'Gold',     '2025-01-15'),
    (102, 'Bob Smith',     'Silver',   '2025-02-01'),
    (103, 'Charlie Brown', 'Platinum', '2025-02-10'),
    (104, 'Diana Prince',  'Bronze',   '2025-03-05'),
    (105, 'Evan Wright',   'Silver',   '2025-03-20');

-- Step 3: Insert orders
-- Alice (101) has 2 orders (duplicate key test)
-- Charlie (103) has 1 order
-- Order 5004 has NULL customer_id (unassigned / guest checkout)
-- Order 5005 has customer_id 999 (orphan record)
-- Bob (102), Diana (104), and Evan (105) have NO orders.
INSERT INTO orders (order_id, customer_id, order_amount, order_date, order_status) VALUES
    (5001, 101,  250.00, '2025-03-01', 'COMPLETED'),
    (5002, 101,  120.50, '2025-03-10', 'COMPLETED'),
    (5003, 103,  990.00, '2025-03-15', 'COMPLETED'),
    (5004, NULL,  45.00, '2025-03-18', 'PENDING'),
    (5005, 999,  310.00, '2025-03-22', 'COMPLETED');

6. Query Execution & Outputs Side-by-Side

1. The Left Semi Join Query (EXISTS)

Goal: Retrieve all customers who have placed at least one order.

SELECT
    c.customer_id,
    c.customer_name,
    c.tier
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

Output:

Notice that Alice appears only once, even though she has two completed orders.

2. The Left Anti Join Query (NOT EXISTS)

Goal: Retrieve all customers who have NEVER placed an order.

SELECT
    c.customer_id,
    c.customer_name,
    c.tier
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

Output:

3. The Left Anti Join Query (LEFT JOIN ... IS NULL)

SELECT
    c.customer_id,
    c.customer_name,
    c.tier
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL
ORDER BY c.customer_id;

Output:

Identical to NOT EXISTS (Bob, Diana, Evan).

4. The NOT IN Landmine (Empty Output Bug)

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
);

Output:

(0 rows returned)

Why did this fail? Because row 5004 in orders has customer_id = NULL. In SQL three-valued logic:

c.id NOT IN (101, 103, 999, NULL) evaluates to: NOT (c.id = 101 OR c.id = 103 OR c.id = 999 OR c.id = NULL)

Since comparing anything to NULL yields UNKNOWN, the negated expression evaluates to UNKNOWN. In a WHERE filter, UNKNOWN is treated as FALSE, discarding every single customer record!

7. How Snowflake's Optimizer Handles Semi and Anti Joins

Snowflake uses a Cost-Based Optimizer (CBO) that optimizes both join types down to the hardware level:

                          +------------------------+
                          |      SQL Query         |
                          |  (EXISTS / NOT EXISTS)  |
                          +-----------+------------+
                                      |
                                      v
                          +------------------------+
                          | Logical Query Rewrite  |
                          | (AST Subquery Flattener)|
                          +-----------+------------+
                                      |
                      +---------------+---------------+
                      |                               |
                      v                               v
          +-----------------------+       +-----------------------+
          | Physical SEMI Join    |       | Physical ANTI Join    |
          |  (Hash Semi-Join)     |       |   (Hash Anti-Join)    |
          +-----------+-----------+       +-----------+-----------+
                      |                               |
                      v                               v
          +-----------------------+       +-----------------------+
          | Emit on FIRST MATCH   |       | Discard on FIRST MATCH|
          | (Short-Circuit probe) |       | (Short-Circuit probe) |
          +-----------------------+       +-----------------------+

1. In-Memory Hash Table with Key Deduplication

• Build Phase: Snowflake scans the inner table (orders) and creates an in-memory hash table containing only the distinct join keys. If a customer has 50,000 orders, only one entry exists in the hash table.
• Probe Phase: Snowflake streams rows from customers against the hash table:
  • In a Semi Join: The first hash hit emits the row and immediately stops probing for that key.
  • In an Anti Join: The first hash hit discards the row and immediately stops probing.
  • Intermediate joined rows are never generated.

2. Runtime Join Filter (Bloom Filter Pushdown)

During the build phase, Snowflake generates a runtime Bloom filter from the join keys and pushes it down into the remote storage scan operators (Amazon S3, Azure Blob, or Google Cloud Storage). This allows Snowflake to prune entire micro-partitions of the outer table before transferring bytes over the network.

3. Query Profile Analysis in Snowsight

When you inspect the Query Profile in Snowsight:

• Semi Join: Operator will display Join Type: Left Semi.
• Anti Join: Operator will display Join Type: Left Anti (or Left Semi with negation).
• Look at Partitions Scanned vs Partitions Total to confirm micro-partition pruning.
• Check Bytes Spilled to Local/Remote Storage - because Semi/Anti joins only store distinct keys in the build hash table, they rarely spill compared to wide INNER JOIN queries.

8. Real-World Banking & Financial Use Cases

Semi Join Scenarios (Existence)

1. Transacting Accounts in Rolling Window

Find customer master records for accounts that executed customer-initiated debit or credit activity within the last 30 days:

SELECT
    c.customer_id,
    c.legal_name,
    c.risk_tier
FROM customer_360.customer_master c
WHERE EXISTS (
    SELECT 1
    FROM core_banking.transactions t
    WHERE t.customer_id = c.customer_id
      AND t.transaction_timestamp >= DATEADD('day', -30, CURRENT_TIMESTAMP())
      AND t.transaction_type NOT IN ('FEE', 'INTEREST_CREDIT')
);

2. High-Severity Fraud Alert Accounts

Retrieve card records that currently have active, confirmed fraud alerts:

SELECT
    card.card_token,
    card.account_number,
    card.card_status
FROM cards.dim_cards card
WHERE EXISTS (
    SELECT 1
    FROM fraud.fraud_alerts fa
    WHERE fa.card_token = card.card_token
      AND fa.alert_severity = 'CRITICAL'
      AND fa.disposition = 'CONFIRMED'
);

3. Marketing Campaign Eligibility

Select customers with pre-approved credit scores:

SELECT
    p.prospect_id,
    p.email_address,
    p.state_code
FROM marketing.prospects p
WHERE EXISTS (
    SELECT 1
    FROM credit_bureau.credit_scores cs
    WHERE cs.ssn_hash = p.ssn_hash
      AND cs.fico_score >= 740
);

Anti Join Scenarios (Absence)

1. Inactive / Dormant Accounts (Escheatment & Regulatory Compliance)

Under federal and state escheatment regulations, banks must report and classify accounts with zero customer activity over 180 or 365 days:

SELECT
    a.account_id,
    a.customer_id,
    a.account_type,
    a.current_balance
FROM core_banking.accounts a
WHERE a.status = 'ACTIVE'
  AND NOT EXISTS (
      SELECT 1
      FROM core_banking.transactions t
      WHERE t.account_id = a.account_id
        AND t.transaction_timestamp >= DATEADD('day', -180, CURRENT_TIMESTAMP())
        AND t.transaction_type NOT IN ('FEE', 'INTEREST_CREDIT')
  );

2. Unmatched Wire Payments & Clearing Settlement Breaks

Detect clearing house payments (Fedwire, ACH, SWIFT) that failed to post to the General Ledger:

SELECT
    c.wire_reference_number,
    c.settlement_amount,
    c.clearing_timestamp
FROM clearing.incoming_wire_feed c
WHERE c.clearing_date = CURRENT_DATE()
  AND NOT EXISTS (
      SELECT 1
      FROM general_ledger.journal_entries gl
      WHERE gl.reference_id = c.wire_reference_number
        AND gl.posting_date = c.clearing_date
        AND gl.amount = c.settlement_amount
  );

3. Missing KYC / CIP Compliance Verification

Identify active customers who have not completed identity verification within their 30-day onboarding grace period:

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.onboarding_date
FROM customer_360.customers c
WHERE c.is_active = TRUE
  AND NOT EXISTS (
      SELECT 1
      FROM compliance.kyc_verification_dossier kyc
      WHERE kyc.customer_id = c.customer_id
        AND kyc.verification_status = 'VERIFIED'
        AND kyc.expiration_date > CURRENT_DATE()
  );

4. ETL Validation & Orphan Data Audits

Identify staging credit card authorizations referencing invalid card tokens before loading reporting tables:

SELECT
    stg.auth_id,
    stg.card_token,
    stg.auth_amount,
    stg.auth_timestamp
FROM staging.stg_card_authorizations stg
LEFT JOIN core_cards.dim_cards card
    ON stg.card_token = card.card_token
WHERE card.card_token IS NULL;

9. Common Mistakes & Best Practices

1. The NOT IN Trap with Nullable Subqueries

Never use NOT IN unless the subquery column has a guaranteed NOT NULL constraint. If a single NULL exists, the entire query returns 0 rows. Use NOT EXISTS instead.

2. INNER JOIN + DISTINCT instead of EXISTS

Using INNER JOIN followed by DISTINCT causes massive row explosion and warehouse memory consumption. EXISTS short-circuits at the first match.

3. Checking IS NULL on an Optional Attribute

In LEFT JOIN ... WHERE right.col IS NULL, always check the join key or a primary key. Checking an optional column like discount_code IS NULL will incorrectly treat matching orders with null discounts as non-existent!

4. Join Filter Placement (ON vs WHERE)

In a Left Anti Join using LEFT JOIN:

-- WRONG: Filters inside ON, returns ALL left rows!
SELECT c.* FROM customers c
LEFT JOIN orders o ON c.id = o.cust_id AND o.cust_id IS NULL;

-- RIGHT: Post-join filter in WHERE
SELECT c.* FROM customers c
LEFT JOIN orders o ON c.id = o.cust_id
WHERE o.cust_id IS NULL;

10. Senior Data Engineering Interview Questions & Answers

Q1: What is the mechanical difference between a Left Semi Join and an Inner Join?

Answer: An INNER JOIN combines records from both tables and can multiply rows from the left table when multiple matches exist in the right table (1-to-many relationship). A Left Semi Join tests only for the existence of at least one match. It never projects columns from the right table and never duplicates rows from the left table.

Q2: Why does Snowflake not have explicit LEFT SEMI JOIN and LEFT ANTI JOIN keywords?

Answer: Snowflake follows the ANSI SQL standard (SQL:1999/2016), which specifies EXISTS and NOT EXISTS subqueries instead of explicit join syntax for semi/anti joins. Snowflake's Cost-Based Optimizer automatically compiles these subqueries into physical Left Semi and Left Anti hash join operators.

Q3: Why is NOT EXISTS safer than NOT IN in SQL?

Answer: Due to SQL's three-valued logic, if the subquery in val NOT IN (...) contains even a single NULL, the expression evaluates to UNKNOWN for all rows, causing the entire query to return 0 rows. NOT EXISTS evaluates row-by-row existence; comparisons resulting in NULL simply evaluate to FALSE, allowing NOT EXISTS to return the true set of unmatched records.

Q4: How does Snowflake avoid memory blowup when performing a Semi Join against a right table with billions of duplicate keys?

Answer: During the build phase of a Semi Join, Snowflake only inserts distinct join keys into the in-memory hash table. During the probe phase, Snowflake short-circuits: as soon as the first hash hit occurs for an outer row, that row is immediately emitted and evaluation for that key halts. Duplicate matches are never scanned or joined.

Q5: How do you verify in Snowflake that an anti-join or semi-join query was properly optimized?

Answer: Inspect the query execution plan in the Snowsight Query Profile. Verify that the join operator shows Join Type: Left Semi or Join Type: Left Anti, ensure that partitions were pruned via Bloom filters, and check that no unnecessary bytes spilled to local or remote storage.

Unified Quick Reference Cheat Sheet