5.4 SQL Join Semantics and Integrated Analytics

5. Complex Joins in Spark SQL: Semantics and Execution

Joining distributed datasets is central to analytical query processing. Understanding how each join variety manages unmatched keys, schema projection, and row multiplication is essential for query optimization.

Standard Joins: Inner and Outer

• INNER JOIN: Emits rows only when join keys match in both tables. If multiple matching records exist on the right side, the left row is duplicated. Columns from both tables are present in the output.
• LEFT OUTER JOIN: Emits all rows from the left table. Matching rows from the right table are joined; unmatched left rows have NULL populated for all right-table columns.
• RIGHT OUTER JOIN: Emits all rows from the right table, populating NULL for unmatched left-table columns.
• FULL OUTER JOIN: Emits all rows from both tables, filling missing attributes on either side with NULL.
• CROSS JOIN: Produces the Cartesian product ($M \times N$ rows). Because cross joins can accidentally produce trillions of rows and crash executors with Out-Of-Memory errors, use explicit crossJoin() to make intent clear. spark.sql.crossJoin.enabled controls implicit Cartesian joins and defaults to true in upstream Spark 3.5; rejection is not a universal default.

Specialized Filtering Joins: Semi and Anti Joins

Spark SQL provides two highly optimized filtering joins that correspond directly to subquery predicates:

1. LEFT SEMI JOIN (Equivalent to WHERE EXISTS or IN)

A Left Semi Join filters the left table to include only rows that have at least one matching key in the right table:

• Output Schema: The resulting DataFrame contains ONLY columns from the left table. Columns from the right table cannot be referenced or projected.
• No Row Duplication: Even if a left-table key matches 10,000 rows in the right table, the left row is emitted exactly once.

-- Emits departments that have at least one active employee
SELECT d.* 
FROM corporate.departments d
LEFT SEMI JOIN corporate.employees e ON d.dept_id = e.dept_id;

2. LEFT ANTI JOIN (Equivalent to WHERE NOT EXISTS)

A Left Anti Join filters the left table to include only rows that have no matching key in the right table:

• Output Schema: The resulting DataFrame contains ONLY columns from the left table.
• Exclusion Filter: Emits left rows where the right-side lookup yields zero matches.

-- Emits orphaned departments that contain zero employees
SELECT d.* 
FROM corporate.departments d
LEFT ANTI JOIN corporate.employees e ON d.dept_id = e.dept_id;

Comprehensive Join Comparison Table

---

6. Real-World Scenario: Customer Cohort and Retention Analytics Walkthrough

The following production script analyzes subscription retention and period-over-period revenue trends using chained window specifications, ranking, and semi/anti join filtering:

WITH 
    -- Step 1: Active subscribers filtered via Left Semi Join with active payments
    retained_subscribers AS (
        SELECT s.subscription_id, s.customer_id, s.plan_tier, s.monthly_fee, s.signup_date
        FROM billing.subscriptions s
        LEFT SEMI JOIN billing.payment_records p 
            ON s.subscription_id = p.subscription_id 
            AND p.payment_status = 'SETTLED'
            AND p.payment_date >= add_months(current_date(), -1)
    ),
    -- Step 2: Calculate customer lifetime metrics and moving averages
    subscription_analytics AS (
        SELECT 
            customer_id,
            plan_tier,
            monthly_fee,
            signup_date,
            -- Unique sequential customer signup order within tier
            ROW_NUMBER() OVER (PARTITION BY plan_tier ORDER BY signup_date ASC) AS signup_sequence,
            -- Rank by fee within tier
            DENSE_RANK() OVER (PARTITION BY plan_tier ORDER BY monthly_fee DESC) AS fee_rank,
            -- Moving 3-subscriber average fee
            AVG(monthly_fee) OVER (
                PARTITION BY plan_tier 
                ORDER BY signup_date 
                ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
            ) AS moving_avg_fee,
            -- Previous subscriber fee to compute step delta
            LAG(monthly_fee, 1, 0.0) OVER (
                PARTITION BY plan_tier 
                ORDER BY signup_date, subscription_id
            ) AS prev_subscriber_fee
        FROM retained_subscribers
    )
-- Final Step: Isolate top-tier customers while excluding flagged fraud accounts
SELECT 
    sa.customer_id,
    sa.plan_tier,
    sa.monthly_fee,
    sa.signup_sequence,
    sa.fee_rank,
    round(sa.moving_avg_fee, 2) AS moving_avg_fee,
    round(sa.monthly_fee - sa.prev_subscriber_fee, 2) AS fee_delta_from_prev
FROM subscription_analytics sa
LEFT ANTI JOIN security.flagged_fraud_accounts f 
    ON sa.customer_id = f.customer_id
WHERE sa.fee_rank <= 5
ORDER BY sa.plan_tier, sa.fee_rank;