Question

A data engineer maintains two Delta tables: `active_customers` and `orders`. They need to produce a result set containing only the rows from `orders` whose `customer_id` does NOT exist in the `active_customers` table, so the marketing team can identify orphaned orders. Which single join operation produces exactly this result most efficiently?

Accepted Answer

A LEFT ANTI JOIN between `orders` and `active_customers` on `customer_id`

Answer

An INNER JOIN between `orders` and `active_customers`, then filter where `active_customers.customer_id IS NULL`

Answer

A LEFT SEMI JOIN between `orders` and `active_customers` on `customer_id`

Answer

A FULL OUTER JOIN between `orders` and `active_customers`, then filter where both keys are NULL